Question

You were recently hired by LW’s Bottle Emporium Ltd. (“LW”) as financial controller. LW sells a high-end set of baby bottles including various sizes to meet the child’s needs throughout their early years. One of your first tasks is to prepare the master budget for the fourth quarter of 2020. To assist you in the preparation of the budget, and supporting schedules, you have been provided with the following information:

1. As of September 30, 2020 LW had the following balance sheet:

LW’s Bottle Emporium Ltd.
Balance Sheet
September 30, 2020
Cash	$       10,000		Accounts payable	$     100,640
Accounts receivable	371,250		Taxes payable	15,000
Inventory	133,200		Total current liabilities	115,640
Prepaid insurance	10,000		Long-term loan payable	250,000
Total current assets	524,450		Total liabilities	365,640
Property, plant and equipment	1,125,000		Common shares	50,000
Accumulated amortization	454,000		Retained earnings	779,810
Net property, plant and equipment	671,000		Total shareholders equity	829,810
Total assets	$ 1,195,450		Total liabilities and equity	$ 1,195,450

2. LW sells the bottle sets for $275 each. Recent and forecasted sales (in units) are as follows:

July (actual)                                                1,000

August (actual)                                          1,150

September (actual)                                   1,600

October                                                       1,200

November                                                   2,150

December                                                   2,400

January                                                           900

February                                                         850

3. Management has a policy of having enough bottle sets on hand at the end of each month to cover 60% of the next months projected sales. At the end of September, the company had 720 bottle sets in inventory. Each bottle set costs the company $185. Inventory purchases are paid 60% in the month of purchase and 40% the following month.

4. All sales are on credit with 30% of receivables being collected in the month of sale, 50% collected the month after sale and the final 20% collected two months after sale.

5. Fixed operating costs are expected to be $42,000 every month. Out of this amount, $1,000 is expensing of prepaid insurance (insurance is paid once a year on August 1^st) and $12,000 is depreciation. The company also expects variable operating costs to be $17 per bottle set sold.

6. Management plans to purchase a new delivery truck in October at a cost of $54,000. The company will pay cash.

7. A dividend of $11,000 will be declared and paid in December.

8. Interest is paid monthly on the long-term loan at a rate of 6% per year. The full principal amount will be repaid on September 30^th, 2026.

9. Income tax expense is calculated as 28% of the earnings before taxes. The company pays income tax installments of $10,000 per month.

10. The company must maintain a minimum cash balance of $9,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 7% per annum. Any cash above $9,000 at month end will be used to reduce any existing short-term loan.

Required:

Use a spreadsheet application such as Excel to complete this assignment. Each student is to create his/her own Excel file (from a blank Excel – I will be checking the file properties), and complete the assignment individually. Use formulas wherever possible – you will thank yourself! Your spreadsheet should be formatted to show amounts to the nearest dollar (no cents). Your instructor will tell you exactly what to submit and when, and will inform you as to penalties for late submission.

The items in the budget should appear in the following order:

1. The balance sheet for September 30, 2020 (as given).
2. A cash receipts schedule for October, November and December.
   Check figure: Cash receipts for October should be $382,250.
3. A purchases schedule in units for October, November and December.
   Check figure: October purchases should be 1,770 units.
4. A cash payments for purchases for October, November and December.

Check figure: October cash payment for purchases should be $297,110.

5. A cash payments schedule for October, November and December.
   Check figure: October’s total cash payments should be $411,760.
6. A cash budget for October, November and December, including a calculation of cumulative loan at the bottom.
   Check figure At the end of October the cash balance should be $9,000 and the cumulative loan should be $28,510.
7. The pro-forma income statements for October, November and December. You should also have a total column which totals all three months.
   1. Subtotals for EBIT and EBT should be included.
   2. List all expenses separately (do not combine).
   3. Show long-term and short-term interest separately.
   4. Hint: Cost of goods sold is not the same thing as purchases.

Check figure: October earnings after taxes should be $31,932.

8. A pro-forma retained earnings schedule for the quarter ended December 31^st.
   Check figure: Ending retained earnings should be $977,232.
9. A pro-forma balance sheet at December 31^st. Hint: Consider what will cause balances to change from the September 30, 2020 balance sheet. Some items will change even though a cash payment didn’t occur.
   Check figure: Total assets should be $1,454,286.

Answer 1

	Jul	Aug	Sept	oct	nov	dec	Jan	feb
Sales units	1000	1150	1600	1200	2150	2400	900	850
Price per unit	275	275	275	275	275	275	275	275
Sales value	275000	316250	440000	330000	591250	660000	247500	233750
	'=1000*275	'=1150*275	=1600*275	=1200*275	=2150*275	2400*275	=900*275	=850*275
Cash receipts on Sale
Jul	82500	137500	55000
	=275000*0.3	=275000*0.5	=275000*0.2
