Explore BrainMass
Share

Payroll and Depreciation : Construction of an Excel Spreadsheet

This content was STOLEN from BrainMass.com - View the original, and get the already-completed solution here!

The purpose of this project is to construct an Excel file that can be used as a time card and calculate an employee's payroll calculations.

1. Create a time card for your employee for one week of work that shows the date, time in and time out. Assume the employee will take a 1 hour lunch break daily. Ensure the employee works some overtime and weekend hours.

2. Compute the total regular hours, overtime hours and double time (Sundays) for the employee.

3. Assume the employee gets paid $15 per hour for regular hours, time and half for overtime hours (assume over 40 hours) and double time for Sundays.Compute the employees regular pay, overtime pay, double-time pay and gross pay.

4. The employee is married with 2 deductions and is paid weekly. Find the federal withholding taxes using the percentage method.

5. Calculate the total deductions for Social Security, Medicare, FUTA and SUTA taxes that the employer pays on the employee's behalf.

6. Be sure to use formulas whenever possible in your Excel spreadsheet so that for example the hourly rate could be modified and the entire spreadsheet would update all calculations.

© BrainMass Inc. brainmass.com October 25, 2018, 12:11 am ad1c9bdddf
https://brainmass.com/business/payroll-and-depreciation-construction-of-an-excel-spreadsheet-219486

Solution Preview

Ok. There are two excel files needed: one for Timecard, the other one is for ...

$2.19
See Also This Related BrainMass Solution

Accounting Principles: normal balances, statement of cash flows

PART I ? NORMAL BALANCES

Instructions: Place a "D" (Debit) or "C" (Credit) in the space provided to indicate whether the account has a normal debit balance (D) or normal credit balance (C).
1. Retained Earnings 6. Common Stock
2. Equipment 7. Unearned Service Revenue
3. Depreciation Expense 8. Accumulated Depreciation
4. Dividends 9. Accounts Payable
5. Service Revenue 10. Prepaid Rent

PART II? STATEMENT OF CASH FLOWS CLASSIFICATIONS

Instructions
Each of the events below may have an effect on the statement of cash flows. Designate how the event should be reported within the statement of cash flows using the codes provided below. Codes may be used more than once, or not at all.

Codes
A. Investing activity; cash inflow
B. Investing activity; cash outflow
C. Financing activity; cash inflow
D. Financing activity; cash outflow
E. Operating activity; cash inflow
F. Operating activity; cash outflow
G. Noncash investing and financing activity

Events

_____ 1. Issued checks for the weekly payroll

_____ 2. Paid an account payable

_____ 3. Issued bonds payable for cash

_____ 4. Declared and paid a cash dividend

_____ 5. Paid cash for a new car for a traveling salesperson

_____ 6. Paid interest due on a long-term liability,

_____ 7. Paid cash for 40% interest in another company

_____ 8. Received dividends from a stock investment.

_____ 9. Converted bonds payable into common stock

_____ 10. Sold a long-term stock investment for cash at book value

Part III - Long Problem

Swish Watch Corporation manufactures, sells and services expensive, ugly watches. The company has been in business for three years. At the end of the most recent year, 2006, the accounting records reported total assets of $2,255,000 and total liabilities of $1,780,000. During the current year, 2007, the following summarized events occurred:
a. Issued additional shares of stock for $109,000 cash.
b. Borrowed $186,000 cash from the bank and signed a 10-year note.
c. A stockholder sold $5,000 of his capital stock in Swish Watch Corporation to another investor.
d. Built an addition on the factory for $200,000 and paid cash to the construction company.
e. Purchased equipment for the new addition for $44,000, paying $12,000 in cash and signing a six month note for the balance.
f. Returned a $4,000 piece of equipment, from (d), because it proved to be defective; received a cash refund.
g. At the end of 2006, lent $2,000 cash to the company president, Thor Gunnarson, who signed a note with terms requiring repayment of the loan in one year.

Requirements:
1. Create a spreadsheet similar to the below and complete it, using plus for increases and minus for decreases for each account. The first transaction is used as an example.

Assets = Liabilities + Stockholders' Equity

Cash
Notes Receivable
Equipment
Building Notes Payable
Contributed Capital
Retained Earnings

(a) +109,000 = +109,000

2. Did you include event "c" in the spreadsheet? Why?
3. Based on beginning balances plus the completed spreadsheet, provide the following amounts. Show your work.
a. Total assets at the end of the year.
b. Total liabilities at the end of the year.
c. Total stockholder's equity at the end of the year.
d. As of December 31, 2007 has the financing for Swish Watch Corporation's investment in assets primarily come from liabilities or stockholder's equity?

PART IV ? SHORT PROBLEMS

Instructions
Present the solutions, with appropriate supporting calculations, for each of the following independent problems.

A. Given the following information, compute 2010 net income for Manon Company.
Stockholders' equity?January 1, 2010 $150,000
Stockholders' equity?December 31, 2010 175,000
Stockholder investments during 2010 15,000
Dividends paid during 2010 30,000

B. Given the following information, determine the three missing amounts.

Stockholders' Equity
Beginning of the Year End of the Year Changes During the Year
Total Assets $60,000 Total Assets $85,000 Investments $10,000
Total Liabilities ??? Total Liabilities 40,000 Dividends 25,000
Total Stockholders'Equity 35,000 TotalStockholders'Equity ??? Revenues 70,000
Expenses ???
Total Change $15,000

View Full Posting Details