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,00$

$0$

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\mathrm{.}7$ days

Wait time $($from order to start of production$)13\mathrm{.}5$ days

Process time $3\mathrm{.}6$ days

Move time $1\mathrm{.}6$ days

Queue time $4\mathrm{.}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\mathrm{.}50$ per unit

Direct labor $$3\mathrm{.}60$ per unit

Variable OH $$1\mathrm{.}50$ per unit

Variable S & A $$1\mathrm{.}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\mathrm{.}00$ lbs $0\mathrm{.}75$ per pound $1\mathrm{.}50$

Direct labor $0\mathrm{.}20$ hrs $18\mathrm{.}00$ per hour $3\mathrm{.}60$

Variable OH $1\mathrm{.}50$ per unit $1\mathrm{.}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.

$1.$ Prepare the flexible budget performance report for last year. $($See exhibit $9-6)$

$2.$ Determine the material price and quantity variances.

$3.$ Determine the direct labor price and quantity variances.

$4.$ Determine the variable overhead spending and efficiency variances.

$5.$ Determine the ROI and RI for last year.

$6.$ Determine last year$$s throughput time, MCE, and delivery cycle time.