Question

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:

• Sales for December total 10,000 units. Each month’s sales are expected to exceed the prior month’s results by 5%. The product’s selling price is $25 per unit.
• Company policy calls for a given month’s ending inventory to equal 80% of the next month’s expected unit sales. The December 31 2017 inventory is 8,400 units, which complies with the policy. The purchase price is $15 per unit.
• Sales representatives’ commissions are 12.5% of sales and are paid in the month of the sales. The sales manager’s monthly salary will be $3,500 in January and $4,000 per month thereafter.
• Monthly general and administrative expenses include $8,000 administrative salaries, $5,000 depreciation, and 0.9% monthly interest on the long-term note payable.
• The company expects 30% of sales to be for cash and the remaining 70% on credit. Receivables are collected in full in the month following the sale (none is collected in the month of sale).
• All merchandise purchases are on credit, and no payables arise from any other transactions. One month’s purchases are fully paid in the next month.
• The minimum ending cash balance for all months is $50,000. If necessary, the company borrows enough cash using a short-term note to reach the minimum. Short-term notes require an interest payment of 1% at each month-end (before any repayment). If the ending cash balance exceeds the minimum, the excess will be applied to repaying the short-term notes payable balance.
• Dividends of $100,000 are to be declared and paid in February.
• No cash payments for income taxes are to be made during the first calendar quarter. Income taxes will be assessed at 35% in the quarter.
• Equipment purchases of $55,000 are scheduled for March.

Part A:

• Prepare budgets such that the pro-forma financial statements for the first quarter of 2018 may be prepared.
• Expected cash receipts from customers and the expected March 31 balance of accounts receivable.
• Expected cash payments for purchases and the expected March 31 balance of accounts payable.
• Cash buget
• Budgeted income statement.
• Budgeted statement of retained earnings.
• Budgeted balance sheet.

Show excel formula

Answer 1

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