Question

You are the sole shareholder and operator of a small incorporated business that purchases compact global positioning devices and sells them to retailers. You started your business two years ago. The following data have been assembled to assist in the preparation of the master budget for the first quarter (January, february and March) of 2020. Your first task is to make up a name for your company and replace Company Name with it.

As of december 31, 2019 your company had the following balance sheet:

	Company Name (Replace with your company name) Balance Sheet December/31/2019
Cash	$5,000	Accounts payable	$26,479
Accounts receivable	25,752	Taxes payable	1200
Inventory	8,425	ST loan payable	1500
Prepaid insurance	3,500	Total current liabilities	29,179
Total current assets	42,677	LT loan payable	15,000
Equipment	16,000	Total liabilities	44,179
Accumulated depreciation	2,133	Common shares*	10,000
Net equipment	13,867	Retained earnings	2,365
Total assets	$56,544	Total liabilities and equity	$56,544

* 1,000 common shares outstanding

1. The company sells each device for $145. Actual sales for November were 240 units and for December were 354 units. In January it is expected that sales will decrease by 30%, but then will increase by 4% in February and will continue to increase by 4% each month after.
2. 60% of the cash for sales is collected in the month of sale, 25% is collected in the following month, and the remaining 15% is collected in the month after that. For simplicity, all sales taxes will be ignored.
3. The company purchases enough units each month to cover the current month’s sales and maintain an ending inventory equal to 40% of the following month’s projected sales. Each unit costs the company $85. Inventory purchases are paid for in the month following purchase.
4. The company is expected to incur fixed operating expenses of $2,000 per month.
5. On August 1, 2019, the company paid $6,000 for one year’s insurance coverage.
6. Variable operating expenses have usually been about 10% of sales for the month. This will continue in January, but they expect to see an increase to 12% by February that will continue. Operating expenses are paid for in the month incurred.
7. Interest is paid monthly on the long-term loan at a rate of 3% per annum. They are also required to make quarterly principle payments, the next is due at the end of March for $1,500. In addition to the long term debt, the company also has short term debt which it pays interest on, see Note M.
8. Equipment costing $8,000 will be purchased and paid for at the beginning of January. All equipment is depreciated on a straight-line basis over 15 years with no residual value.
9. You pay salaries totalling $5,200 each month. For simplicity, ignore all payroll tax implications.
10. You issue 100 additional common shares and sell them to your uncle for $10.00 per share at the end of Feb.
11. You will declare and pay a dividend of $5,000 at the beginning of March.
12. Income tax expense for this small business is calculated at 15% of the earnings before taxes. The company pays income tax instalments of $300 per month.
13. The company must maintain a minimum cash balance of $5,000. A short-term loan is available to cover any shortfall. Interest is paid monthly on the previous month's loan balance at a rate of 6% per annum. Any cash above $5,000 available at month end is used to reduce any existing short-term loan. The interest for the short term debt should be calculated separately from the long term debt in note G.

Required: Use Microsoft Excel to complete this assignment. The spreadsheet MUST use formulas and cell referencing.

1. The balance sheet for December 31, 2019 (as given above).

2. A cash receipts schedule for January, February and March. Hint: use the given percentages. Check figure: Cash receipts for January should be $39.611.

3. A purchases schedule in units for January, February and March. Check figure: January purchases should be 252 units.

4. A cash payments schedule for January, February and March. Check figure: January’s total cash payments should be $45,617

5. A cash budget for January, February and March, including a calculation of cumulative loan at the bottom. Check figure: At the end of January the cash balance should be $5,000 and the cumulative loan should be $7,506.

6. The pro-forma income statements for January, February and March. You should also have a total column which totals all three months.

i. Subtotals for EBIT and EBT should be included.

ii. List all expenses separately (do not combine).

iii. Show long-term and short-term interest separately.

iv. Hint: Cost of goods sold is not the same thing as purchases. Check figure: January’s earnings after taxes should be $2,887

7. A pro-forma retained earnings schedule for the quarter ended March 31 (not for each month). Check figure: Ending retained earnings should be $5,873

