In: Accounting
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:
ABC Company’s management is also considering 3 new projects consisting of the purchase of new equipment. The company has limited resources, and may not be able to complete make all 3 purchases. The information is as follows for the purchases below.
Project 1 |
Project 2 |
Project 3 |
|
Purchase Price |
$80,000 |
$175,000 |
$22,700 |
Required Rate of Return |
6% |
8% |
12% |
Time Period |
3 years |
5 years |
2 years |
Cash Flows – Year 1 |
$48,000 |
$85,000 |
$13,000 |
Cash Flows – Year 2 |
$36,000 |
$74,000 |
$13,000 |
Cash Flows – Year 3 |
$22,000 |
$38,000 |
N/A |
Cash Flows – Year 4 |
N/A |
$26,800 |
N/A |
Cash Flows – Year 5 |
N/A |
$19,000 |
N/A |
How to?
Balance Sheet as at the end of Dec.31,2017 | ||
Assets | ||
Current assets | ||
Cash | 50,000 | |
Accounts Receivable | 175,000 | |
Inventory | 126,000 | |
Total Current assets | 351,000 | |
Fixed assets: | ||
Equipment | 480,000 | |
Less:Accumulated Depreciation | 90,000 | 390,000 |
Total Assets | 741,000 | |
Liabilities & SH .Equity | ||
Current Liabilities | ||
Accounts Payable | 156,000 | |
Short-term Notes Payable | 12,000 | |
Total current liabilities | 168,000 | |
Long-term Liabilities | ||
Long-term Notes Payable | 200,000 | |
Total Long-term Liabilities | 200000 | |
Total Liabilities | 368,000 | |
Sh. Equity | ||
Common Stock | 235,000 | |
Retained Earnings(Bal.fig.) | 138,000 | |
Total Sh. Equity | 373,000 | |
Total Liabilities & SH .Equity | 741000 |
Sales Budget | Jan. | Feb. | Mar. | Quarter | April |
In units | 10500 | 11025 | 11576 | 33101 | 12155 |
S.P./unit in $ | 25 | 25 | 25 | 25 | 25 |
Budgeted sales $ | 262500 | 275625 | 289406 | 827531 |
Purchases budget | |||||
Budgeted Sales in units | 10500 | 11025 | 11576 | 33101 | 12155 |
Add:Ending inventory reqd.(80%*next mth sales) | 8820 | 9261 | 9724 | 9724 | |
Total units needed | 19320 | 20286 | 21300 | 42825 | |
Less: Beginning inventory | 8400 | 8820 | 9261 | 8400 | |
Budgeted Purchases( units) | 10920 | 11466 | 12039 | 34425 | |
Purchase price/unit | 15 | 15 | 15 | 15 | |
Budgeted purchases ($) | 163800 | 171990 | 180590 | 516380 |
COGS Budget | ||||
Budgeted Sales in units | 10500 | 11025 | 11576 | 33101 |
COGS at $ 15/unit | 157500 | 165375 | 173644 | 496519 |
Cost of Mar 31, ending inventory | 145860 | |||
9724*15 |
Selling expense Budget | ||||
Budgeted sales $ | 262500 | 275625 | 289406 | 827531 |
Sales Rep. commn at 12.5%*Sales $ | 32813 | 34453 | 36176 | 103441 |
Sales mgr. salary | 3500 | 4000 | 4000 | 11500 |
Total sell.exp. Budgeted | 36313 | 38453 | 40176 | 114941 |
General and administrative expense budget | ||||
Administrative salaries | 8000 | 8000 | 8000 | 24000 |
Interest exp.(0.9%*200000) | 1800 | 1800 | 1800 | 5400 |
Total cash G&A exp. | 9800 | 9800 | 9800 | 29400 |
Depreciation | 5000 | 5000 | 5000 | 15000 |
Total G&A exp. | 14800 | 14800 | 14800 | 44400 |
Without interest exp.(for I/S) | 39000 |
Expected cash receipts from customers | ||||
Budgeted sales $ | 262500 | 275625 | 289406 | 827531 |
Cash sales (30%) | 78750 | 82688 | 86822 | 248259 |
Credit sales of: | ||||
Dec-17 | 175000 | 175000 | ||
Jan-18 | 183750 | 183750 | ||
Feb-18 | 192938 | 192938 | ||
Total sales collections expected | 253750 | 266438 | 279759 | 799947 |
Exp.March 31 balance of accounts receivable | 202584 | |||
(289406*70%) |
Budgeted purchases ($) | 163800 | 171990 | 180590 | 516380 |
Payment for purchases of: | ||||
Dec-17 | 156000 | 156000 | ||
Jan-18 | 163800 | 163800 | ||
Feb-18 | 171990 | 171990 | ||
Total pmt. For purchases | 156000 | 163800 | 171990 | 491790 |
Exp. March 31 balance of accounts payable. | 180590 |
Cash Budget | ||||
Beginning balance | 50000 | 89518 | 50000 | 50000 |
Total sales collections exp. | 253750 | 266438 | 279759 | 799947 |
Total cash available | 303750 | 355955 | 329759 | 849947 |
Less: Disbursements: | ||||
Total pmt. For purchases | 156000 | 163800 | 171990 | 491790 |
Total sell.exp. Budgeted | 36313 | 38453 | 40176 | 114941 |
Total cash G&A exp. | 9800 | 9800 | 9800 | 29400 |
Dividends | 100000 | 100000 | ||
Equipment purchase | 55000 | 55000 | ||
Total disbursements | 202113 | 312053 | 276966 | 791131 |
Surplus/(Deficit) | 101638 | 43902 | 52793 | 58815 |
Add: Borrowings | 6098 | 6098 | ||
Less: Repayments | 12000 | 2732 | 14732 | |
Less: Interest | 120 | 61 | 181 | |
Ending balance | 89518 | 50000 | 50000 | 50000 |
Budgeted Income Statement | ||
Sales revenue | 827531 | |
Less: COGS | 496519 | |
Gross profit | 331012 | |
Less: Operating expenses | ||
Selling expenses | 114941 | |
G&A exp. | 39000 | 153941 |
Operating Income | 177071 | |
Less: Interest exp. | ||
Int. on Long-term notes | 5400 | |
Int. on short-term notes | 181 | 5581 |
Income before tax | 171490 | |
Less: Income Tax at 35%(171490*35%) | 60021 | |
Net Income | 111468 |
Budgeted Statement of ret. earnings | |
Beginning balance | 138000 |
Add:Net income for the qtr. | 111468 |
Less: Cash dividends | 100000 |
Ending balance | 149468 |
Budgeted Balance Sheet | ||
Assets | ||
Current assets | ||
Cash | 50,000 | |
Accounts Receivable | 202,584 | |
Inventory | 145,860 | |
Total Current assets | 398,444 | |
Fixed assets: | ||
Equipment(480000+55000) | 535,000 | |
Less:Accumulated Depreciation(90000+15000) | 105,000 | 430,000 |
Total Assets | 828,444 | |
Liabilities & SH .Equity | ||
Current Liabilities | ||
Accounts Payable | 180,590 | |
Income tax payable | 60,021 | |
Short-term Notes Payable | 3,366 | |
Total current liabilities | 243,977 | |
Long-term Liabilities | ||
Long-term Notes Payable | 200,000 | |
Total Long-term Liabilities | 200000 | |
Total Liabilities | 443,977 | |
Sh. Equity | ||
Common Stock | 235,000 | |
Retained Earnings(Bal.fig.) | 149,468 | |
Total Sh. Equity | 384,468 | |
Liabilities & SH .Equity | 828,445 |