Question: Using the Excel spreadsheet prepare an Income statement and a Balance Sheet for The Coffee Hut for a future year. Then prepare various ratio analyses,
Using the Excel spreadsheet prepare an Income statement and a Balance Sheet for The Coffee Hut for a future year. Then prepare various ratio analyses, and a contribution margin analysis, to see how Sues business is doing. Work must be completed on the Excel Template that is attached.
| ACC201
| |||||||||||||
| Final Project | |||||||||||||
| Current year: | Current year: | ||||||||||||
| Sales | 540,000 | Operating expenses | 229,000 | ||||||||||
| Inventory purchases | 162,000 | Cash | 33,000 | ||||||||||
| Sales and marketing expenses | 49,000 | Prepaid expenses | 9,000 | ||||||||||
| Accounts receivable | 23,000 | Note payable - current | 10,000 | ||||||||||
| Accumulated depreciation | 23,000 | Administrative expenses | 60,000 | ||||||||||
| Accrued expenses | 5,000 | Common stock | 50,000 | ||||||||||
| Interest expense | 5,000 | Equipment | 108,000 | ||||||||||
| Inventory - current year | 25,000 | Accounts payable | 13,000 | ||||||||||
| Dividends | none | Long-term note payable | 45,000 | ||||||||||
| Shares outstanding | 50,000 | Supplies | 11,000 | ||||||||||
| Prior year: | |||||||||||||
| Accounts receivable | 18,000 | ||||||||||||
| Inventory | 23,000 | Beginning inventory | |||||||||||
| Total assets | 174,000 | ||||||||||||
| Common stock | 50,000 | Goods available | |||||||||||
| Retained earnings | 26,000 | ||||||||||||
| Shares outstanding | 50,000 | Cost of goods sold | |||||||||||
| The Coffee Hut | The Coffee Hut | ||||||||||||
| Balance Sheet | Income Statement | ||||||||||||
| Current | Current | ||||||||||||
| Current assets: | Revenue: | Ratio | Analysis | ||||||||||
| Current ratio | |||||||||||||
| Quick ratio | |||||||||||||
| Gross profit | Accounts receivable turnover | ||||||||||||
| Inventory turnover | |||||||||||||
| Expenses: | Debt to assets | ||||||||||||
| Total current assets | Debt to equity | ||||||||||||
| Property, plant and equipment: | Return on sales | ||||||||||||
| Asset turnover | |||||||||||||
| Total expenses | Return on investment | ||||||||||||
| Net equipment | Operating income | Return on equity | |||||||||||
| Other expenses: | Earnings per share | ||||||||||||
| Total assets | |||||||||||||
| Net income | |||||||||||||
| Current liabilities: | |||||||||||||
| If cost of goods sold is the only variable cost, and the contribution margin | |||||||||||||
| per drink was $3.40, how many coffee drinks were sold during the year, | |||||||||||||
| and what was the average selling price per drink? | |||||||||||||
| Total current liabilities | |||||||||||||
| Long-term liabilities: | Number of drinks sold | ||||||||||||
| Average selling price | |||||||||||||
| Total liabilities | |||||||||||||
| Stockholders' equity: | |||||||||||||
| Total stockholders' equity | |||||||||||||
| Total liabilities and equity | |||||||||||||
Instructions: 1. The template includes all the data necessary to complete the two financial statements, and ratio analyses. But, the financial statement data is in random order, so you must determine where in each financial statement the items should be placed. . 2. Complete the income statement first, since net income will affect stockholders' equity on the balance sheet. Note that you need to calculate cost of goods sold for the income statement 3. Next, complete the balance sheet, taking into account the effect net income has on stockholders' equity. Before you finish, make sure the balance sheet meets the accounting equation. 4. Now you can complete your ratio analyses. Since certain ratios require prior year data, that data has also been included in the template. After calculating the ratios, add a brief analysis about what each ratio means for The Coffee Hut's financial position and results of operations. 5. The final step is to complete the contribution margin question. If cost of goods sold is the only variable cost, and the contribution margin per drink is $3.40, how many coffee drinks did The Coffee Hut sell? What was the average selling price? Instructions: 1. The template includes all the data necessary to complete the two financial statements, and ratio analyses. But, the financial statement data is in random order, so you must determine where in each financial statement the items should be placed. . 2. Complete the income statement first, since net income will affect stockholders' equity on the balance sheet. Note that you need to calculate cost of goods sold for the income statement 3. Next, complete the balance sheet, taking into account the effect net income has on stockholders' equity. Before you finish, make sure the balance sheet meets the accounting equation. 4. Now you can complete your ratio analyses. Since certain ratios require prior year data, that data has also been included in the template. After calculating the ratios, add a brief analysis about what each ratio means for The Coffee Hut's financial position and results of operations. 5. The final step is to complete the contribution margin question. If cost of goods sold is the only variable cost, and the contribution margin per drink is $3.40, how many coffee drinks did The Coffee Hut sell? What was the average selling price
Step by Step Solution
There are 3 Steps involved in it
Get step-by-step solutions from verified subject matter experts
Study Help