Master Budget, Flexible Budget, and Profit-Variance Analysis 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 percent 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 percent 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.
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), prepare in as much detail as the data allow, a separate summary report similar to text.