Required: (12 PTS) 1. Save a copy of this original budget, so you have a basis of comparison. 2. Input the projections below.All the projections should be entered before the questions are answered in #3. 3. There is insufficient info to create a complete cash budget, but the following calculations can be made BEFORE and AFTER the projections Change in Expected Cash Collections for the year based on the projections. $____________ indicate increase or decrease Change in Expected Cash Payments(raw mateirals) for the year based on the projections. $____________ indicate increase or decrease Change in Budgeted Labor paymentsfor the Year based on the projections. $____________ indicate increase or decrease 4. Based on the changes in the above 3 budgets, what is the overall cummulativeeffect of the projections in dollar terms? $____________ indicate increase or decrease Projections: Budgeted Sales increase 5% in Q 1-3, 3% increase inQ4. In the first 2 quarters next year, there is a 1% increase each quarter. A drop in sales price of $0.97 is what is expected to drive up sales units. More aggressive collection efforts are being made to increase the % to 80% in current quarter, 20% next quarter. A better supplier has been found to decrease raw material waste in production by 18%, so less material is needed. But the cost of these betterraw materials is expected to increaseprices by 12%. The supplier is requestion more prompt payment so that 75% of our direct materials purchases must be paid off in the month of purchase; 25% the following month. Technology has reduced labor hours required per unit of production by 15%. More highly skilled employees are needed to run the high tech equipment. Avg wage rates will increase to $17.85/hour. Only theitems stated are changing. The other components of the budgets are unchanged. ":"tree,

Required: Select Budgets that make up the Master Budget. The budgets are linked with Excel formulas. Data Year 1 by Quarter 1 2 Budgeted unit sales 42,000 Selling price per unit Year 2, 1st 2 Quarters 3 46,500 4 40,000 1 38,000 2 46,000 48,000 $27.16 per unit (12 PTS) 1. Save a copy of this original budget, so you have a basis of comparison. 2. Input the projections below. All the projections should be entered before the questions are answered in #3. 3. There is insufficient info to create a complete cash budget, but the following calculations can be made BEFORE and AFTER the projections Change in Expected Cash Collections for the year based on the projections. $___________indicate increase or decrease Change in Expected Cash Payments (raw mateirals) for the year based on the projections. $___________indicate increase or decrease Change in Budgeted Labor payments for the Year based on the projections. $___________indicate increase or decrease $___________ Accounts receivable, beginning balance Sales collected in the quarter sales are made Sales collected in the quarter after sales are made Desired ending finished goods inventory is $65,000 75% 25% 30% of the budgeted unit sales of the next quarter 4. Based on the changes in the above 3 budgets, what is the overall cummulative effect of the projections in dollar terms? Finished goods inventory, beginning 12,000 units Projections: Budgeted Sales increase 5% in Q 1-3, 3% increase in Q4. In the first 2 quarters next year, there is a 1% increase each quarter. A drop in sales price of $0.97 is what is expected to drive up sales units. More aggressive collection efforts are being made to increase the % to 80% in current quarter, 20% next quarter. A better supplier has been found to decrease raw material waste in production by 18%, so less material is needed. But the cost of these better raw materials is expected to increase prices by 12%. The supplier is requestion more prompt payment so that 75% of our direct materials purchases must be paid off in the month of purchase; 25% the following Technology has reduced labor hours required per unit of production by 15%. More highly skilled employees are needed to run the high tech equipment. Avg wage rates will increase to $17.85/hour. Only the items stated are changing. The other components of the budgets are unchanged. Raw materials required to produce one unit 5.00 pounds Desired ending inventory of raw materials is 10% of the next quarter's production needs Raw materials inventory, beginning 23,000 pounds Raw material costs $0.80 per pound Raw materials purchases are paid 60% in the quarter the purchases are made and 40% in the quarter following purchase Accounts payable for raw materials, beginning balance $81,500 Budgeted labor hours per unit produced 6.00 hours Labor wage rate $16.25 per hour Construct the sales budget 1 Budgeted unit sales Selling price per unit Total sales 42,000 $27.16 $1,140,720 Construct the schedule of expected cash collections 4 $ 38,000 $27.16 $1,032,080 2 3 4 65,000 855,540 $ Second-quarter sales $ $ 947,205 $ $ Fourth-quarter sales 920,540 $ Construct the production budget 1,140,720 1,262,940 271,600 $ 1,086,400 774,060 315,735 814,800 $ $ 65,000 285,180 Third-quarter sales 1,232,385 $ 1,130,535 $ 1,045,660 $ 4,329,120 42,000 13,950 55,950 12,000 43,950 Year 1 by Quarter 2 3 46,500 40,000 12,000 11,400 58,500 51,400 13,950 12,000 44,550 39,400 4 38,000 21,000 59,000 11,400 47,600 Year 166,500 21,000 187,500 12,000 175,500 43,950 5.00 219,750 22,275 242,025 23,000 219,025 $0.80 $175,220 Year 1 by Quarter 2 3 44,550 39,400 5.00 5.00 222,750 197,000 19,700 23,800 242,450 220,800 22,275 19,700 220,175 201,100 0.80 0.80 $176,140 $160,880 4 47,600 5.00 238,000 36,500 274,500 23,800 250,700 0.80 $200,560 Year 175,500 5.00 877,500 36,500 914,000 23,000 891,000 0.80 $712,800 1 Budgeted unit sales Add desired finished goods inventory Total needs Less beginning inventory Required production Construct the raw materials purchases budget 1 Required production (units) Raw materials required to produce one unit Production needs (pounds) Add desired ending inventory of raw materials (pounds) Total needs (pounds) Less beginning inventory of raw materials (pounds) Raw materials to be purchased Cost of raw materials per pound Cost of raw materials to be purchased Construct the schedule of expected cash payments 1 Accounts payable, beginning balance First-quarter purchases Second-quarter purchases Third-quarter purchases Fourth-quarter purchases $ Total cash disbursements $ 81,500 105,132 $ 186,632 $ Construct the direct labor budget 1 Planned Production in Units x Direct Labor Hours per Unit Budgeted Direct Labor Hours x Cost per Direct Labor Hour Budgeted Direct Labor Cost Year 2, 1st 2 Quarters 1 2 46,000 48,000 $27.16 $27.16 $1,249,360 $1,303,680 Year 774,060 $ First-quarter sales Total cash collections Year 166,500 $27.16 $4,522,140 Year 1 by Quarter 1 Accounts receivable, beginning balance Year 1 by Quarter 2 3 46,500 40,000 $27.16 $27.16 $1,262,940 $1,086,400 43,950 6.00 263,700 $16.25 $ 4,285,125 $ Year 1 by Quarter 2 3 70,088 105,684 $ 175,772 $ 4 70,456 96,528 $ 166,984 $ Year 1 by Quarter 2 3 44,550 39,400 6.00 6.00 267,300 236,400 $16.25 $16.25 4,343,625 $ 3,841,500 $ $ $ 64,352 $ 120,336 $ Year 81,500 175,220 176,140 160,880 120,336 184,688 $ 714,076 4 47,600 6.00 285,600 $16.25 $ 4,641,000 $ Year 175,500 6.00 1,053,000 $16.25 17,111,250 Year 2, 1st 2 Quarters 1 2 70,000 80,000 24,000 94,000 21,000 73,000 Year 2, 1st Quarter 1 73,000 5.00 365,000 0.80 indicate increase or decrease llowing month