Problem

Master Budget, Flexible Budget, and Profit-Variance Analysis; Spreadsheet Application Goin...

Master Budget, Flexible Budget, and Profit-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 per unit. Budgeted variable manufacturing costs per unit are $50. Ortiz pays its salespeople a 10% 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 were $70 per unit (the actual sales commission was 10% 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 text Exhibit 14.4.
2. Use the spreadsheet you developed in (1) and the data presented above to complete the profit-variance report for the period. Below the table you create, show separately 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.
3. Provide a concise interpretation for each of the variances calculated above in (2).
4. Using the variances you calculated above in (2) and, in as much detail as the data allow, prepare a separate summary report similar to text Exhibit 14.2.

EXHIBIT 14.4 Breakdown of Total Operating Income Variance