Objective
Determine the proper inventory stocking level to maximize long term profits.
Background
The owner of a sales kiosk of perishable goods downloaded 10 weeks of sales data from her POS (point of sales) system.
That appears below. The unit revenue is $65, the unit cost is $28, and any unsold inventory at the end of each day is sold on the secondary market at a salvage price of $9 per unit.
Restocking occurs on a daily basis. An inventory stockout on any given day sets the maximum revenue for that day, since replenishment orders cannot be placed for delivery on the same day.
The daily fixed cost of operation is $160.
Assignment
1. Develop a relative frequency table and histogram based on the 10-week sales data.
2. Develop a daily profit and loss (P&L) model for this kiosk owner.
3. Based on the model, develop a two-way table (using the sensitivity tools) with the header row representing the customer demands and the header column representing inventory stocking decisions.
4. Compute the EMV (expected monetary values) for each inventory stocking level (decision alternative) based on your relative frequencies.
5. Determine the best decision based on the table results.
6. Using the goal seek tool, determine the minimum daily demand required to achieve breakeven with the given fixed cost.
Last picures were blurry, these are better.
Qbjective Determine the proper inventory stocking level to mavimize long term profits. Backeround The owner of a sales kiosk of perishable goods downloaded 10 wreeks of sales data from her POS (point of sales) system. That appears below. The unit revenue is $65, the unit cost is $28, and any unsald inventory at the end of each day is sold on the secondary market at a salvage price of $9 per unit. Restocking occurs on a daily bask. An inventory stockout on any given doy sets the maximum feveme for that diy. since replenithment or ders cannot be placed for delivery on the same day. The dally fixed cost of operation is $160, Assienment 1. Develop a relative frequency table and histogram based on the 10-week sales data. 2. Develop a daily profit and loss (PRL) model for this kiosk ownet. (input parameters specified with the green cells). 3. Based on the model, develop a two-way table (usieg the semitivity tools) with the header row representing the cumtomer demands and the header column representing imventory stocking decisions. 4. Compute the EMV (expected monetary values) for each inventory stocking level (decision alternative) based on your relative frequencies. 5. Determine the best decision based on the table rewlts. 6. Using the goal seek tool, determine the minimum demand required to achieve breakeven with the given fixed cast. Ten Week Sales Data (Units) Output Summary Ten Week Sales Data (Units) \begin{tabular}{|r|rr|r|r|r|} \hline & Day 1 & \multicolumn{1}{|c|}{ Day 2 } & \multicolumn{1}{r|}{ Day 3 } & Day 4 & \multicolumn{2}{|c|}{ Day 5} \\ \hline Week 1 & 15 & 20 & 13 & 18 & 16 \\ \hline Week 2 & 17 & 12 & 18 & 15 & 16 \\ \hline Week 3 & 18 & 13 & 17 & 12 & 15 \\ \hline Week & 14 & 10 & 12 & 13 & 18 \\ \hline Week 5 & 14 & 17 & 15 & 14 & 16 \\ \hline Week 6 & 13 & 14 & 12 & 15 & 14 \\ \hline Week & 16 & 15 & 11 & 14 & 13 \\ \hline Week & 14 & 16 & 17 & 17 & 16 \\ \hline Week 9 & 19 & 17 & 18 & 15 & 16 \\ \hline Week 10 & 11 & 15 & 16 & 13 & 19 \\ \hline \end{tabular} \begin{tabular}{l|l} \hline 34 & \\ 36 & Model \& Inputs \end{tabular} utput Summary Relative Frea 1 11 12 13 14 15 16 17 18 19. 20 Qbjective Determine the proper inventory stocking level to mavimize long term profits. Backeround The owner of a sales kiosk of perishable goods downloaded 10 wreeks of sales data from her POS (point of sales) system. That appears below. The unit revenue is $65, the unit cost is $28, and any unsald inventory at the end of each day is sold on the secondary market at a salvage price of $9 per unit. Restocking occurs on a daily bask. An inventory stockout on any given doy sets the maximum feveme for that diy. since replenithment or ders cannot be placed for delivery on the same day. The dally fixed cost of operation is $160, Assienment 1. Develop a relative frequency table and histogram based on the 10-week sales data. 2. Develop a daily profit and loss (PRL) model for this kiosk ownet. (input parameters specified with the green cells). 3. Based on the model, develop a two-way table (usieg the semitivity tools) with the header row representing the cumtomer demands and the header column representing imventory stocking decisions. 4. Compute the EMV (expected monetary values) for each inventory stocking level (decision alternative) based on your relative frequencies. 5. Determine the best decision based on the table rewlts. 6. Using the goal seek tool, determine the minimum demand required to achieve breakeven with the given fixed cast. Ten Week Sales Data (Units) Output Summary Ten Week Sales Data (Units) \begin{tabular}{|r|rr|r|r|r|} \hline & Day 1 & \multicolumn{1}{|c|}{ Day 2 } & \multicolumn{1}{r|}{ Day 3 } & Day 4 & \multicolumn{2}{|c|}{ Day 5} \\ \hline Week 1 & 15 & 20 & 13 & 18 & 16 \\ \hline Week 2 & 17 & 12 & 18 & 15 & 16 \\ \hline Week 3 & 18 & 13 & 17 & 12 & 15 \\ \hline Week & 14 & 10 & 12 & 13 & 18 \\ \hline Week 5 & 14 & 17 & 15 & 14 & 16 \\ \hline Week 6 & 13 & 14 & 12 & 15 & 14 \\ \hline Week & 16 & 15 & 11 & 14 & 13 \\ \hline Week & 14 & 16 & 17 & 17 & 16 \\ \hline Week 9 & 19 & 17 & 18 & 15 & 16 \\ \hline Week 10 & 11 & 15 & 16 & 13 & 19 \\ \hline \end{tabular} \begin{tabular}{l|l} \hline 34 & \\ 36 & Model \& Inputs \end{tabular} utput Summary Relative Frea 1 11 12 13 14 15 16 17 18 19. 20
