accounting

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, 2019:

B&L Manufacturing

Balance Sheet

March 2019

Cash $40,000

Accounts Receivable 490,000

Inventory 204,000

Net Capital Assets 1,180,000

Total Assets 1,914,000

Accounts Payable 24,000

Common Stock 100,000

Shareholder’s Equity (SE) 1,790,000

Total Liabilities and SE 1,914,000

Inventory is made up of the following balances:

Raw Materials $ 24,000 4,800 units

WIP $ 60,000

Finished Goods $120,000 4,000 units

Recent and Projected Sales

$ Units

February $300,000 6,000

March 400,000 8,000

April 400,000 8,000

May 400,000 8,000

June 300,000 6,000

July 400,000 8,000

August 500,000 10,000

September 500,000 10,000

Credit accounts are collected 70% in the month following the sale and 30% 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 $10.00 of raw materials (representing 2.0 units of material

with a purchase cost of $5.00 per unit) and $14.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 $7 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.5 per direct labour hour. The desired finished goods inventory is 50%

of the next month’s sales. The desired raw materials inventory is 30% 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 $24,000 per month. The total depreciation

per month for manufacturing assets is $4,000.

Salaries, wages and commissions average 10% of sales, all other expenses excluding

depreciation is 5% of sales. Fixed selling and administrative expenses for rent, property

taxes and other items are $40,000 per month. Depreciation is $3,000 per month.

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

for in April.

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

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 $20,000 in

beginning cash. Income taxes can be ignored.

Required:

Prepare monthly budgeted income statements and cash flow statements for the quarter

(April, May & June) along with all the supporting budgets and a Balance Sheet as of June

30, 2019). 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: $30.27

Total Assets: $2,016,091

Income: $150,091