In this problem, you will determine whether or not to use self-checkout registers in stores and if yes, how many registers should be assigned to each employee for monitoring and assistance
Problem
Theft Problem | |||||||||
Many retailers and grocers, like Target and Walmart, have started using self-checkout registers instead of standard checkout registers. Retailers staff a group of self-checkout registers with a single employee to help with problems and monitor the check out process rather than having a 1 to 1 ratio of employees to standard checkout registers. According to a study completed by the University of Leicester in 2016, self-checkout registers nearly double the shoplifting rate from approximately 2% to approximately 4%. As 1/3 of sales come from shoppers in the self-checkout lanes, this increase is substantial. Suppose you are helping a retailer decide whether or not to install self-checkout registers in their stores. This retailer is not only deciding whether or not to use self-checkout registers, but also if they decide to change, how many registers should be assigned to each employee for monitoring and assistance. Use the data reference cells provided below and the concepts learned to complete the following tasks and make a decision. | |||||||||
Employee Hourly Wages | $10.00 | ||||||||
Standard Average Hourly Sales | $2,200.00 | ||||||||
Standard Theft Proportion | 0.02 | ||||||||
a) | Using the reference cells provided and formulas, fill in known information for staffed registers. Then determine the hourly cost per lane (including wage and theft costs) using a staffed register. | ||||||||
b) | Continuing to use the data reference cells provided, use a formula to determine hourly employee wages for managing the number of checkout registers (currently set at 1) by dividing the cost of an employee by the number of self-check out lanes this employee will watch. Use formulas to fill in average sales and theft proportion assuming sales per hour at a self-checkout register is half of a staffed register and the theft proportion increases by .5% (or .005) for every additional self-checkout register added to the monitoring employee's group. Determine the hourly cost per self-checkout register and multiply this cost by the number of checkout lanes (currently set at 1) to determine the cost for multiple checkout lanes. | ||||||||
c) | What is the current hourly cost for staffed registers and self-checkout registers? | ||||||||
Number of Currently Set Checkout Registers | 1 | ||||||||
Increase in Theft Proportion for Additional Self-Checkout Register | 0.005 | ||||||||
Average Hourly Sales at a Self-Checkout Register in proportion to Average Hourly Sales at a Staffed Register | 0.5 | ||||||||
Staffed Register Costs | a)&c) | Self-Checkout Register Costs | b)&c) | ||||||
Employee Wages | Employee Wages | ||||||||
Average Hourly Sales | Average Hourly Sales | ||||||||
Theft Proportion | Theft Proportion | ||||||||
Costs per Hour per Register | Costs per Hour per Register | ||||||||
d) | Increase the number of checkout registers in the data reference cells to 2. What is the current hourly cost for multiple registers using 2 registers for both types of check-out? Repeat this for 3, 4, 5, 6, and 7 registers. Pay attention that increase in the number of self-checkout registers don't require adding employees to monitor them. Assume that the sales revenue is proportional to the number of registers. | ||||||||
Staffed Register Costs | |||||||||
Number of Registers | 2 | 3 | 4 | 5 | 6 | 7 | |||
Employee Wages | |||||||||
Average Hourly Sales | |||||||||
Theft Proportion | |||||||||
Costs per Hour per Register | |||||||||
Costs per Hour for all Registers | |||||||||
Self-Checkout Register Costs | |||||||||
Number of Self-Checkout Registers | 2 | 3 | 4 | 5 | 6 | 7 | |||
Additional Self-Checkout Registers | |||||||||
Employee Wages per Register | |||||||||
Average Hourly Sales | |||||||||
Theft Proportion | |||||||||
Costs per Hour per Register | |||||||||
Costs per Hour for all Registers | |||||||||
e) | Should this retailer use self-checkout registers? | ||||||||
If yes, how many registers should be in a group monitored by 1 employee? If no, enter 0. | |||||||||
Number of Checkout Registers = | |||||||||
f) | Suppose a minimum wage increase of $15 per hour is passed. Change the data reference cell accordingly. | ||||||||
Employee Hourly Wages | $15.00 | ||||||||
Staffed Register Costs | |||||||||
Number of Registers | 2 | 3 | 4 | 5 | 6 | 7 | |||
Employee Wages | |||||||||
Average Hourly Sales | |||||||||
Theft Proportion | |||||||||
Costs Per Hour Per Lane | |||||||||
Costs for Multiple Lanes | |||||||||
Self-Checkout Register Costs | |||||||||
Number of Self-Checkout Registers | 2 | 3 | 4 | 5 | 6 | 7 | |||
Additional Self-Checkout Registers | |||||||||
Employee Wages per Register | |||||||||
Average Hourly Sales | |||||||||
Theft Proportion | |||||||||
Costs per Hour per Register | |||||||||
Costs per Hour for all Registers | |||||||||
Should this retailer use self-checkout registers? | |||||||||
If yes, how many registers should be in a group monitored by 1 employee? If no, enter 0. | |||||||||
Number of Checkout Registers = | |||||||||
Instructions
Project Description: In this problem, you will determine whether or not to use self-checkout registers in stores and if yes, how many registers should be assigned to each employee for monitoring and assistance. | ||
For the purpose of grading the project you are required to perform the following tasks: | ||
Step | Instructions | Points Possible |
1 | Start Excel. | 0 |
2 | In cells E15-E17, by using cell references, enter the hourly employee wages, average hourly sales, and theft proportion using staffed registers. Refer to appropriate cells among E5-E7. | 1 |
3 | In cell E18, by using cell references, calculate the hourly cost per register using staffed registers. Use cells E15, E16, and E17. | 1 |
4 | In cell J15, by using cell references, calculate the hourly employee wages using self-checkout registers. Use cells E5 and H11. | 1 |
5 | In cell J16, by using cell references, calculate the average hourly sales at a self-checkout register as a proportion of hourly sales at a staffed registers. Use cells E6 and H13. | 1 |
6 | In cell J17, by using a cell reference, enter the theft proportion using self-checkout registers assuming there are no additional self-checkout registers. Refer to an appropriate cell among E5-E7. | 1 |
7 | In cell J18, by using cell references, calculate the hourly cost per register using self-checkout registers. Use cells J15, J16, and J17. | 1 |
8 | In cells E22-E24, by using absolute cell references, enter the hourly employee wages, average hourly sales, and theft proportion assuming the increase of staffed registers to the value indicated in cell E21. Refer to appropriate cells among E5-E7. Copy the formulas from cells E22-E24 across columns F-J. | 1 |
9 | In cell E25, by using cell references, calculate the hourly cost per register assuming the increase of staffed registers to the value indicated in cell E21. Use cells E22, E23, and E24. Copy the formula from cell E25 across columns F-J. | 1 |
10 | In cell E26, by using cell references, calculate the hourly cost for multiple registers assuming the increase of staffed registers to the value indicated in cell E21. Use cells E21 and E25. Copy the formula from cell E26 across columns F-J. | 1 |
11 | In cell E29, by using relative and absolute cell references, calculate the number of additional self-checkout registers assuming the increase of self-checkout registers to the value indicated in cell E28. Use cells H11 and E28. Copy the formula from cell E29 across columns F-J. | 1 |
12 | In cell E30, by using relative and absolute cell references, calculate the hourly employee wages assuming the increase of self-checkout registers to the value indicated in cell E28. Use cells E5 and E28. Copy the formula from cell E30 across columns F-J. | 1 |
13 | In cell E31, by using absolute cell references, calculate the average hourly sales assuming the increase of self-checkout registers to the value indicated in cell E28. Use cells E6 and H13. Copy the formula from cell E31 across columns F-J. | 1 |
14 | In cell E32, by using relative and absolute cell references, calculate the theft proportion assuming the increase of self-checkout registers to the value indicated in cell E28. Use cells E7, H12, and E29. Copy the formula from cell E32 across columns F-J. | 1 |
15 | In cell E33, by using cell references, calculate the hourly cost per register assuming the increase of self-checkout registers to the value indicated in cell E28. Use cells E30, E31, and E32. Copy the formula from cell E33 across columns F-J. | 1 |
16 | In cell E34, by using cell references, calculate the hourly cost for multiple registers assuming the increase of self-checkout registers to the value indicated in cell E28. Use cells E28 and E33. Copy the formula from cell E34 across columns F-J. | 1 |
17 | In cells C36 and E38, do the following: In cell C36, determine whether the retailer should use self-checkout registers. In case the answer in cell C36 is Yes, in cell E38, by using a cell reference, enter the number of registers that should be in a group monitored by 1 employee. Refer to an appropriate cell among E28-J28. In case the answer in cell C36 is No, in cell E38, enter zero value. Note: Do not use the equal sign when entering a numeric value. | 1 |
18 | In cells E43-E45, by using absolute cell references, enter the new hourly employee wages, average hourly sales, and theft proportion. Refer to appropriate cells among E5-E7 and E40. Copy the formulas from cells E43-E45 across columns F-J. | 1 |
19 | In cell E46, by using cell references, calculate the hourly cost per register assuming the new minimum wage and the increase of staffed registers to the value indicated in cell E42. Use cells E43, E44, and E45. Copy the formula from cell E46 across columns F-J. | 1 |
20 | In cell E47, by using cell references, calculate the hourly cost for multiple registers assuming the new minimum wage and the increase of staffed registers to the value indicated in cell E42. Use cells E42 and E46. Copy the formula from cell E47 across columns F-J. | 1 |
21 | In cell E50, by using relative and absolute cell references, calculate the number of additional self-checkout registers assuming the increase of self-checkout registers to the value indicated in cell E49. Use cells H11 and E49. Copy the formula from cell E50 across columns F-J. | 1 |
22 | In cell E51, by using relative and absolute cell references, calculate the hourly employee wages assuming the new minimum wage and the increase of self-checkout registers to the value indicated in cell E49. Use cells E40 and E49. Copy the formula from cell E51 across columns F-J. | 1 |
23 | In cell E52, by using absolute cell references, calculate the average hourly sales assuming the new minimum wage and the increase of self-checkout registers to the value indicated in cell E49. Use cells E6 and H13. Copy the formula from cell E52 across columns F-J. | 1 |
24 | In cell E53, by using relative and absolute cell references, calculate the theft proportion assuming the new minimum wage and the increase of self-checkout registers to the value indicated in cell E49. Use cells E7, H12, and E50. Copy the formula from cell E53 across columns F-J. | 1 |
25 | In cell E54, by using cell references, calculate the hourly cost per register assuming the new minimum wage and the increase of self-checkout registers to the value indicated in cell E49. Use cells E51, E52, and E53. Copy the formula from cell E54 across columns F-J. | 1 |
26 | In cell E55, by using cell references, calculate the hourly cost for multiple registers assuming the new minimum wage and the increase of self-checkout registers to the value indicated in cell E49. Use cells E49 and E54. Copy the formula from cell E55 across columns F-J. | 1 |
27 | In cells C57 and E59, do the following: In cell C57, determine whether the retailer should use self-checkout registers if the minimum wage increases. In case the answer in cell C57 is Yes, in cell E59, by using a cell reference, enter the number of registers that should be in a group monitored by 1 employee. Refer to an appropriate cell among E49-J49. In case the answer in cell C57 is No, in cell E59, enter zero value. Note: Do not use the equal sign when entering a numeric value. | 1 |
28 | Save the workbook. Close the workbook and then exit Excel. Submit the workbook as directed. | 0 |
,
Grader – Instructions Excel 2016 Project
Chapter 14 Theft Problem (v2)
Project Description:
In this problem, you will determine whether or not to use self-checkout registers in stores and if yes, how many registers should be assigned to each employee for monitoring and assistance.
Steps to Perform:
Step |
Instructions |
Points Possible |
1 |
Start Excel. Download and open the workbook named: Chapter_14_Theft_Problem_Start. |
0 |
2 |
In cells E15-E17, by using cell references, enter the hourly employee wages, average hourly sales, and theft proportion using staffed registers. Refer to appropriate cells among E5-E7. |
1 |
3 |
In cell E18, by using cell references, calculate the hourly cost per register using staffed registers. Use cells E15, E16, and E17. |
1 |
4 |
In cell J15, by using cell references, calculate the hourly employee wages using self-checkout registers. Use cells E5 and H11. |
1 |
5 |
In cell J16, by using cell references, calculate the average hourly sales at a self-checkout register as a proportion of hourly sales at a staffed registers. Use cells E6 and H13. |
1 |
6 |
In cell J17, by using a cell reference, enter the theft proportion using self-checkout registers assuming there are no additional self-checkout registers. Refer to an appropriate cell among E5-E7. |
1 |
7 |
In cell J18, by using cell references, calculate the hourly cost per register using self-checkout registers. Use cells J15, J16, and J17. |
1 |
8 |
In cells E22-E24, by using absolute cell references, enter the hourly employee wages, average hourly sales, and theft proportion assuming the increase of staffed registers to the value indicated in cell E21. Refer to appropriate cells among E5-E7. Copy the formulas from cells E22-E24 across columns F-J. |
1 |
9 |
In cell E25, by using cell references, calculate the hourly cost per register assuming the increase of staffed registers to the value indicated in cell E21. Use cells E22, E23, and E24. Copy the formula from cell E25 across columns F-J. |
1 |
10 |
In cell E26, by using cell references, calculate the hourly cost for multiple registers assuming the increase of staffed registers to the value indicated in cell E21. Use cells E21 and E25. Copy the formula from cell E26 across columns F-J. |
1 |
11 |
In cell E29, by using relative and absolute cell references, calculate the number of additional self-checkout registers assuming the increase of self-checkout registers to the value indicated in cell E28. Use cells H11 and E28. Copy the formula from cell E29 across columns F-J. |
1 |
12 |
In cell E30, by using relative and absolute cell references, calculate the hourly employee wages assuming the increase of self-checkout registers to the value indicated in cell E28. Use cells E5 and E28. Copy the formula from cell E30 across columns F-J. |
1 |
13 |
In cell E31, by using absolute cell references, calculate the average hourly sales assuming the increase of self-checkout registers to the value indicated in cell E28. Use cells E6 and H13. Copy the formula from cell E31 across columns F-J. |
1 |
14 |
In cell E32, by using relative and absolute cell references, calculate the theft proportion assuming the increase of self-checkout registers to the value indicated in cell E28. Use cells E7, H12, and E29. Copy the formula from cell E32 across columns F-J. |
1 |
15 |
In cell E33, by using cell references, calculate the hourly cost per register assuming the increase of self-checkout registers to the value indicated in cell E28. Use cells E30, E31, and E32. Copy the formula from cell E33 across columns F-J. |
1 |
16 |
In cell E34, by using cell references, calculate the hourly cost for multiple registers assuming the increase of self-checkout registers to the value indicated in cell E28. Use cells E28 and E33. Copy the formula from cell E34 across columns F-J. |
1 |
17 |
In cells C36 and E38, do the following: In cell C36, determine whether the retailer should use self-checkout registers. In case the answer in cell C36 is Yes, in cell E38, by using a cell reference, enter the number of registers that should be in a group monitored by 1 employee. Refer to an appropriate cell among E28-J28. In case the answer in cell C36 is No, in cell E38, enter zero value. Note: Do not use the equal sign when entering a numeric value. |
1 |
18 |
In cells E43-E45, by using absolute cell references, enter the new hourly employee wages, average hourly sales, and theft proportion. Refer to appropriate cells among E5-E7 and E40. Copy the formulas from cells E43-E45 across columns F-J. |
1 |
19 |
In cell E46, by using cell references, calculate the hourly cost per register assuming the new minimum wage and the increase of staffed registers to the value indicated in cell E42. Use cells E43, E44, and E45. Copy the formula from cell E46 across columns F-J. |
1 |
20 |
In cell E47, by using cell references, calculate the hourly cost for multiple registers assuming the new minimum wage and the increase of staffed registers to the value indicated in cell E42. Use cells E42 and E46. Copy the formula from cell E47 across columns F-J. |
1 |
21 |
In cell E50, by using relative and absolute cell references, calculate the number of additional self-checkout registers assuming the increase of self-checkout registers to the value indicated in cell E49. Use cells H11 and E49. Copy the formula from cell E50 across columns F-J. |
1 |
22 |
In cell E51, by using relative and absolute cell references, calculate the hourly employee wages assuming the new minimum wage and the increase of self-checkout registers to the value indicated in cell E49. Use cells E40 and E49. Copy the formula from cell E51 across columns F-J. |
1 |
23 |
In cell E52, by using absolute cell references, calculate the average hourly sales assuming the new minimum wage and the increase of self-checkout registers to the value indicated in cell E49. Use cells E6 and H13. Copy the formula from cell E52 across columns F-J. |
1 |
24 |
In cell E53, by using relative and absolute cell references, calculate the theft proportion assuming the new minimum wage and the increase of self-checkout registers to the value indicated in cell E49. Use cells E7, H12, and E50. Copy the formula from cell E53 across columns F-J. |
1 |
25 |
In cell E54, by using cell references, calculate the hourly cost per register assuming the new minimum wage and the increase of self-checkout registers to the value indicated in cell E49. Use cells E51, E52, and E53. Copy the formula from cell E54 across columns F-J. |
1 |
26 |
In cell E55, by using cell references, calculate the hourly cost for multiple registers the new minimum wage and the increase of self-checkout registers to the value indicated in cell E49. Use cells E49 and E54. Copy the formula from cell E55 across columns F-J. |
1 |
27 |
In cells C57 and E59, do the following: In cell C57 determine whether the retailer should use self-checkout registers if the minimum wage increases. In case the answer in cell C57 is Yes, in cell E59, by using cell reference, enter the number of registers that should be in a group monitored by 1 employee. Refer to an appropriate cell among E49-J49. In case the answer in cell C57 is No, in cell E59, enter zero value. Note: Do not use the equal sign when entering a numeric value. |
1 |
28 |
Save the workbook. Close the workbook and then exit Excel. Submit the workbook as directed. |
0 |
Total Points |
26 |
Created On: 07/05/2019 1 Chapter 14 Theft Problem (v2)