In: Accounting
You are the Controller for the Window Blinds Company (WBC), a firm that makes wooden window blinds. Your firm just reached an agreement with the local Home Depot to begin selling your products on a test basis in their store for the months of April, May and June 2020 (second quarter). Management expects sales to increase as a result of this test and you realize that a sufficient quantity of product must be produced to avoid stock outs.
You have requested your Accounting Manager to prepare a Master Budget for the second quarter of 2020 (April, May and June) to assist in managing operations, determining how much material needs to be ordered, and how much labor will be required.
In addition, you also requested the Accounting Manager to prepare a pro-forma income statement for the 2nd Quarter (April, May and June) that will to be used in evaluating the results of the test program.
You should prepare your budgets and income statement using Excel. The attached format for the Excel spreadsheet must be used. The completed worksheet must contain formulas and cell references for all calculations.
Information
Sales and Cash Collection Information
WBC sales staff have prepared a schedule of the budgeted sales for the months of January through December 2019. Expected unit sales for the months of 2019 are listed below:
January |
1000 |
February March |
3000 3000 |
April |
2250 |
May |
10000 |
June |
7750 |
July |
1000 |
August September October November December TOTAL |
1500 1000 2000 1000 2500 36,000 |
WBC sells the blinds to Home Depot and its other customers at a price of $40 each. WBC sells all the blinds on account and expects to collect 60% of the cash in the month of the sale and 40% in the month after the sale.
Production and Material Budget Information
Finished Goods
Window Blinds Company (WBC) manufactures window blinds from wood. Each blind uses 3 feet of “high-quality” teak hardwood, which costs WBC $3 per foot. WBC likes to end the month with a finished goods inventory equal to 30% of the planned sales for the next month.
Raw Materials
In addition to the finished goods inventory, WBC also has an inventory of Teak wood in a climate controlled warehouse. WBC like to have in its warehouse a beginning inventory equal to 40% of the teak wood it will need for the month’s production.
WBC pays for the material it purchases in the month of purchase.
Labor Budget Information
WBC pays its workers $11 per hour and each blind takes 1 hour of labor to complete. All wages are paid in the month incurred.
Manufacturing Overhead Information
Total manufacturing overhead is estimated to be $94,500. Manufacturing Overhead is applied equally to each product produced.
Overhead is paid in the month incurred.
Selling and Administrative Costs
Selling and administrative expenses are a combination of fixed and variable costs. Fixed Selling and Administrative costs are estimated at $8,000 per month and variable selling and administrative costs are $5.00 per unit sold.
Selling and administrative costs are paid in the month incurred.
Cash Management
WBC starts the month of April with $2,000 in the bank. Management would like to maintain a minimum of $10,000 in the bank. WBC just opened a line of credit with the bank that they can draw upon on the last day of the month. Interest is paid at the end of the month and the line of credit can be paid back in any increment. Interest is charged at 1% per month on the outstanding balance borrowed.
Required:
1. What will be the number of units and the cost of the Raw Materials and Finished Goods ending inventories that will appear on the Balance Sheet at the End of the 2nd Quarter?
2. What will be the balance in Accounts Receivable at the end of the 2nd Quarter?
3. Will WBC need to consider borrowing any cash from a bank during the 2nd Quarter?
The beginning cash balance is $2,000 and WBC would like to end each month with $10,000 in the bank.
To accomplish this, how much should be borrowed and how much will WBC owe the bank on June 30?
A.
1.
Sales Budget | ||||
April | May | June | Quarter | |
Budgeted unit sales | 2,250 | 10,000 | 7,750 | 20,000 |
Sales price per unit | $ 40 | $ 40 | $ 40 | $ 40 |
Budgeted sales revenue | $ 90,000 | $ 400,000 | $ 310,000 | $ 800,000 |
2.
Production Budget | |||||
April | May | June | Quarter | July | |
Budgeted unit sales | 2,250 | 10,000 | 7,750 | 20,000 | 1,000 |
Add: Desired ending inventory | 3,000 | 2,325 | 300 | 300 | 450 |
Total | 5,250 | 12,325 | 8,050 | 20,300 | 1,450 |
Less: Beginning inventory | 675 | 3,000 | 2,325 | 675 | 300 |
Required production in units | 4,575 | 9,325 | 5,725 | 19,625 | 1,150 |
3.
Raw Materials Purchases Budget | ||||
April | May | June | Quarter | |
Qty needed in production ( ft ) | 13,725 | 27,975 | 17,175 | 58,875 |
Add: Desired ending inventory | 11,190 | 6,870 | 1,380 | 1,380 |
Total | 24,915 | 34,845 | 18,555 | 60,255 |
Less: Beginning inventory | 5,490 | 11,190 | 6,870 | 5,490 |
Budgeted purchases in ft. | 19,425 | 23,655 | 11,685 | 54,765 |
Cost per ft. | $ 3 | $ 3 | $ 3 | $ 3 |
Budgeted Cost of RM Purchases | $ 58,275 | $ 70,965 | $ 35,055 | $ 164,295 |
4.
Direct Labor Budget | ||||
April | May | June | Quarter | |
Budgeted production in units | 4,575 | 9,325 | 5,725 | 19,625 |
Labor hour per unit | 1 | 1 | 1 | 1 |
Budgeted labor hours | 4,575 | 9,325 | 5,725 | 19,625 |
Labor hour rate | $ 11 | $ 11 | $ 11 | $ 11 |
Budgeted Cost of Direct Labor | $ 50,325 | $ 102,575 | $ 62,975 | $ 215,875 |