Instructions Problem Guidelines All problems must be solved using Excel formulas
ID: 3049338 • Letter: I
Question
Instructions
Problem Guidelines
All problems must be solved using Excel formulas to receive credit
Submission Guidelines
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 store manager has tracked hours worked and orders processed as given below.
Hours Worked
Cashier
1-Sep
2-Sep
3-Sep
4-Sep
5-Sep
6-Sep
7-Sep
8-Sep
Victoria
8
8
8
8
8
Wendy
8
7
7
6
8
Xavier
8
8
8
8
8
Yusuf
8
8
8
7
Zachary
6
6
6
6
6
Orders Processed
Cashier
1-Sep
2-Sep
3-Sep
4-Sep
5-Sep
6-Sep
7-Sep
8-Sep
Victoria
84
87
91
88
85
Wendy
88
79
74
60
83
Xavier
78
87
86
81
83
Yusuf
83
84
84
74
Zachary
63
59
63
63
55
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. (3pts) (3pts)
2. For each cashier, compute his or her productivity (in orders per hour) over the period 9/1 through 9/5. Using the MIN function, find the lowest productivity for 9/1 – 9/5 among the cashiers. (5pts)
3. For each cashier, computer his or her productivity (in orders per hour) over the period 9/4 through 9/8. Using the MAX function, find the highest productivity for 9/4 – 9/8 among the cashiers. (5pts)
Question Set 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 50 full-time line workers (each working 45 hours per week at a wage of $14/hr) and 6 managers, each earning a salary of $1,300/week. The company has capital expenses (plant, property, and equipment) of $18,000 per week and produces 200,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.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 including all employees (line workers & managers) for the old and new systems, in tons of output per dollar of input. (4pts)
2. 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. (4pts)
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. (4pts)
4. Calculate the percentage changes in labor and multifactor productivity. (3pts)
Labor Productivity
(including managers)
Labor Productivity
(excluding managers)
Multifactor Productivity
Old System
New System
Percentage Change
Question Set 3. A small manufacturing plant produces specialized stainless steel valves for high-pressure steam systems. Each valve costs $2000 to produce. The plant incurs $1,200,000 in fixed annual costs. The plant sells the valves directly to power plants for $6400 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 100 to 400 valves, in increments of 20. You must use a data table structure to receive credit for this problem. (6pts)
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 may be negative but should still be displayed. Label your chart axes appropriately. (6pts)
Hours Worked
Cashier
1-Sep
2-Sep
3-Sep
4-Sep
5-Sep
6-Sep
7-Sep
8-Sep
Victoria
8
8
8
8
8
Wendy
8
7
7
6
8
Xavier
8
8
8
8
8
Yusuf
8
8
8
7
Zachary
6
6
6
6
6
Explanation / Answer
Question Set 1 Hours Worked Total Hours Cashier 01-Sep 02-Sep 03-Sep 04-Sep 05-Sep 06-Sep 07-Sep 08-Sep Victoria 8 8 8 8 8 40 Wendy 8 7 7 6 8 36 Xavier 8 8 8 8 8 40 Yusuf 8 8 8 7 31 Zachary 6 6 6 6 6 30 Orders Processed Total Orders Cashier 01-Sep 02-Sep 03-Sep 04-Sep 05-Sep 06-Sep 07-Sep 08-Sep Victoria 84 87 91 88 85 435 Wendy 88 79 74 60 83 384 Xavier 78 87 86 81 83 415 Yusuf 83 84 84 74 325 Zachary 63 59 63 63 55 303 1) Cashier Total Hours Total Orders Productivity (Orders/Hours) Victoria 40 435 10.88 Wendy 36 384 10.67 Xavier 40 415 10.38 Yusuf 31 325 10.48 Zachary 30 303 10.10 2) Productivity Cashier 01-Sep 02-Sep 03-Sep 04-Sep 05-Sep Min Productivity from 1 to 5 Sep Victoria 10.5 10.875 10.50 Wendy 11 11.28571429 10.57143 10 10.00 Xavier 9.75 10.875 10.75 9.75 Yusuf 10.375 10.5 10.38 Zachary 10.5 9.833333333 10.5 10.5 9.83 3) Productivity Cashier 04-Sep 05-Sep 06-Sep 07-Sep 08-Sep Max Productivity from 4 to 8 Sep Victoria 10.875 11.375 11 10.625 11.38 Wendy 10.57142857 10 10.375 10.57 Xavier 10.75 10.125 10.375 10.75 Yusuf 10.5 10.5 10.57143 10.57 Zachary 10.5 9.166666667 10.50 Question Set 2 Workers 50 Working hours per week 45 Wage per hour 14 Total wages to workers per week 31500 Managers 6 Salary of manager per week 1300 Total salary of managers 7800 Capital expenses per week 18000 Production per week in tons 200000 New Equipment Output increase by 9% 218000 Capital expense increase by 5.5% 18990 Labor Productivity (including managers) Labor Productivity (excluding managers) Multifactor Productivity Old System 5.089058524 6.349206349 3.490401396 New System 5.547073791 6.920634921 3.739921084 Percentage Change 9.00% 9.00% 7.15% Productivity is total output divded by total input