Excel Assignment # 6
Performance Measurement
Spring
Northern Illinois Manufacturing reported the following results from last year’s operations:
| Sales (1,320,000 units) | $ 15,840,000 | |
| Variable Expenses: | ||
| Direct Materials | 1,940,400 | |
| Direct Labor | 5,450,220 | |
| Manufacturing OH | 2,100,500 | |
| S & A | 2,112,000 | |
| Total Variable | 11,603,120 | |
| Contribution Margin | 4,236,880 | |
| Fixed Expenses | ||
| Manufacturing OH | 767,700 | |
| S & A | 1,126,800 | |
| Total Fixed | 1,894,500 | |
| Net Operating Income | $ 2,342,380 |
Total operating assets at the end of the year are $20,900,000. The total operating assets at the end of last year was $19,100,000.
The company tracks its internal business process performance and reports the following data for this year:
| Inspection time | 0.7 days |
| Wait time (from order to start of production) | 13.5 days |
| Process time | 3.6 days |
| Move time | 1.6 days |
| Queue time | 4.1 days |
Budgeted information for Northern Illinois is based on the following information:
| Sales – Units | 1,300,000 |
| Sales Price | $12 per unit |
| Direct materials | $1.50 per unit |
| Direct labor | $3.60 per unit |
| Variable OH | $1.50 per unit |
| Variable S & A | $1.60 per unit |
| Fixed OH | $750,000 |
| Fixed S & A | $1,125,000 |
The following standard cost card is for the variable manufacturing for one unit of production:
| Std Quantity | Std Price | Std Cost | |
| Direct materials | 2.00 lbs | 0.75 per pound | 1.50 |
| Direct labor | 0.20 hrs | 18.00 per hour | 3.60 |
| Variable OH | 1.50 per unit | 1.50 |
During the year, the company purchased and used 2,772,000 pounds of material. The company worked 302,790 hours at a rate of $18 per hour.
The company’s minimum return is 10%.
This assignment requires several items to be completed using an Excel spreadsheet. Please prepare the items in the order given with the number of the requirement or your Excel spreadsheet.
- Prepare the flexible budget performance report for last year. (See exhibit 9-6)
- Determine the material price and quantity variances.
- Determine the direct labor price and quantity variances.
- Determine the variable overhead spending and efficiency variances.
- Determine the ROI and RI for last year.
- Determine last year’s throughput time, MCE, and delivery cycle time.