For this assignment, please submit one Excel file. Use a separate worksheet for each question set and label the worksheets. To rename a worksheet, right$-$click the worksheet tab located at the bottom of your current sheet and use the rename option. Label the tabs as $$Q Set $1,$Q Set $2,$ and $$Q Set $3,$ respectively. Be sure to label all results clearly.

$$

Questions

Question Set $1.$ A manager has tracked days worked and Donuts sold as given below.

Days Worked

Seasons Winter Spring Summer Autumn Winter

Cashier Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec

Larry $48422915262225$

Bent $4125353325$

George $32251731313130$

Tanya $37243624$

Jasmine $36443032$

Claire $38222836$

Donuts Sold

Seasons Winter Spring Summer Autumn Winter

Cashier Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec

Larry $62824457233365172284$

Bent $660375388575166$

George $4762274864751482286$

Tanya $612512169274$

Jasmine $445496276308$

Claire $64046455262$

$1.$ For each cashier, compute his or her overall productivity $($in Donuts sold per day$)$ for Summer and Autumn. You should have one number per cashier. $(3$pts$)$

$2.$ For each cashier, compute his or her productivity $($in Donuts sold per day$)$ for Spring. Using the MAX function, find the highest productive cashier for the Spring among the cashiers. $(3$pts$)$

$3.$ Compute the overall productivity for each cashier $($in Donuts sold per day$)$ for the entire year. Use the MIN function to find the lowest productive cashier for the year among the cashiers. $(3$ pts$)$

$4.$ Which month has the lowest overall productivity? $(2$ pts$)$

Question Set $2.$ For this question set, you will calculate labor productivity and multifactor productivity, both in tons of wheat produced per dollar value of inputs.

A Wheat Manufacturer employs $60$ field workers $($each working $50$ hours per week at a wage of $$15/$hr$)$ and $30$ line workers $($each working $25$ hours per week at a wage of $$12/$hr$).$

The business has capital expenses of $$20,000$ per week and produces $450,000$ tons of rice per week.

Recently, the company decided to invest on additional equipment which increased the capital expenses by $12\%.$ Also, the number of field workers was cut short by $8\%$ whereas line workers were $15\%$ more recruited. Both field and line workers were given a raise of $8\%$ and $10\%,$ respectively. Due to the above$-$mentioned changes, the new system reported to have an $9\%$ more output than the old system.

You will need the following formulas:

Labor productivity $=([$Weekly output$])/([$Weekly total labor cost$])$

Multifactor productivity $=([$Weekly output$])/([$Weekly total labor cost$+$Weekly capital expenses$])$

After copying the table below into Excel, solve the following questions and fill the table

OLD SYSTEM $\%$ CHANGE NEW SYSTEM

Number of Field workers $60-0\mathrm{.}08?$

Hours $50$

Wages $150\mathrm{.}08?$

Total cost for field workers $?$

Number of Line workers $300\mathrm{.}15?$

Hours $25$

Wages $120\mathrm{.}10?$

Total cost for line workers $??$

TOTAL LABOR COST $??$

Capital expenditure $250000\mathrm{.}12?$

Output $4500000\mathrm{.}09?$

LABOR PRODUCTIVITY $??$

MULTIFACTOR PRODUCTIVITY $??$

$\%$ CHANGE FOR LABOR PRODUCTIVITY $?$

$\%$ CHANGE FOR MULTIFACTOR PRODUCTIVITY $?$

Calculate the Total Input Cost for Old and New systems $($Labor cost $+$ capital expense$)(2$ pts$)$

Calculate the Capital Expenditure and Output of the new system $(2$ pts$)$

Calculate labor productivity including all employees $($line workers & field workers$)$ for the old and new systems, in tons of output per dollar of input. $(4$pts$)$

Calculate the multifactor productivity $($including line workers, field workers, and capital expenses$)$ for the old and new systems, in tons of output per dollar of input. $(4$pts$)$

Calculate the percentage changes in labor and multifactor productivity. $(3$pts$)$

Question Set $3.$ A bakery has a production target of $1000$ units of pancakes, waffles and cakes each in a month. The owners spent $$10,200$ on setting up the bakery, which could be accounted as the fixed cost. The production cost and selling price for the items is listed in the table below. For this question set, use the following formulas and information:

$[$Total Cost$]=[$Production$]$ x $[$Variable Unit Cost$]+[$Fixed Costs$]$

$[$Total Revenue$]=[$Production$]$ x $[$Unit Revenue$]$

$[$Total Profit$]=[$Total Revenue$][$Total Cost$]$

$[$Break Even Point$]=[$Fixed Cost$]/([$Unit Revenue$]-[$Unit Variable Cost$])$

Item Unit Cost Unit Revenue

pancakes $2\mathrm{.}53\mathrm{.}4$

waffles $3\mathrm{.}24\mathrm{.}5$

cakes $4\mathrm{.}86\mathrm{.}0$

Calculate Total profit for the bakery in a month, given they bake $1000$ units each of all $3$ products $(3$pts$)$

Create a data table $($as demonstrated during lab exercise $2),$ to show the change in Total profit corresponding to change in Production from $2000$ to $3700$ units with increments of $100.$ You must use a data table structure to receive credit for this problem. $(6$pts$)$