Insight Glass makes sliding glass doors for two local construction companies and wants to prepare a master
Question:
Insight Glass makes sliding glass doors for two local construction companies and wants to prepare a 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 only 20 as of June 1, 2014 with a per unit cost of $624.
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 1st, 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,800.
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 30th 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 10th 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 1st (which represents 40% of May sales) is expected to be received in June.
Glass is purchased on account. Approximately 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 1st, 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 build a spreadsheet in Excel, which will enable you to prepare all the budgeted 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.
You may work individually or in a team (see syllabus). You must submit one computer file via e-mail by 4:00 pm on the due date indicated in the course syllabus. Attach your Excel file to the e-mail. The Excel file name should include your name(s) and the name of the company that you were assigned. You should receive a confirmation e-mail, indication that I could open you Excel file. It is your responsibility to follow up if you do not hear from me.
Grades will be based on completion of the initial project, linking the master budget and financial statement, appropriate format and the following directions.
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.Pro-forma financial statements for June
A. Multi-step Income Statement
i. Link the CGS to the production budget (average cost per unit).
ii. ii. Reconcile retained earnings at the bottom of income statement.
B. Classified and comparative balance sheet statement that show May and June numbers
9. 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.
(Below is the performance balance sheet)
Insight Glass Company
Perfomance Balance Sheet
May 31, 2015
Current Assets
Cash $43,000
Accounts recievable 88,000
Inventory--- Raw materials 15,000
Finished goods 12,480
Total Current assets $158,480
Land, building and equipment
Land 100,000
Building (net of depreciation) 997,625
Equipment (net of depreciation) 746,310 1,843,935
Total property, plant &equipment
125,585
Other assets $2,128,000
Liabilities
Current Liabilities
Accounts payable $28,500
Income tax payable 20,080
Total current liabilities $48,580
Long-term debt 575,000
Total liabilities 623,580
Stockholder’s Equity
Common stock, $4 per value, (100,000 400,000
Shares outstanding)
Additional Paid-in Capital 600,000
Retained earnings 504,240
Total stockholders’ Equity 1,504,420
Total Liabilities stockholder’s equity $2,128,000
Expert Answer:
1 Sales budget June Estimted sales 180 Retail price 1100 Total Revenue 198000 2 Production budget Ju... View the full answer
