Budgeting and Variances: Problem 9-43/11-26. Use excel to put in answers. Docx is the problem in the book that needs to be answered in excel templates.

9-43: Preparation of a Master Budget. It corner it states that the 1)Total Sales Revenue is $1,100,000.00. 3)Cost of purchases (paperboard):$97,000. 5) Total Overhead is $148,500. 7) Predetermined Overhead:$40 per hour. FreshPak Corporation Manufacters two types of cardboard boxes used in shipping canned food, fruit and vegetables. The canned box (Type C) and the perishable food box (type P) have the following material and labor requirements. Direct Material Per 100 Boxes: Paperboard ( $.20 per pound ) Corrugating mediaum ($.10 per pound) Direct Labor required per 100 boxes ($12.00 per hour ) Type C 30 pounds 20 pounds .25 hours Type P 70 pounds 30 pounds .50 hour The following manufacturing overhead cost are anticipated for next year. The predetermined overhead rate is based on a production volume of 495,000 units for each type of box. Manufacteing overhead is applied on the basis os direct-labor hours. Indirect Material Indirect Labor Utilities Property Taxes Insurance Depreciation Total $10,500 50,000 25,000 18,000 16,000 29,000 $148,500 The following selling and administrative expenses are anticipated for next year. Salaries and fringe benefits of sales personnel Advertising Manager Salaries and fringe benefits Clerical Wages and fringe benefits Misc. Administrative Expenses Total $75,000 15,000 90,000 26,000 4,000 $210,000 The Sales forecast for next year is as follows: Finished Goods Expected Inventory January 1 Box Type C Box Type P 10,000 boxes 20,000 boxes 5,000 boxes 15,000 boxes 15,000 pounds 5,000pounds Raw Material Paperboard: Expected Iventory December 31 Corrugating Mediaum: 5,000 pounds 10,000 pounds Required: Prepare a Master Budget for Freshpak Corporation for the next year. Assume an income tax rate of 40 percent. Include the following schedules. 1) Sales Budget. 2) Production Budget 3) Direct-Material Budget 4) DirectLabor Budget 5) Manufactering-Overhead Budget 6) Selling and Administrative Expense Budget 7) Budgeted Income Statement(Hint: To determine Cost of Good sold, first compute the manufacturing cost per unit for each type of box. Include applied manufacturing overhead in the cost). 11-26: Straigtfoward Computation of Overhead Variances The following data are actual results for Marvelous Marshmellow Company for October. Actual Output Actual Variable Overhead Actaul fixed Overhead Actual Machine Time 9,000 cases $405,000 $122,000 40,500 machine hours Standard Cost and Budget Information for Marvelous Marshmellows. Standard Variable Overhead Rate Standard quantity of machine hours Budgeted Fixed Overhead Budgeted Output $9.00 per machine hour 4 hours per case of marshmellows $120,000 per month 10,000 cases per month Required: Use any of the methods explained in the chapter to compute the following variances. Indicate Rather is favorable or unfavorable. a) Variable Overhead Spending Variance b) Variable Overhead Efficiency Variance c) Fixed Overhead budget variance d) Fixed Overhead Volume Variance 2) Construct an Excel Spreadsheet to solve the preceding requirement. Show how the solution will change if the following information changes: actual out put was 91,000 cases and actual variable overhead was $395,000. Populate the cells highlighted in green Req 1 a. Variable-overhead spending variance = actual variable overhead - (AH x SVR) = = b. Variable-overhead efficiency variance = SVR(AH - SH) = = *SH = c. Fixed-overhead budget variance = actual fixed OH - budgeted fixed OH = = d. Fixed-overhead volume variance Applied fixed overhead = budgeted fixed OH - applied fixed OH = = = Predetermined fixed overhead rate x standard allowance hours = = Just populate the cells highlighted in green (there are formulas in the cells below that will automatically calc DATA INPUT Actual output Actual variable overhead (Actual VOH) Actual fixed overhead (actual FOH) Actual machine time $ $ Standard variable-overhead rate Standard quantity of machine hours Budgeted fixed overhead (budgeted FOH) Budgeted output 1) Variable-overhead spending variance $ $ = actual VOH = = 2) Variable-overhead efficiency variance $ $ = * SH = 3) Fixed-overhead budget variance = = 4) Fixed-overhead volume variance ^ applied fixed overhead (AH - $ $ - - Favorable budgeted FOH = = $ = (predetermined FOH rate) - #DIV/0! #DIV/0! x x - x - - (AH x - Favorable actual FOH = per machine hour hours per case of marshmallows per month cases per month x hrs. = = - machine hours - Favorable $ $ - cases - SVR = = - cases x - budgeted FOH $ - applied fixed overhead ^ #DIV/0! (standard allowed hours) = predetermined fixed overhead rate standard allowed hours = = = = x #DIV/0! $ - / - = - x #DIV/0! - x hrs. - ls below that will automatically calculate) arshmallows SVR) $ - SH*) - hrs per case #DIV/0! - Please fill out the cells in green 1 Sales budget: Box C Box P 2 Production budget (in units): Box C Box P Total Please fill out the cells in green Raw-material budget: Paperboard Box C Box P Total Box C Box P Total Corrugating Medium Please fill out the cells in green 4 Direct-labor budget: Box C Box P Total Please fill out the cells in green 5 Manufacturing-overhead budget: 6 Selling and administrative expense budget: Please fill out the cells in green 7 Budgeted income statement: *Calculation of manufacturing cost per unit: (a) Predetermined overhead rate = = = (b) Calculation of manufacturing cost per unit: Box C Box P