Question

Bart and Lisa operate a manufacturing operation in southern New Brunswick. They make a single product. In order to help ensure a successful year they have engaged you to prepare a complete master budget for the upcoming quarter. Lisa is a qualified engineer but not overly sophisticated in financial matters. Bart handles the design and marketing of their company’s products.

You have gathered the following information as of March 31, 2020:

B&L Manufacturing

Balance Sheet

March 31, 2020

Cash                                 $     60,000

Accounts Receivable               624,000

Inventory                                 302,587

Net Capital Assets               1,180,000                      

Total Assets                       2,166,587

Accounts Payable                     72,000

Common Stock                       200,000

Shareholder’s Equity (SE)   1,894,587

Total Liabilities and SE      2,166,587          

Inventory is made up of the following balances:

Raw Materials             $ 28,800         6,400 units

WIP                             $100,000

Finished Goods           $173,787         4,800 units

Recent and Projected Sales

                                                $                      Units

February                      $360,000                     6,000

March                          480,000                     8,000

April                            480,000                     8,000

May                               480,000                     8,000

June                             360,000                     6,000  

July                             420,000                     7,000

August                        600,000                   10,000

September                   600,000                   10,000

Credit accounts are collected 60% in the month following the sale and 40% in the next month following. There are no bad debts. Collections are current; the March A/R balance is made up of uncollected amounts from February and March sales. You may assume no changes to the WIP balances.

Each unit produced requires $9.00 of raw materials (representing 2 units of material with a purchase cost of $4.50 per unit) and $20.00 of direct labour (2 hours of direct labour are required for each unit). There is a minimum of 5,000 labour hours per month regardless of how much time is worked. All labour is paid at $10 per hour (i.e. no overtime is paid.) Overhead is applied on the basis of direct labour hours. Variable overhead is estimated to be $1.50 per direct labour hour. The desired finished goods inventory is 60% of the next month’s sales. The desired raw materials inventory is 40% of the next month’s production requirements. All purchases are paid in the month following the purchase and all A/P is current.

Fixed manufacturing overhead is estimated at $30,000 per month including total depreciation per month for manufacturing assets of $5,000.

Salaries, wages and commissions average 10% of sales, all other administrative expenses excluding depreciation is 5% of sales. Fixed selling and administrative expenses for rent, property taxes, depreciation and other items are $50,000 per month. Depreciation is $3,000 per month.

There is a planned acquisition of a new machine in April for $150,000 which will be paid for in April.

A dividend of $100,000 will be paid in June.

Any borrowing the company makes are effective at the beginning of the month and all repayments are made at the end of the month. Ignore interest for the purposes of this assignment. The company does not want to begin a month with less than $30,000 in beginning cash. Income taxes can be ignored.

Required:

Prepare monthly budgeted income statements and cash flow projections for the quarter (April, May & June) along with all the supporting budgets and a Balance Sheet as of June 30, 2020). Each budget component should be prepared on a separate sheet. This must be prepared using Excel (DO NOT USE ANOTHER SPREADSHEET PROGRAM) and submitted electronically via email. PLEASE INCLUDE THE NAMES OF ALL GROUP MEMBERS ON THE FIRST SHEET OF YOUR EXCEL FILE. AS PER COURSE OUTLINE, THIS ASSIGNMENT MUST BE DONE IN TEAMS OF 2-3.

HINT: If everything is correct you should get the following numbers:

Cost per unit of Finished Goods:        $36.21

Total Assets:                                       $2,237,384

Net Income:                                        $175,477

Answer 1

	UNITS
	8000	8000	6000	7000	10000	10000
	APRIL	MAY	JUNE	JULY	AUG	SEPT