8. A pro-forma balance sheet at March 31. You do not have to complete balance sheets for January or February.

• Hints: Consider what will cause balances to change from the December 31 2019 balance sheet. 1. Prepaid insurance will be the opening amount less the amount expensed on the income statement.
• 2. Tax payable will be the opening balance plus total tax expense less total tax instalments.
• 3. Check figure: Total assets should be $59,094

Answer 1

FORMULAS

Your Company Name
Sales Budget
Req 1	Jan	Feb	Mar	Quarter
Sales (Units)	=354*(1-30%)	=B4*(1+4%)	=C4*(1+4%)	=SUM(B4:D4)
Selling price per unit	145	=B5	=C5	=D5
Total budgeted sales	=B4*B5	=C4*C5	=D4*D5	=E4*E5
Purchases budget
Req 2	=B3	=C3	=D3	=E3
Budgeted Sales (Units)	=B4	=C4	=D4	=SUM(B10:D10)
Budgeted Ending Inventory (Units)	=C10*40%	=D10*40%	=(D4*(1+4%))*40%	=D11
Inventory Needed	=SUM(B10:B11)	=SUM(C10:C11)	=SUM(D10:D11)	=SUM(E10:E11)
Less Beginning Inventory	=B10*40%	=B11	=C11	=B13
Required Purchases (Units)	=B12-B13	=C12-C13	=D12-D13	=E12-E13
Cost per unit	85	=B15	=C15	=D15
Required Purchases	=B14*B15	=C14*C15	=D14*D15	=E14*E15
Operating expenses Budget
Req 3	=B9	=C9	=D9	=E9
Budgeted Sales	=B6	=C6	=D6	=SUM(B20:D20)
Variable operating rate	0.1	0.12	=C21
Budgeted Variable operating expenses	=B20*B21	=C20*C21	=D20*D21	=SUM(B22:D22)
Budgeted Fixed operating expenses	2000	=B23	=C23	=SUM(B23:D23)
Budgeted total operating expenses	=B22+B23	=C22+C23	=D22+D23	=E22+E23
General & Admin Expenses Budget
Req 4	=B19	=C19	=D19	=E19
Salaries	5200	=B28	=C28	=SUM(B28:D28)
Interest on long term note	=15000*3%*1/12	=B29	=15000*3%*1/12	=SUM(B29:D29)
Total budgeted General & Admin expenses	=SUM(B28:B29)	=SUM(C28:C29)	=SUM(D28:D29)	=SUM(E28:E29)
Schedule of cash receipts
Req 5	=B27	=C27	=D27	=E27
Sales collected in the month of sale	=B6*60%	=C6*60%	=D6*60%	=SUM(B34:D34)
Sales collected in the following month	=354*145*25%	=B6*25%	=C6*25%	=SUM(B35:D35)
Sales collected in the month after that	=240*145*15%	=354*145*15%	=B6*15%	=SUM(B36:D36)
Total cash collections	=SUM(B34:B36)	=SUM(C34:C36)	=SUM(D34:D36)	=SUM(E34:E36)

