Question: Using the financial statements that you have prepared for Smith Manufacturing; prepare the following analyses in Excel:
• Horizontal analysis
• Vertical analysis
• Ratio analysis - calculate a minimum of five ratios
In addition to the numerical calculations, prepare a Word document that contains a brief written analysis of your findings. For example, is this company struggling, doing well, etc.
| Smith Manufacturing, Inc. |
| Balance Sheet |
| As at Dec 31, 20XX |
| Assets |
Prior YR |
Current YR |
| Current Assets: |
|
|
| Cash |
$ 15,000 |
$ 362,750 |
| Accounts receivable |
225,750 |
320,750 |
| Less Allowance for doubtful Debts |
(20,000) |
(23,500) |
| Net Accounts Receivable |
205,750 |
297,250 |
| Prepaid Insurance |
5,000 |
8,000 |
| Inventory |
80,000 |
40,000 |
| Total Current Assets |
$ 305,750 |
$ 708,000 |
| Fixed Assets: |
|
|
| Land |
$ 50,000 |
$ 50,000 |
| Building |
150,000 |
150,000 |
| Less Accumulated Depreciation on Building |
(7,500) |
(15,000) |
| Net Building |
142,500 |
135,000 |
| Equipment |
500,000 |
500,000 |
| Less Accumulated Depreciation on Equipment |
(50,000) |
(100,000) |
| Net Equipment |
450,000 |
400,000 |
| Total Fixed Assets |
642,500 |
585,000 |
| Total Assets |
$ 948,250 |
$ 1,293,000 |
| Current Liabilities: |
|
|
| Accounts payable |
$ 55,000 |
$ 105,000 |
| Notes Payable (Short term ) |
50,000 |
125,000 |
| Salaries Payable |
5,000 |
2,000 |
| Interest Payable |
17,250 |
21,250 |
| Total Current Liabilities |
127,250 |
253,250 |
| Long Term Liabilities: |
|
|
| Notes Payable (long term ) |
350,000 |
500,000 |
| Total Long term Liabilities |
350,000 |
500,000 |
| Common stock |
300,000 |
300,000 |
| Retained Earnings |
171,000 |
239,750 |
| Total Equity |
471,000 |
539,750 |
| Total Liabilities and Equity |
$ 948,250 |
$ 1,293,000 |