SALES	480000	480000	360000	420000	600000	600000
OPENING CASH IN HAND	60000	0	23920	65640	68920	30000
CASH COLLECTED FROM ACCOUNTS RECEIVABLE	432000	480000	480000	408000	396000	528000
LOAN TAKEN	88000				40600
TOTAL CASH AVAILABLE	580000	480000	503920	473640	505520	558000
CASH PAID FOR MATERIAL PURCHASES	72000	67680	60480	67320	83520	68400
DIRECT LABOUR CHARGES	160000	136000	132000	176000	200000	80000
VARIABLE OVERHEADS	24000	20400	19800	26400	30000	12000
FIXED OVERHEADS	25000	25000	25000	25000	25000	25000
(EXCLUDING DEPRECIATION OF $5000 PER MONTH)
SALARIES, WAGES AND COMMISISION	48000	48000	36000	42000	60000	60000
(10 % OF SALES)
OTHER ADMINISTRATIVE EXPENSES	24000	24000	18000	21000	30000	30000
(5% OF SALES)
FIXED SELLING ADMINISTRATION	47000	47000	47000	47000	47000	47000
(EXCLUDING DEPRECIATION OF $3000 PER MONTH)
PURCHASE OF NEW MACHINE	150000
PAYMENT OF DIVIDEND			100000
LOAN REPAID		88000
TOTAL CASH PAYMENTS	550000	456080	438280	404720	475520	322400
DESIRED CLOSING CASH	30000	30000	30000	30000	30000	30000
CLOSING CASH IN HAND	30000	23920	65640	68920	30000	235600

	FEB	MAR	APRIL	MAY	JUNE	JULY	AUG	SEPT
SALE	360000	480000	480000	480000	360000	420000	600000	600000
CASH COLLECTED
60% IN NEXT MONTH		216000	288000	288000	288000	216000	252000	360000
40% IN NEXT MONTH FOLLOWING			144000	192000	192000	192000	144000	168000
SALE OF FEB WILL BE COLLECTED
60 % IN MARCH AND 40% IN APRIL
		216000	432000	480000	480000	408000	396000	528000
		MARCH	APRIL	MAY	JUNE	JULY	AUG	SEPT
MATERIAL PURCHASES		72000	67680	60480	67320	83520	68400	21600
$ 9 PER UNIT
ALL PURCHASES ARE PAID IN THE MONTH
FOLLOWING THE MONTH OF
PURCHASES
CASH PAYMENT WILL BE LIKE THIS			72000	67680	60480	67320	83520	68400

	SALE	DESIRED CLOSING STOCK	UNITS TO BE PRODUCED	OPENING STOCK	UNITS PRODUCED	LABOUR HRS PER UNIT	TOTAL LABOUR HOURS	LABOUR COST PER UNIT	VARIABLE OVERHEAD(PER DIRECT LABOUR HOUR)	TOTAL VARIABLE OVERHEADS
FEB	6000	4800	10800	0	10800	2	21600	10	216000	1.5	32400
MAR	8000	4800	12800	4800	8000	2	16000	10	160000	1.5	24000
APRIL	8000	4800	12800	4800	8000	2	16000	10	160000	1.5	24000
MAY	8000	3600	11600	4800	6800	2	13600	10	136000	1.5	20400
JUNE	6000	4200	10200	3600	6600	2	13200	10	132000	1.5	19800
JULY	7000	6000	13000	4200	8800	2	17600	10	176000	1.5	26400
AUG	10000	6000	16000	6000	10000	2	20000	10	200000	1.5	30000
SEPT	10000	0	10000	6000	4000	2	8000	10	80000	1.5	12000
DESIRED CLOSING STOCK=60% OF NEXT MONTH SALES
	MATERIAL REQUIRED PER UNIT	UNITS PRODUCED	TOTAL MATERIAL CONSUMED	DESIRED CLOSING STOCK	OPENING STOCK	MATERIAL TO BE PURCHASED (IN UNITS)	RATE PER UNIT	MATERIAL PURCHASES
FEB	2	10800	21600	6400	0	28000	4.5	126000
MAR	2	8000	16000	6400	6400	16000	4.5	72000
APRIL	2	8000	16000	5440	6400	15040	4.5	67680
MAY	2	6800	13600	5280	5440	13440	4.5	60480
JUNE	2	6600	13200	7040	5280	14960	4.5	67320
JULY	2	8800	17600	8000	7040	18560	4.5	83520
AUG	2	10000	20000	3200	8000	15200	4.5	68400
SEPT	2	4000	8000	0	3200	4800	4.5	21600
DESIRED CLOSING STOCK=40% OF NEXT MONTH PRODUCTION