Reference no: EM131196387
This is a comprehensive problem all contained on this spreadsheet tab. You are to prepare a cash flow statement, a vertical analysis, a horizontal analysis, and some ratio calculations. Scroll down the spreadsheet to complete each part.
Philadelphia Widget Corporation is in the process of preparing financial statements for the year ended 12/31/2015.
They have completed the balance sheet and income statement as shown.
|
Philadelphia Widget Corporation
Income Statement
For the year ended 12/31/2015
|
|
Revenue
|
|
1,235,000
|
|
Cost of Goods Sold
|
|
806,356
|
|
Gross Profit
|
|
428,644
|
|
Administrative Expenses:
|
|
|
|
Salaries
|
212,450
|
|
|
Rent
|
82,500
|
|
|
Depreciation
|
24,800
|
|
|
Total Administrative Expense
|
|
319,750
|
|
Operating Profit
|
|
108,894
|
|
Gain on Sale of Equipment
|
|
4,500
|
|
Interest Expense
|
|
(42,115)
|
|
Net Income
|
|
71,279
|
|
Philadelphia Widget Corporation
Balance Sheet
As of 12/31/2015, 12/31/2014, and 12/31/2013
|
|
Cash
|
|
119,411
|
|
89,564
|
|
105,644
|
|
Accounts Receivable
|
|
85,455
|
|
83,118
|
|
78,400
|
|
Inventory
|
|
41,600
|
|
48,560
|
|
62,600
|
|
Prepaid Expenses
|
|
14,500
|
|
18,100
|
|
24,000
|
|
Total Current Assets
|
|
260,966
|
|
239,342
|
|
270,644
|
|
Land
|
|
50,000
|
|
50,000
|
|
50,000
|
|
Buildings and Equipment
|
182,450
|
|
172,450
|
|
166,800
|
|
|
Accumulated Depreciation
|
78,900
|
103,550
|
62,600
|
109,850
|
54,580
|
112,220
|
|
Total Assets
|
|
414,516
|
|
399,192
|
|
432,864
|
|
Accounts Payable
|
|
62,525
|
|
51,480
|
|
45,200
|
|
Wages payable
|
|
4,500
|
|
4,500
|
|
1,500
|
|
Unearned Revenue
|
|
3,000
|
|
-
|
|
-
|
|
Current Portion of Long-Term Debt
|
|
50,000
|
|
50,000
|
|
50,000
|
|
Total Current Liabilities
|
|
120,025
|
|
105,980
|
|
96,700
|
|
Long-Term Debt
|
|
175,000
|
|
225,000
|
|
275,000
|
|
Common Stock, 3500 shares outstanding
|
35,000
|
|
35,000
|
|
35,000
|
|
|
Retained Earnings
|
84,491
|
|
33,212
|
|
26,164
|
|
|
Total Stockholders' Equity
|
|
119,491
|
|
68,212
|
|
61,164
|
|
Total Liabilities and Equity
|
|
414,516
|
|
399,192
|
|
432,864
|
A. Prepare a Statement of Cash Flow using the indirect method for 2015 using the above statements and the following additional information:
1. Equipment costing $30,000 was purchased in 2015.
2. Equipment having an original cost of $20,000 and accumulated depreciation of $8,500 was sold for $16,000 during 2015.
3. A dividend of $20,000 was declared and paid in 2015.
|
Philadelphia Widget Corporation
|
|
Statement of Cash Flow
|
|
For the year ended 12/31/2015
|
|
Cash flows from Operating Activities
|
|
|
|
Net Income
|
|
71,279
|
|
Adjustments
|
|
|
|
Gain on sale of equipment
|
(4,500)
|
|
|
Depreciation Expense
|
24,800
|
|
|
Increase in Accounts Receivable
|
(2,337)
|
|
|
Decrease in Inventory
|
6,960
|
|
|
Decrease in Prepaid Expense
|
3,600
|
|
|
Increase in Accounts Payable
|
11,045
|
|
|
Increase in Unearned revenue
|
3,000
|
|
|
|
|
42,568
|
|
Net cash flow from Operating Activities
|
|
113,847
|
|
|
|
|
|
Cash flows from Investing Activities
|
|
|
|
Sale proceeds of sale of equipment
|
16,000
|
|
|
Purchase of Equipment
|
(30,000)
|
|
|
Net cash out-flow from Operating Activities
|
|
(14,000)
|
|
|
|
|
|
Cash flows from Financing Activities
|
|
|
|
Payment of Dividends
|
(20,000)
|
|
|
Repayment of Long term debt
|
(50,000)
|
|
|
Net cash out-flow from Financing Activities
|
|
(70,000)
|
|
Net Increase in cash
|
|
29,847
|
|
Cash Balance - Beginning
|
|
89,564
|
|
Cash Balance - Ending
|
|
119,411
|
B. Prepare a vertical analysis (1 year) of the income statement above and a horizontal analysis (2 years) of the balance sheet.
|
Philadelphia Widget Corporation
|
|
Vertical Analysis of Income Statement
|
|
|
2015
|
|
|
Amount
|
Percent
|
|
Revenue
|
|
1235000
|
100.00%
|
|
Cost of Goods Sold
|
|
806356
|
65.29%
|
|
Gross Profit
|
|
428644
|
34.71%
|
|
Administrative Expenses:
|
|
|
|
|
Salaries
|
212450
|
|
|
|
Rent
|
82500
|
|
|
|
Depreciation
|
24800
|
|
|
|
Total Administrative Expense
|
|
319750
|
25.89%
|
|
Operating Profit
|
|
108894
|
8.82%
|
|
Gain on Sale of Equipment
|
|
4500
|
0.36%
|
|
Interest Expense
|
|
-42115
|
-3.41%
|
|
Net Income
|
|
71279
|
5.77%
|
|
Philadelphia Widget Corporation
Horizontal Analysis of Balance Sheet
|
|
2015
|
|
2014
|
|
Variance
|
|
|
|
|
|
Amount
|
Percentage
|
|
Cash
|
|
119,411
|
|
89,564
|
29,847
|
33.32%
|
|
Accounts Receivable
|
|
85,455
|
|
83,118
|
2,337
|
2.81%
|
|
Inventory
|
|
41,600
|
|
48,560
|
(6,960)
|
-14.33%
|
|
Prepaid Expenses
|
|
14,500
|
|
18,100
|
(3,600)
|
-19.89%
|
|
Total Current Assets
|
|
260,966
|
|
239,342
|
21,624
|
9.03%
|
|
Land
|
|
50,000
|
|
50,000
|
-
|
0.00%
|
|
Buildings and Equipment
|
182,450
|
|
172,450
|
|
|
|
|
Accumulated Depreciation
|
#REF!
|
#REF!
|
62,600
|
109,850
|
#REF!
|
#REF!
|
|
Total Assets
|
|
#REF!
|
|
399,192
|
#REF!
|
#REF!
|
|
Accounts Payable
|
|
62,525
|
|
51,480
|
11,045
|
21.45%
|
|
Wages payable
|
|
4,500
|
|
4,500
|
-
|
0.00%
|
|
Unearned Revenue
|
|
3,000
|
|
-
|
3,000
|
|
|
Current Portion of Long-Term Debt
|
|
50,000
|
|
50,000
|
-
|
0.00%
|
|
Total Current Liabilities
|
|
120,025
|
|
105,980
|
14,045
|
13.25%
|
|
Long-Term Debt
|
|
175,000
|
|
225,000
|
(50,000)
|
-22.22%
|
|
Common Stock, 3500 shares outstanding
|
35,000
|
|
35,000
|
|
|
|
|
Retained Earnings
|
51,279
|
|
33,212
|
|
|
|
|
Total Stockholders' Equity
|
|
86,279
|
|
68,212
|
18,067
|
26.49%
|
|
Total Liabilities and Equity
|
|
381,304
|
|
399,192
|
(17,888)
|
-4.48%
|
C. Calculate the following ratios based on 12/31/2015 numbers:
1. Earnings per share
2. Return on common stockholder's equity
3. Return on assets
4. Current ratio
5. Acid-test ratio
6. Accounts receivable turnover.
|
Explain the use of current technologies to both carry out
: Explain to the university (in a whitepaper) the characteristics of high-tech crime also known as Internet, cyber, and computer crime. Explain the use of current technologies to both carry out and fight cyber crime.
|
|
Comparing multiple sample means
: In many ways, comparing multiple sample means is simply an extension of what we covered last week. Just as we had 3 versions of the t-test (1 sample, 2 sample (with and without equal variance), and paired;
|
|
Find the maximum and minimum heights the building can be
: Assume that the distance to the building along the ground is 120 m and the angle measured along the line of sightis 30+-3. Find the maximum and minimum heights the building can be.
|
|
How is it important to the history of water treatment
: What is "germ theory" and how is it important to the history of water treatment?
|
|
Create a vertical analysis of the income statement
: Prepare a vertical analysis (1 year) of the income statement above and a horizontal analysis (2 years) of the balance sheet. Prepare a Statement of Cash Flow using the indirect method for 2015 using the above statements.
|
|
Why is rifling present in the bore of the barrel
: Compare and contrast a revolver versus a semiautomatic handgun. How is the caliber of a handgun determined? Explain the difference between a bullet, shell casing, and a projectile. When examining a handgun, projectile, or shell casing, provide exampl..
|
|
What is the nature of most common type of foundation failure
: What is the nature of the most common type of foundation failure? What measures can be taken to prevent its occurrence?
|
|
Draw well labelled diagram that shows the impat of the tax
: At the Melbourne Cricket Ground, the venue for the Australian Foot- ball League (AFL) grand final and the Boxing Day test cricket match, seating is limited to 95,000 people. Seeing a golden opportunity to raise revenue, the Victorian state government..
|
|
Impact of consumption tax on welfare of in dividual parties
: Having rejected a tariff on steel (that is, a tax on steel imports), the Prime-Minister of Isoland (a small open economy) is now considering a specific tax on all steel consumption in isoland (both domestically sources steel and imported steel). Illu..
|