BA213

Project Instructions

Insight Glass Company

Insight Glass makes sliding glass doors for two local construction companies and wants to master budget for the next month of operation, June 2014.

The sales department estimates that it can sell 180 doors in June.Each door retails for $1,100.In order to avoid delays in shipping, management wants to maintain ending inventory each month equal to 10% of the estimated unit sales in that month.Beginning inventory of finished doors is expected to be 20 units as of June 1, 2014 with a per unit cost of $624. Note: The per unit cost of doors produced during June may vary slightly from the per unit cost of June's beginning Finished Goods Inventory (given as $624).In other words, you will need to arrive at a new per unit cost for the month of June.

Each door takes 36 square feet of glass and 4 hours of direct labor.Glass purchases are estimated at $10 per square foot and direct labor averages $16 an hour, including benefits.As of June 1^st, Insight Glass estimates it will have 1500 square feet of glass in raw materials inventory and would like to have ending inventory of 1000 feet.Variable overhead costs are estimated at $100 per door.Fixed overhead for the month is estimated to be $17,010.

Insight Glass anticipates selling and administrative costs of $18,750 monthly and the monthly interest cost on its long-term debt is 1% of the outstanding balance, paid on the 30^th of each month.The principal payment on the debt is $25,000 per month.Insight Glass, as a corporation, expects to pay 40% of its net income in income taxes.Monthly estimates are sent to the appropriate taxing authority by the 10^th of the next month (therefore, income taxes payable as of 5/31/14 will be paid on 6/10).

Monthly depreciation of the building and the equipment is $8,000 and $10,000 respectively (this is the sales/administrative portion of depreciation and not a part of overhead production costs).

All sales are on account.However, in estimating cash flows, Insight Glass expects 60% of the current month sales to be received by the end of the month and the balance to be collected in the next month.Therefore, all the Accounts Receivable owing at June 1^st (which represents 40% of May sales) is expected to be received in June.

Glass is purchased on account.Exactly 70% of the purchases are paid in the current month and the balance is paid early in the next month.The balance of Accounts Payable owing at June 1^st, will be paid in June.Assume all other expenses (both production and administrative) are paid in the month incurred.

The Board of Directors for the company plans to declare and pay a $.50 per share cash dividend during the month of June.

Instructions:

You are to use the Excel project template on the course Moodle site, which will enable you to prepare all the budgets and statements listed under the "Required" section below.The budgets should be "linked", using cell references, etc.For instance, total sales revenue on the Income Statement should be referenced from the Sales Budget so that if the projected units sold in June changed, total sales revenue on the Income Statement would change in kind.

Required:

1. Sales budget for June
2. Production budget for June in units.
3. Direct Materials budget for June.
4. Direct Labor budget for June.
5. Manufacturing Overhead budget for June.
6. Cost of Goods Sold budget for June.
7. Cash Receipts & Cash Disbursements budget for June
8. Cash Budget for June
9. Pro-forma financial statements for June
10. Multi-step Income Statement

i.Link the CGS to the production budget (average cost per unit).

ii.Reconcile retained earnings at the bottom of income statement.

1. Classified Balance Sheet
2. Management is unsure of the projected sales figure and would like to know how many units need to be sold for the company to break-even.

What to Print and Turn In:

Each student must submit hard copies of the following on or before the due date indicated in the course syllabus:

1)From the "Budgets" tab of your project template, the print ranges have been set.Three separate pages will print up for these elements.

2)Print the Proforma Income Statement

3)Print the Proforma Balance Sheet

4)Make sure your name appears on the top sheet, and staple all pages together.

Grades will be based on completion of the initial project, linking the master budget and financial statement, appropriate format and the following directions.