Aug		94875	158125	63250
		=316250*0.3	=316250*0.5	=316250*0.2
Sept			132000	220000	88000
			=440000*0.3	=440000*0.5	=440000*0.2
Oct				99000	165000	66000
				=330000*0.3	=330000*0.5	=330000*0.2
Nov					177375	295625	118250
					=591250*0.2	=591250*0.5	=591250*0.2 accounts receivable
Dec						198000	330000	132000
						=660000*0.3	=660000*0.5 accounts receivable	=660000*0.2 accounts receivable
	82500	232375	345125	382250	430375	559625	448250	132000

Production budget
Sales units	1000	1150	1600	1200	2150	2400	900	850
Desired closing stock	690	960	720	1290	1440	540	510	0
	=1150*0.6	1600*0.6	=1200*0.6	=2150*0.6	=2400*0.6	=2400*0.6	=850*0.6
Total units required	1690	2110	2320	2490	3590	2940	1410	850
	=1000+690	1150+960	=1600+720	=1200+1290	=2150+1440	=2400+540	=900+510
Beginning inventory[ending prev month]		690	960	720	1290	1440	540	510
Purchases[total units required-beginning inventory]		1420	1360	1770	2300	1500	870	340
Cost per bottle		185	185	185	185	185	185	185
Purchase value[Purchases*cost per bottle]		262700	251600	327450	425500	277500	160950	62900
Payments for purchases
Sept			150960	100640
			=251600*0.6	=251600*0.4
Oct				196470	130980
				=327450*0.6	=327450*0.4
Nov					255300	170200
					=425500*0.6	=425500*0.4
Dec						166500	111000
						=277500*0.6	=277500*0.4 accounts payable
Total payments				297110	386280	336700	111000
Operating expenses
Sales units	1000	1150	1600	1200	2150	2400	900	850
Variable costs	17	17	17	17	17	17	17	17
Variable expense [sales units*variable costs]	17000	19550	27200	20400	36550	40800	15300	14450
Fixed	42000	42000	42000	42000	42000	42000	42000	42000
Non-cash	12000	12000	12000	12000	12000	12000	12000	12000
Prepaid	1000	1000	1000	1000	1000	1000	1000	1000
Cash payments fixed exp[fixed costs-non-cash expense-prepaid expenses]	29000	29000	29000	29000	29000	29000	29000	29000
Total operating cash payments	46000	48550	56200	49400	65550	69800	44300	43450

Cash Budget
				Oct	Nov	Dec
Beginning balance				10000	9000	9000
Cash receipts				382250	430375	559625
Total cash available for payments[beggining+cash receipts]				392250	439375	568625
Cash payments
Purchases				297110	386280	336700
Operating expenses				49400	65550	69800
Truck purchase				54000
Dividend paid						11000
Interest on long-term loan				1250	1250	1250
Income tax expense				10000	10000	10000
Total payments				411760	463080	428750
Cash receipts in excess of payments				-19510	-23705	139875
Finance
Borrowings				28510	32871
Repayments						-61381
Interest on borrowings					-166	-358
					=28510*0.07/12	=61381*0.07/12
Total finances				28510	32704.69	-61739
End balance				9000	9000	78136

Income statement	Oct	Nov	Dec	Total
Sales	330000	591250	660000	1581250
Cost of Goods sold:
opening inventory	133200	238650	266400	133200
Add: Purchases	327450	425500	277500	1030450
Less: closing inventory	238650	266400	99900	99900
Total cost of goods sold	222000	397750	444000	1063750
Gross margin	108000	193500	216000	517500
Less: operating expenses
Variable operating costs	20400	36550	40800	97750
Fixed operating costs	42000	42000	42000	126000
Total operating costs	62400	78550	82800	223750
EBIT	45600	114950	133200	293750
Interest on long term loan	1250	1250	1250	3750
Interest on short term loan		166	358	524
Total finance costs	1250	1416	1608	4274
EBT	44350	113534	131592	289476
Income tax expense	12418	31790	36846	81054
Earnings after taxes	31932	81744	94746	208422

Retained earnings
Beginning balance	779810
Net income	208422
Dividends	-11000
End balance	977232

Balance sheet
Assets
Cash	78136
Accounts receivables	580250
Prepaid Inusrance	7000	=10000-3000(1000 each for 3 months)
Inventory	99900
Total current assets	765286
P,P&E	1179000	=1125000+54000 purchases
Accumulated amortisation	490000	=454000+360000(12000 each for 3 months)
Net PP&E	689000
Total Assets	1454286
Liabilities
Accounts payable	111000
Taxes payable	66054
Total current liabilities	177054
Long term loan payable	250000
Total liabilities	427054
Common shares	50000
Retained earnings	977232
Total liabilities and equity	1454286