Cash budget
Req 7	=B33	=C33	=D33	=E33
Cash balance, beginning	5000	=B61	=C61	=B41
Cash receipts
Cash receipts from customers	=B37	=C37	=D37	=SUM(B43:D43)
Additional common shares issued	0	=100*10	0	=SUM(B44:D44)
Total Cash receipts	=SUM(B43:B44)	=SUM(C43:C44)	=SUM(D43:D44)	=SUM(E43:E44)
Total cash available	=B41+B45	=C41+C45	=D41+D45	=E41+E45
Less Cash payments for
Raw materials	26479	=B16	=C16	=SUM(B48:D48)
Variable operating expenses	=B22	=C22	=D22	=SUM(B49:D49)
Fixed operating expenses	=B23	=C23	=D23	=SUM(B50:D50)
Salaries	=B28	=C28	=D28	=SUM(B51:D51)
Dividends	0	0	5000	=SUM(B52:D52)
Short term loan interest	=B64*6%*1/12	=C64*6%*1/12	=D64*6%*1/12	=SUM(B53:D53)
Long term loan interest	=B29	=C29	=D29	=SUM(B54:D54)
Long term loan principle payments	0	0	1500	=SUM(B55:D55)
Purchase of equipment	8000	0	0	=SUM(B56:D56)
Taxes paid	300	=B57	=C57	=SUM(B57:D57)
Total cash payments	=SUM(B48:B57)	=SUM(C48:C57)	=SUM(D48:D57)	=SUM(E48:E57)
Preliminary cash balance	=B46-B58	=C46-C58	=D46-D58	=E46-E58
Additional loan (loan repayments)	=5000-B59	=5000-C59	=5000-D59	=SUM(B60:D60)
Ending Cash balance	=B59+B60	=C59+C60	=D59+D60	=E59+E60
Short-term Loan Balance	=B40	=C40	=D40	=E40
Loan Balance - Beginning of Month	1500	=B66	=C66	=B64
=A60	=B60	=C60	=D60	=SUM(B65:D65)
Loan Balance - End of Month	=SUM(B64:B65)	=SUM(C64:C65)	=SUM(D64:D65)	=SUM(E64:E65)
Income Statement
Req 8	=B63	=C63	=D63	=E63
Sales	=B6	=C6	=D6	=E6
Cost of goods sold	=B4*B15	=C4*C15	=D4*D15	=E4*E15
Gross Profit	=B70-B71	=C70-C71	=D70-D71	=E70-E71
Operating expenses
Variable operating expenses	=B49	=C49	=D49	=E49
Fixed operating expenses	=B50	=C50	=D50	=E50
Salaries expense	=B51	=C51	=D51	=E51
Insurance expense	=6000/12	=B77	=C77	=SUM(B77:D77)
Depreciation expense	=(16000+8000)/15*1/12	=B78	=C78	=(16000+8000)/15*3/12
Earnings before interest and taxes (EBIT)	=B72-SUM(B74:B78)	=C72-SUM(C74:C78)	=D72-SUM(D74:D78)	=E72-SUM(E74:E78)
Interest on short term note payable	=B53	=C53	=D53	=E53
Interest on long term note payable	=B54	=C54	=D54	=E54
Earnings before taxes (EBT)	=B79-SUM(B80:B81)	=C79-SUM(C80:C81)	=D79-SUM(D80:D81)	=E79-SUM(E80:E81)
Income tax expense	=B82*15%	=C82*15%	=D82*15%	=E82*15%
Net Income	=B82-B83	=C82-C83	=D82-D83	=E82-E83
Retained Earnings Statement
Req 9	=B69	=C69	=D69	=E69
Retained Earnings, Beginning Balance	2365	=B91	=C91	=B88
Add: Net Income	=B84	=C84	=D84	=E84
Less: Dividends	0	0	=-D52	=SUM(B90:D90)
Retained Earnings, Ending Balance	=SUM(B88:B90)	=SUM(C88:C90)	=SUM(D88:D90)	=SUM(E88:E90)

Balance Sheet
Req 10
Assets
Cash	=E61
Accounts Receivable	=(C6*15%)+(D6*40%)
Inventory	=D11*E15-0.2
Prepaid insurance	=3500-E77
Total Current Assets		=SUM(B96:B99)
Equipment	=16000+E56
Accumulated Depreciation	=-2133-E78
Equipment, net		=B101+B102
Total Assets		=SUM(C96:C103)
Liabilities & Stockholders Equity
Current Liabilties
Accounts Payable	=D16
Short term notes payable	=E66
Income taxes Payable	=1200+E83-E57
Total Current Liabilties		=SUM(B108:B110)
Long term notes payable		=15000-E55
Total Liabilties		=SUM(C111:C112)
Stockholders Equity
Common Stock	=10000+E44
Retained Earnings	=2365+E84-E52
Total Stockholders Equity		=SUM(B115:B116)
Total Liabilities & Stockholders Equity		=SUM(C113:C117)