Question 1. A store manager has tracked hours worked and orders processed as given below.
| |
Hours Worked
|
|
Cashier
|
1-Feb
|
2-Feb
|
3-Feb
|
4-Feb
|
5-Feb
|
6-Feb
|
7-Feb
|
8-Feb
|
|
Kevin
|
|
|
8
|
7
|
7
|
|
|
8
|
|
Lisa
|
8
|
8
|
|
8
|
|
8
|
7
|
7
|
|
Mark
|
|
|
7
|
7
|
8
|
|
8
|
|
|
Naomi
|
8
|
8
|
|
|
7
|
7
|
|
|
|
Oscar
|
8
|
8
|
8
|
|
|
8
|
8
|
8
|
| |
Orders Processed
|
|
Cashier
|
1-Feb
|
2-Feb
|
3-Feb
|
4-Feb
|
5-Feb
|
6-Feb
|
7-Feb
|
8-Feb
|
|
Kevin
|
|
|
94
|
76
|
73
|
|
|
102
|
|
Lisa
|
82
|
83
|
|
104
|
|
94
|
88
|
88
|
|
Mark
|
|
|
76
|
75
|
97
|
|
96
|
|
|
Naomi
|
107
|
107
|
|
|
91
|
88
|
|
|
|
Oscar
|
75
|
96
|
102
|
|
|
79
|
78
|
107
|
1. For each cashier, compute his or her overall productivity (in orders per hour) over all eight days. You should have one number per cashier.
2. For each cashier, compute his or her productivity (in orders per hour) over the period 2/1 through 2/4. Using the MIN function, find the lowest productivity for 2/1 - 2/4 among the cashiers.
3. For each cashier, computer his or her productivity (in orders per hour) over the period 2/5 through 2/7. Using the MAX function, find the highest productivity for 2/5 - 2/7 among the cashiers.
Question 2. For this question set, you will calculate labor productivity and multifactor productivity, both in tons of rock salt produced per dollar value of inputs.
The Delaware Salt Company employs 90 full-time line workers (each working 40 hours per week at a wage of $12/hr) and 6 managers, each earning a salary of $1,500/week. The company has capital expenses (plant, property, and equipment) of $24,000 per week and produces 180,000 tons of rock salt per week. The company recently installed new equipment that will increase weekly output by 9% and weekly capital expenses by 5%. You will need the following formulas:
Labor productivity =
Multifactor productivity =
After copying the table below into Excel, solve the following questions:
1. Calculate labor productivity using just the line workers (do not include the managers) for the old and new systems, in tons of output per dollar of input.
2. Calculate labor productivity including all employees (line workers & managers) for the old and new systems, in tons of output per dollar of input.
3. Calculate the multifactor productivity (including line workers, managers, and capital expenses) for the old and new systems, in tons of output per dollar of input.
4. Calculate the percentage changes in labor and multifactor productivity. (3pts)
|
|
Labor Productivity
(excluding managers)
|
Labor Productivity
(including managers)
|
Multifactor Productivity
|
|
Old System
|
|
|
|
|
New System
|
|
|
|
|
Percentage Change
|
|
|
|
Question 3: A small manufacturing plant produces specialized stainless steel valves for high-pressure steam systems. Each valve costs $3000 to produce. The plant incurs $2,700,000 in fixed annual costs. The plant sells the valves directly to power plants for $5400 each.
For this question set, use the following formulas:
[Total Profit] = [Total Revenue] - [Total Cost]
[Total Revenue] = [Production] x [Unit Revenue]
[Total Cost] = [Production] x [Variable Unit Cost] + [Fixed Costs]
1. Create a data table (as demonstrated during lab exercise 2), that shows what total profit would be if the company produced 1500 to 2500 valves, in increments of 50. You must use a data table structure to receive credit for this problem.
2. Create a scatter chart that displays the variable total profit (and no other variables) as a function of the number of valves produced and sold. At low production quantities, total profit will be negative but should still be displayed. Label your chart axes appropriately.