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 February March April May June July August September October November December TOTAL 1000 3000 3000 2250 10000 7750 1000 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 594,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 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: A. Prepare the following for WBC for the second quarter of the year (April, May, June). Include cach month in your projections as well as the 2nd Quarter totals for each budget. 1. Sales Budget 2. Production Budget 3. Raw Materials Purchases Budget 4. Direct Labor Budget 5. Manufacturing Overhead Budget 6. Budgeted Cost of Cost of Goods Sold 7. Selling and Administrative Expenses Budget 8. WBC's Pro Forma Income Statement for the 2nd Quarter. 9. Cash Budget for the 2nd Quarter Section Title April May | June 7,750 Sales Budget 10,000 Projected Sales X Sales Priceper unit 2,250 $40 Total 20,000 $40 $40 $40 Total Sales (Budgeted) $90,000 $400,000 $310,000 $800,000 12 Cash Reciepts Collections Current Month Collections From Prior Month Total Cash Collections for Month $54,000 $36,000 $90,000 $240,000 $160,000 $400,000 $186,000 $124,000 $310,000 $480,000 $320,000 $800,000 16 Production April June May 10,000 7.750 20,000 Scheduled Sales (Completed Units) Plus: Desired Ending Inventory (Completed Units) Less Beginning Inventory (Completed Units 3,000 2,325 6,000 19 Budget 20 Finished Goods Scheduled Production (Completed Units) 046 Sx 24750 AB D E F G H I J K, April May June Total 2.250 10,000 7,750 20,000 Scheduled Production X's Amount of Raw Material per Completed Unit (in Fe 3 26 Raw Materials 27 Budget 6,750 2,700 30,000 12,000 23,250 93,000 60,000 24,000 Total raw materials needed for production Plus: Ending Raw Materials Inventory Less: Beginning Raw Materials Inventory Materials for Purchase X's Acquisition Price per foot - Raw Materials Cost of Materials Needed to Purchase Cash Payments for Raw Materials April May June Total 2,250 10.000 7,750 20,000 40 Direct Labor Production (Completed Units) Direct Labor Per Unit Total Direct Labor Hours required X's Average Hourly Labor Rate 2250 11.00 10000 1 11.00 7750 11.00 $ S S Total Direct Labor Cost $ 24,750.00 $ 110,000.00 $ 85,250.00 $220,000.00 Cash Payments Labor - Current Month $24,750.00 $110,000.00 $85,250.00 $220,000.00 April May | June Total Production (Competed Units) X's Overhead rate 50 Manufacturing 51 Overhead Total Overhead Cash Payments - Overhead April May June Total 58 Cost of Goods 59 Sold Direct Materials per unit Direct Labor per unit X's Budgeted Sales (units) Total Material and Labor Manufacturing Overhead Cost of Goods Sold April 2,250 $11,250 Budget Sales in Units X's Variable Selling and Admin Expense per unit May 11 June 10,000 || 7.750 $ 50,000.00 || $ 38,750.00 | Total 20,000 $ 100,000.00 Selling & Admin Expense Fixed Selling and Admin Expenses $8,000 $ 8,000.00 $ 8,000.00 $ 24,000.00 Total Selling and Admin Expenses $19,250 $ 58,000.00 $ 46,750.00 $ 124,000.00 Selling and Admin Cash Payments Selling and Admin Cash Payments Window Blinds Company Pro Forma Income Statement Second Quarter 2020 REVENUE COST OF GOODS SOLD GROSS PROFIT SELLING, GENERAL & ADMINISTRATIVE EXPENSES NET INCOME NET INCOME CASH BUDGET April May II June Quarter Beginning Cash Balance Monthly Cash Receipts Total Cash Available Cash Payments Raw Materials Purchases Labor Overhead S & A Expenses Interest Total Payments Cash from Operations Surplus (Shortage) Borrowing (Repayment) Ending Cash Balance 112 113 114 Surplus (Shortage) Borrowing (Repayment) Ending Cash Balance Units Value B1 Raw Material inventory end of 2nd quarter Finished Goods inventory end of 2nd quarter B2 Balance Accounts Receivable at end of 2nd quarter 120 B3 Yes 122 Did the company borrow money during the 2nd quarte If YES what was the amount? Did the company have any debt at the end of the quart If YES what was the amount? Yes 123 124 125 126 127 133 134 135 136 Sheet1