14-43 Master Budget, Flexible Budget, and Operating Income Variance Analysis; Spreadsheet Application Going into the period just ended, Ortiz & Co., manufacturer of a moderately priced espresso maker for retail sale, had planned to produce and sell 3,900 units at $100.00 per unit. Budgeted variable manufacturing costs per unit are $50.00. Ortiz pays its salespeople a 10.0% sales commission, which is the only variable nonmanufacturing cost for the company. Fixed costs are budgeted as follows: manufacturing, $50,000, marketing, $36,000. Actual financial results for the period were disappointing. While sales volume was up (4,000 units sold), actual operating profit was only $20,000 for the period. Fixed manufacturing costs were as budgeted, but fixed marketing expenses exceeded budget by $4,000. Actual sales revenue for the period was $390,000, and actual variable costs (manufacturing and nonmanufacturing combined) were $70.00 per unit (the actual sales commission cost was 10.0% of sales revenue generated). Required 1. Develop an Excel spreadsheet that is able produce a profit-variance report similar to the one presented in Exhibit 14.40 Use your spreadsheet (and the data presented above) to complete the profit-variance report for the period (Round all variances to the nearest whole dollar.) Make sure that you calculate each of the following variances: a. Total master (static) budget variance (i.e., the total operating income variance for the period). b. Total flexible-budget variance. c. Flexible-budget variance for total variable costs, plus the flexible-budget variance for: (1) Variable manufacturing costs. (2) Variable nonmanufacturing costs. d. Flexible-budget variance for total fixed costs, plus the flexible-budget variance for: (1) Fixed manufacturing costs. (2) Fixed nonmanufacturing costs. SCHMIDT MACHINERY COMPANY Analysis of Financial Results For October 2019 (1) (4) (2) Flexible-Budget Variances (3) Flexible Budget Sales Volume Variances (5) Master (Static) Budget Actual 780 0 780 Units Sales Variable costs Contribution margin Fixed costs Operating income $639,600 350,950 $288,650 160,650 $128,000 $15,600F 50F $15,650F 10,650U $5,000F $624,000 351,000 $273,000 150,000* $123,000 2200 $176,000U 99,000F $ 77,000U 0 $ 77,0000 1,000 $800,000 450,000 $350,000 150,000 $200,000 Analysis of Total Operating Income Variance Total operating income variance** =$128,000-$200,000=$72,000U Flexible-budget variance = $128,000 - $123,000 =$5,000F Sales volume variance =$123,000 - $200,000 =$77,000U