BA213 Insight Glass Company Prepare a master budget for the month of June 2014 Data Table Part 1 Insight Glass Company Company Name Insight Glass Company Sales Budget Budgeted Sales, in units For the Month Ending June 30, 2014 Expected Sales price Beginning Inv. FG (in units): Budgeted Sales, Door Units 180 Ending Inv. of FG desired (in units): =10%* Sales Price per Unit S 1,100.00 *Desired El in Units is 10% of current's month sales Total Sales 198,000.00 Beg Inv, Direct Material (glass) in sq ft Ending Inv, Glass in sq ft Insight Glass Company Square feet of glass required per unit Production Budget Cost Per sq ft glass For the Month Ending June 30, 2014 Direct Material (glass) Cost per unit Budgeted Sales, Doors 180 Add Desired Ending Inventory 18 Direct Labor Hours per unit Total Units Needed 198 Direct Labor Cost per Hour Less Beginning Inventory 20 Budgeted Doors to Produce in June 178 Variable OH per Unit Fixed OH Insight Glass Company Insight Glass Company Direct Materials Budget Direct Labor Budget For the Month Ending June 30, 2014 For the Month Ending June 30, 2014 Budgeted Units to Produce 178 Budgeted Units to Produce 178 Direct Material Cost per Unit 360 Direct Labor Hours per Unit 4.0 Direct Materials Needed for Production 64,080 Direct Labor Hours needed 712 Add Value of Desired Direct Materials in Ending Inv. 10,000.00 Direct Labor Cost per Hour $ 16.00 Total Direct Materials Needed 74,080 Budgeted Direct Labor Cost S 11,392.00 Less: Value of Direct Materials in Beginning Inv. 15,000.00 Budgeted Purchases of Direct Materials 59,080Total Projected Manufacturing Costs per Unit Insight Glass Company Manufacturing Overhead Budget Direct Materials Direct Labor (for 4 hours) in | in un un For the Month Ending June 30, 2014 64.00 Allocated MOH (for 4 hours) Budgeted Units to Produce 178 Total Projected Cost per Unit 64.00 VOH per Door Budgeted VOH Ending Inv. of FG desired (in units): Budgeted FOH Total Projected Cost per Unit 64.00 Budgeted Manufacturing Overhead Costs Ending Inv. of FG: Direct Labor Hours 712 Predetermined MOH Allocation rate (per hour) = $ Insight Glass Company BMOHC/DLHR per unit Cost of Goods Sold Budget For the Month Ending June 30, 2014 Insight Glass Company Beginning Inventory Selling & Administrative Expense Budget 0 Units Cost per unit: S For the Month Ending June 30, 2014 Doors Produced and sold in June 2014 General Selling & Admin 0 units Cost per unit: $ 64.00 S Depreciation - Building Depreciation - Equipment Total Budgeted Cost of Goods Sold S Total Budgeted S & A expenseInsight Glass Company Data Table - Part 2 Cash Budget Annual Interest Rate on Debt For the Month Ending June 30, 2014 Debt from Balance Sheet Monthly Interest on debt (=1): S Beginning Cash Balance Cash Receipts Income tax rate A/R balance on May 31, 2014 General Selling & Admin June Sales, 60% collected in same month Depreciation - Building Depreciation - Equipment Cash Available S Income Tax Payable from Balance Sheet Cash Payments Debt Principal Repayment by Month Accounts Payable fr. May 31 to be paid off June Purchases, 70% paid in same month A/R collections % in month of sale Direct Labor 11,392.00 A/P payment % in month of purchase MOH - Variable MOH - Fixed Dividends per share Selling & Admin Number of shares outstanding Income Taxes Interest Expense S 11,392.00 Ending Cash before Financing $ (11,392.00) Cash Reciepts from Customers Financing Total Sales Forecast for June 198,000.00 Principal Repayments Dividends declared and paid Cash Receipts A/R balance on May 31, 2014 Ending Cash Balance S (11,392.00 June Sales, 60% collected in same month S Cash Disbursements Total Direct Materials purchases S 59,080 Cash Payments Accounts Payable fr. May 31 to be paid off June Purchases, 70% paid in same month S Direct Labor 11,392.00 MOH - Variable MOH - Fixed Selling & Admin ncome Taxes to be paid (from May) Interest Expense Principal Repayments Dividends Paid