Question

The company desires a minimum ending cash balance each quarter on $35,000. The beard grooming products are sold to retailers for $20 each and the sales have been stagnant due to the Covid-19. However, the marketing department has been positive toward the end of the year due to season change and upcoming holiday. The marketing department has just sent you their forecasted quarter sales and marketing budget. Quarter 2020 Q4 2021 Q1 2021 Q2 2021 Q3 2021 Q4 2022 Q1 Sales in Unit 30,000 35,000 40,000 65000 68000 70,000 Marketing Expenses $35,000 $20,000 $20,000 $45,000 $45,000 $45,000 The increased sales volume before and during June and January is due to Father’s Day and holidays with AATB being a favorite. Ending finished goods inventories are supposed to be equal to 20% of the next quarter’s sales in units. AATB currently does its own assembly production in house. Each unit consists of 3 shaves and the cost of each is $1.50. Each unit needs 0.10 labour hour from assemble to finish package. The hourly pay rate to the assembling workers is $15 per hour. The production manager also required desired direct material ending inventory to 30% of the next quarter production. Purchases are paid for in the following manner: 50% in the quarter of the purchase and the remaining 50% paid in the quarter following the purchase. All sales to the distributors are made on credit terms with no discount (for now), and payable within 15 days. The AATB has determined that only 50% of sales are collected by the end of the quarter in which the sale occurred. An additional 30% is collected in the quarter following the sale, and the remaining 20% is collected in the second quarter following the sale. Bad debts have been negligible, supporting the credit terms as favorable. Below is a display of the AATB division monthly manufacture overhead and selling and administrative expenses: Manufacture Overhead Variable: Indirect labour $0.5 per labour hour Indirect Materials $0.2 per labour hour Fixed: Wages and Salaries$2,000 per month Utilities $1,500 per month Insurance $2,000 per month Depreciation $2,000 per month Selling and Administrative Variable: Sales Commissions $1 per unit Fixed (Monthly) : Wages and Salaries$22,000 Utilities $6,000 Insurance $1,200 Depreciation $1,500 Miscellaneous $3,000 Labour, Manufacture Overhead, and Selling and Administrative expenses are all paid during the month, in cash, with the exception of depreciation (of course). AATB will make a purchase of a parcel of land during the first quarter of 2021 for $22,500 cash. AATB contributes to the corporate dividend at a rate of $12,000 each quarter, payable in the first month of the following quarter. AATB’s balance sheet at the end of the third quarter is shown below: Assets Cash $14,000 Accounts receivable ($48,000 sales in Q2 and $152,000 in Q3 this year ) $200,000 Liabilities Accounts payable $85,700 An agreement with Bank of the West allows AATB to borrow in increments of $1,000 at the beginning of each month, up to a total loan amount of $550,000. The interest rate on these loans is 12% annually (pretty high considering market rates) but the interest is not compounded, meaning this is simple interest only. Required: Prepare a master budget for twelve months from Oct, 2020 to Oct, 2021. Include the following budget schedules and financial statements: 1) Master Budget 2) Cash Budget. Show the cash budget by month and in total. 3) Budgeted Income statement

Answer 1

	Q4	Q1	Q2	Q3	Q4
	2020	2021	2021	2021	2021	2022
Sales units	30000	35000	40000	65000	68000	70000
Sales price per unit	20	20	20	20	20	20
Total sales consideration	600000	700000	800000	1300000	1360000	1400000
Marketing expense	35000	20000	20000	45000	45000	45000
Inventory to be maintained	7000	8000	13000	13600	14000	0
Production Required	37000	36000	45000	65600	68400	56000
Direct Material ending inventory required	32400	40500	59040	61560	50400	0
Direct Material required for current month	111000	108000	135000	196800	205200	168000
Total direct material to be procured	143400	116100	153540	199320	194040	117600
Cost per unit	1.5	1.5	1.5	1.5	1.5	1.5
Direct Material costs	215100	174150	230310	298980	291060	176400
Labor hours	3700	3600	4500	6560	6840	5600
Cost per hour	15	15	15	15	15	15
Direct Labor costs	55500	54000	67500	98400	102600	84000

Inventory to be maintained	=next quarter's sales * 20%
Production required	=Closing inventory to be aintained + Current month's demand-opening inventory
Direct material ending inventory	=Next quarter's production *30% * 3 units per product
Direct Material for current month prodction	=production required * 3 units per product
Direct Material prcured	=Closing inventory to be maintained + current month's requirement-opening inventory
Labor hours required	=production required * 0.10 per product

Cashflow	Q4	Q1	Q2	Q3	Q4	Q1	Q2	Q3
	2020	2021	2021	2021	2021	2022	2022	2022
Payments for purchases
Payment for Q4 2020 Purchase	-107550	-107550
Payment for Q1 2021 Purchase		-87075	-87075
Payment for Q2 2021 Purchase			-115155	-115155
Payment for Q3 2021 Purchase				-149490	-149490
Payment for Q4 2021 Purchase					-145530	-145530
Payment for Q1 2022 Purchase						-88200	-88200
Receipt for Q4 2020 Sale	300000	180000	120000
Receipt for Q1 2021 Sale		350000	210000	140000
Receipt for Q2 2021 Sale			400000	240000	160000
Receipt for Q3 2021 Sale				650000	390000	260000
Receipt for Q4 2021 Sale					680000	408000	272000
Receipt for Q1 2022 Sale						700000	420000	280000
Marketing expenses payment	-35000	-20000	-20000	-45000	-45000	-45000	0
Wages payment	-55500	-54000	-67500	-98400	-102600	-84000
Balance	101950	261375	440270	621955	787380	1005270	603800	280000

x