In: Finance
Assume ABC Company has asked you to not only prepare their 2017 year-end Balance Sheet but to also provide pro-forma financial statements for 2018. In addition, they have asked you to evaluate their company based on the pro-forma statements with regard to ratios. They also want you to evaluate 3 projects they are considering. Their information is as follows:
End of the year information:
Account |
12/31/17 Ending Balance |
Cash |
50,000 |
Accounts Receivable |
175,000 |
Inventory |
126,000 |
Equipment |
480,000 |
Accumulated Depreciation |
90,000 |
Accounts Payable |
156,000 |
Short-term Notes Payable |
12,000 |
Long-term Notes Payable |
200,000 |
Common Stock |
235,000 |
Retained Earnings |
solve |
Additional Information:
Part A:
Show excel formula
I made all necessary budget in excel and paste tacle here and show the all formula via screenshot of excel. If you have any query please revert me.
Below is the input data sheet screen shot in this cell no. B25 commission of sales representative is 12.5% due to roundig off it shows 13% but calculations are correct. in table below the screenshot i made correction .
Input data sheet | ||
Account | 31/12/2017 | |
Ending Balance | ||
Cash | 50,000 | |
Accounts Receivable | 1,75,000 | |
Inventory | 1,26,000 | |
Equipment | 4,80,000 | |
Accumulated Depreciation | 90,000 | |
Accounts Payable | 1,56,000 | |
Short-term Notes Payable | 12,000 | |
Long-term Notes Payable | 2,00,000 | |
Common Stock | 2,35,000 | |
Retained Earnings | solve | |
December sales | 10,000 | units |
Each month's sales | 5% | extra of prior month's sales |
Selling price | $25 | per unit |
Each month's ending inventory | 80% | of the next month's expected unit sales |
December 31, 2017 inventory | 8,400 | units |
Purchase price | $15 | per unit |
Sales representatives' commissions | 12.5% | of sales |
Sales manager's monthly salary (January) | $3,500 | |
Sales manager's monthly salary (After Jan) | $4,000 | |
General and administrative expenses: | ||
Administrative salaries | $8,000 | per month |
Depreciation | $5,000 | per month |
Interest on the long-term note payable | 0.90% | monthly |
Cash sales | 30% | of sales |
Credit sales | 70% | of sales |
Collection of receivables | in the month following the sale | |
Purchases | Credit | |
Payment of purchases | in the next month | |
Minimum ending cash balance | $50,000 | |
Interest on short-term notes | 1% | at each month end |
Dividends payment in February | $1,00,000 | |
Income tax rate | 35% | |
Purchase of equipment in March | $55,000 | |
ABC Company | ||
Balance Sheet | ||
As At December 31, 2017 | ||
Assets | ||
Current Assets | ||
Cash | $50,000 | |
Accounts Receivable | 1,75,000 | |
Inventory | 1,26,000 | |
Total Current Assets | 3,51,000 | |
Fixed Assets | ||
Equipment | 4,80,000 | |
Less: Depreciation | 90,000 | 3,90,000 |
Total Fixed Assets | 3,90,000 | |
Total Assets | 7,41,000 | |
Liabilities | ||
Current Liabilities | ||
Accounts payable | 1,56,000 | |
Short-term Notes Payable | 12,000 | |
Total Current Liabilities | 1,68,000 | |
Long Term Liabilities | ||
Long-term Notes Payable | 2,00,000 | |
Total Liabilities | 3,68,000 | |
Owner's Equity | ||
Common Stock | 2,35,000 | |
Retained Earnings | 1,38,000 | |
Total Owner's Equity | 3,73,000 | |
Liabilities & Owner's Equity | 7,41,000 |
ABC Company | ||||
Sales Budget | ||||
January | February | March | April | |
Expected unit sales | 10,500 | 11,025 | 11,576 | 12,155 |
Unit selling price | $25 | $25 | $25 | $25 |
Total sales | $2,62,500 | $2,75,625 | $2,89,406 | $3,03,877 |
ABC Company | ||||
Purchases Budget | ||||
January | February | March | Quarter | |
Expected unit sales | 10,500 | 11,025 | 11,576 | 33,101 |
Add: Desired ending inventory | 8,820 | 9,261 | 9,724 | |
Total inventory required | 19,320 | 20,286 | 21,300 | |
Less: Beginning inventory | 8,400 | 8,820 | 9,261 | |
Units required to be purchased | 10,920 | 11,466 | 12,039 | 34,425 |
Price per unit | $15 | $15 | $15 | |
Total cost of purchases | $1,63,800 | $1,71,990 | $1,80,590 | $5,16,380 |
Cost of goods sold | $1,57,500 | $1,65,375 | $1,73,644 | $4,96,519 |
Cost of ending inventory | $1,45,861 | |||
ABC Company | ||||
Selling Expense Budget | ||||
January | February | March | Quarter | |
Sales representatives' commissions | $32,812.50 | $34,453.13 | $36,175.78 | 1,03,441 |
Sales manager's salary | 3,500 | 4,000 | 4,000 | 11,500 |
Total selling expenses | 36,313 | 38,453 | 40,176 | 1,14,941 |
ABC Company | ||||
General and Administrative Expense Budget | ||||
January | February | March | Quarter | |
Administrative salaries | $8,000 | $8,000 | $8,000 | 24,000 |
Depreciation | 5,000 | 5,000 | 5,000 | 15,000 |
Interest on the short-term note payable | 120 | 120 | ||
Interest on the long-term note payable | 1,800 | 1,800 | 1,800 | 5,400 |
Total general & administrative expense | $14,920 | $14,800 | $14,800 | $44,520 |
ABC Company | ||||
Expected Cash Receipt from Customers | ||||
January | February | March | Quarter | |
Cash sales | $78,750 | $82,688 | $86,822 | 2,48,259 |
Collection from credit sales | 1,75,000 | 1,83,750 | 1,92,938 | 5,51,688 |
Total cash receipt from customers | 2,53,750 | 2,66,438 | 2,79,759 | 7,99,947 |
Expected accounts receivable (March 31) | $2,02,584 | |||
ABC Company | ||||
Expected Cash Payment for Purchases | ||||
January | February | March | Quarter | |
Payment for purchases | $1,56,000 | $1,63,800 | $1,71,990 | 4,91,790 |
Expected accounts payable (March 31) | $1,80,590 | |||
ABC Company | ||||
Cash Budget | ||||
January | February | March | ||
Cash Inflows | ||||
Cash receipt from customers | 2,53,750 | 2,66,438 | 2,79,759 | |
Cash Outflows | ||||
Payment for purchases | 1,56,000 | 1,63,800 | 1,71,990 | |
Selling expenses | 36,313 | 38,453 | 40,176 | |
Administrative salaries | $8,000 | $8,000 | $8,000 | |
Interest on the long-term note payable | 1,800 | 1,800 | 1,800 | |
Interest on the short-term note payable | 120 | 0 | 0 | |
Payment of dividends | 1,00,000 | |||
Purchase of equipment | 55,000 | |||
Total cash outflows | 2,02,233 | 3,12,053 | 2,76,966 | |
Cash Flow Summary | ||||
Cash at the start of month | $50,000 | $89,518 | $50,000 | |
Net cash gain (loss) during month | 51,518 | -45,616 | 2,794 | |
Cash balance at end of month before financing | $1,01,518 | $43,902 | $52,794 | |
Less: Minimum cash balance desired | 50,000 | 50,000 | 50,000 | |
Surplus cash (deficit) | $51,518 | ($6,098) | $2,794 | |
External Financing Summary | ||||
External financing balance at start of month | 0 | 0 | $6,098.13 | |
New financing required | 0 | $6,098.13 | 0 | |
Financing repayments | 12,000 | 0 | $2,794 | |
External financing balance at end of month | 0 | 6,098 | 3,305 | |
Cash balance at the end of month after financing | $89,518 | $50,000 | $50,000 | |
ABC Company | ||||
Budgeted Income Statement | ||||
For the quarter Ending March 31, 2018 | ||||
Sales | 8,27,531 | |||
Cost of goods sold | 4,96,519 | |||
Gross margin | 3,31,013 | |||
Operating Expenses | ||||
Selling Expense | ||||
Sales representatives' commissions | 1,03,441 | |||
Sales manager's salary | 11,500 | |||
Total Selling Expense | 1,14,941 | |||
General and Administrative Expense (G & A) | ||||
Administrative salaries | 24,000 | |||
Depreciation | 15,000 | |||
Interest on the short-term note payable | 120 | |||
Interest on the long-term note payable | 5,400 | |||
Total general & administrative expense | 44,520 | |||
Total Operating Expenses | 1,59,461 | |||
Operating Income | 1,71,551 | |||
Income Tax Expense | 60,043 | |||
Net income | 1,11,508 | |||
Statement of Retained Earnings | ||||
March 31, 2018 | ||||
Opening Retained Earnings | 1,38,000 | |||
Add: Net Income | 1,11,508 | |||
Less: Dividends | ($1,00,000) | |||
Retained Earnings, March 31, 2018 | 1,49,508 | |||
ABC Company | ||||
Budgeted Balance Sheet | ||||
As At March 31, 2018 | ||||
Assets | ||||
Current Assets | ||||
Cash | $50,000 | |||
Accounts Receivable | 2,02,584 | |||
Inventory | 1,45,861 | |||
Total Current Assets | 3,98,445 | |||
Fixed Assets | ||||
Equipment | 5,35,000 | |||
Less: Depreciation | 1,05,000 | 4,30,000 | ||
Total Fixed Assets | 4,30,000 | |||
Total Assets | 8,28,445 | |||
Liabilities | ||||
Current Liabilities | ||||
Accounts payable | 1,80,590 | |||
Short-term Notes Payable | 3,305 | |||
Income Tax Payable | 60,043 | |||
Total Current Liabilities | 2,43,937 | |||
Long Term Liabilities | ||||
Long-term Notes Payable | 2,00,000 | |||
Total Liabilities | 4,43,937 | |||
Owner's Equity | ||||
Common Stock | 2,35,000 | |||
Retained Earnings | 1,49,508 | |||
Total Owner's Equity | 3,84,508 | |||
Liabilities & Owner's Equity | 8,28,445 |