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 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). EXHIBIT 14.2 Operating-Income Variances (Single Product Example: Product XV-1) Static master budget Variance (Total operating income variance) Flexible-budget variance Sales volume variance Selling price variance Manufacturing cost variances Selling and administrative expense variances Variable selling Direct materials (DM) variance Direct labor(DL) variance Factory overhead Variance and administrative expense variances Foxed selling und administrative expense variances Direct materials price variance Direct materials usage variance Direct labor rate variance Direct labor efficiency Variance Variable factory overhead variances Fixed factory overhead variances Variable factory overhead spending variance Variable factory overhead efficiency variance Foxed Foced factory factory overhead overhead spending production (budget) volume variance variance covered "The decompose of this arance As plained in the standar Chapter 15 t 4.4. the sales vo c e can be called and presedinte of each compot of the inco m e 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 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). EXHIBIT 14.2 Operating-Income Variances (Single Product Example: Product XV-1) Static master budget Variance (Total operating income variance) Flexible-budget variance Sales volume variance Selling price variance Manufacturing cost variances Selling and administrative expense variances Variable selling Direct materials (DM) variance Direct labor(DL) variance Factory overhead Variance and administrative expense variances Foxed selling und administrative expense variances Direct materials price variance Direct materials usage variance Direct labor rate variance Direct labor efficiency Variance Variable factory overhead variances Fixed factory overhead variances Variable factory overhead spending variance Variable factory overhead efficiency variance Foxed Foced factory factory overhead overhead spending production (budget) volume variance variance covered "The decompose of this arance As plained in the standar Chapter 15 t 4.4. the sales vo c e can be called and presedinte of each compot of the inco m e