Please prepare Problem 14-44. Please be thorough in your analysis. Skip part 3.
14-44 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 to produce a profit-variance report similar to the one presented in [囚] Exhibit 14.4. 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 budget variance. 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.
