After you have created the data table, name the sheet $$Table $1$ Data$,$ then create a copy of theAfter you have created the data table, name the sheet $$Table $1$ Data$,$ then create a copy of the

sheet to use for the second step of the analysis. Label this sheet $$Productivity Ratios$$

$7)$ Calculate labor productivity ratio, materials productivity ratio, and multifactor productivity ratio for

each month using product sales value in $ as the output.

$8)$ Add a column next to the June values labeled $$Average$($column H$)$ and calculate the average

productivity ratios for the six month period by averaging Jan to June productivity values for each

measure.

$9)$ Format the data appropriately. For example, large numbers, in the thousands, do not show

decimals. For the ratios, show two decimal places and be sure they are labeled properly to indicate

the values used such as in column A $$Labor Productivity Ratio $($$$/$$$)$

$10)$ Create and format a clustered column chart that shows all three types of productivity measures on

the same chart with month on the horizonal axis. Make sure each month is labeled. The easy way

to do this is to select the row in the table with the months, then hold Ctrl down while selecting the

table rows with the ratios. Place the chart below your productivity calculations placing the top

corner in column A$.$

$11)$ Add a column labeled $$Trend$($column I$)$ next to your $$Average$$ heading and use Sparklines with

markers for the Jan to June productivity for each type of measure.

$12)$ Review your results. Do the results make sense?

Assignment Sheet $3$:

$13)$ Create another copy of the $$Table $1$ Data$$ table on a new tab and label it $$Cost Changes$$ in the

spreadsheet.

$14)$ Add the title $$Average$$ in cell H$4.$ Compute the six$-$month average units produced, sales, labor,

direct material, and operations overhead values in column H$.$

$15)$ For each month and six$-$month average, calculate the Total Operating Cost, Cost$/$Unit and the Gross

Profit$/$Unit $($gross profit is sale price minus cost$/$unit$).$ Show the monthly trend with Sparklines in

column I $($do NOT include the six$-$month average in your sparklines$).$

$16)$ Last year the average cost was $$55\mathrm{.}95.$ Calculate, compared to last year, the percent change in

product costs and format appropriately.

Questions:

$17)$ Add a new sheet and label the tab $$Analysis$.$ Use a textbox to answer the f

sheet to use for the second step of the analysis. Label this sheet $$Productivity Ratios$$

$7)$ Calculate labor productivity ratio, materials productivity ratio, and multifactor productivity ratio for

each month using product sales value in $ as the output.

$8)$ Add a column next to the June values labeled $$Average$($column H$)$ and calculate the average

productivity ratios for the six month period by averaging Jan to June productivity values for each

measure.

$9)$ Format the data appropriately. For example, large numbers, in the thousands, do not show

decimals. For the ratios, show two decimal places and be sure they are labeled properly to indicate

the values used such as in column A $$Labor Productivity Ratio $($$$/$$$)$

$10)$ Create and format a clustered column chart that shows all three types of productivity measures on

the same chart with month on the horizonal axis. Make sure each month is labeled. The easy way

to do this is to select the row in the table with the months, then hold Ctrl down while selecting the

table rows with the ratios. Place the chart below your productivity calculations placing the top

corner in column A$.$

$11)$ Add a column labeled $$Trend$($column I$)$ next to your $$Average$$ heading and use Sparklines with

markers for the Jan to June productivity for each type of measure.

$12)$ Review your results. Do the results make sense?

Assignment Sheet $3$:

$13)$ Create another copy of the $$Table $1$ Data$$ table on a new tab and label it $$Cost Changes$$ in the

spreadsheet.

$14)$ Add the title $$Average$$ in cell H$4.$ Compute the six$-$month average units produced, sales, labor,

direct material, and operations overhead values in column H$.$

$15)$ For each month and six$-$month average, calculate the Total Operating Cost, Cost$/$Unit and the Gross

Profit$/$Unit $($gross profit is sale price minus cost$/$unit$).$ Show the monthly trend with Sparklines in

column I $($do NOT include the six$-$month average in your sparklines$).$

$16)$ Last year the average cost was $$55\mathrm{.}95.$ Calculate, compared to last year, the percent change in

product costs and format appropriately.

Questions:

$17)$ Add a new sheet and label the tab $$Analysis$.$ Use a textbox to answer the following questions:

a$)$ Did the Kaizen event improve the process? Why or why not?

b$)$ Is Engines R Us on track to achieve its $3\%$ cost reduction goal for the current year? Why or why

not?

c$)$ Refer to the Week $1$ slides and articles read for the course. Which of the four types of supply

chain analysis is this? Why?

$18)$ Spell check $($First item on the Review tab$)$ then submit your final spreadsheet as an excel file upload

on Canvas in Assignments in Cost Reduction at Engine R Us Inc Chapter $1.$