2. Develop a daily profit and loss (P&L) model for this kiosk owner. (Input parameters specified with the green cells).

To be able answer the question, I need to find the QTY Sold and Excess QTY but I don't know or understand how. Please help!!

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. (Input parameters specified with the green cells). 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 demand required to achieve breakeven with the given fixed cost. Data Ten Week Sales Data (Units) Day 1 Week 1 Week 2 Week 3 Week 4 Week 5 Week 6 Week 7 Week 8 8 Week 9 Week 10 15 17 18 14 14 14 13 15 16 14 19 11 Day 2 20 12 13 10 17 17 14 14 15 15 16 17 15 Day 3 13 18 17 1 12 14 15 12 11 1 17 Day 4 18 15 12 12 13 14 17 15 12 14 14 17 15 13 Day 5 16 16 15 15 18 16 14 13 12 16 16 19 Units FrequencyRelative Frequenc Percent Frequency 10 1 0.02 2% 11 2 0.04 4% 12 4 7 0.08 8% 070 13 1 6 0.12 12% 127 14 7 0.14 14% 15 8 8 o 0.16 16% 1070 16 8 0.16 16% 10 1070 17 17 6 0.12 12% 1270 18 5 0.1 10% 1070 19 2 0.04 4% 20 1 0.02 2% 50 1 18 16 Model & Inputs 65 9 9 Unit Revenue Unit Salvage Revenue Unit Variable Cost Daily Fixed Cost Daily Demand Daily Inventory Stocking Level 28 160 15 16 QTY sold Excess QTY Daily Sales Revenue (Normal Sales) Daily Salvage Revenue Total Revenues Less Total Variable Cost Contribution Margin Less Fixed Costs Profit 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. (Input parameters specified with the green cells). 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 demand required to achieve breakeven with the given fixed cost. Data Ten Week Sales Data (Units) Day 1 Week 1 Week 2 Week 3 Week 4 Week 5 Week 6 Week 7 Week 8 8 Week 9 Week 10 15 17 18 14 14 14 13 15 16 14 19 11 Day 2 20 12 13 10 17 17 14 14 15 15 16 17 15 Day 3 13 18 17 1 12 14 15 12 11 1 17 Day 4 18 15 12 12 13 14 17 15 12 14 14 17 15 13 Day 5 16 16 15 15 18 16 14 13 12 16 16 19 Units FrequencyRelative Frequenc Percent Frequency 10 1 0.02 2% 11 2 0.04 4% 12 4 7 0.08 8% 070 13 1 6 0.12 12% 127 14 7 0.14 14% 15 8 8 o 0.16 16% 1070 16 8 0.16 16% 10 1070 17 17 6 0.12 12% 1270 18 5 0.1 10% 1070 19 2 0.04 4% 20 1 0.02 2% 50 1 18 16 Model & Inputs 65 9 9 Unit Revenue Unit Salvage Revenue Unit Variable Cost Daily Fixed Cost Daily Demand Daily Inventory Stocking Level 28 160 15 16 QTY sold Excess QTY Daily Sales Revenue (Normal Sales) Daily Salvage Revenue Total Revenues Less Total Variable Cost Contribution Margin Less Fixed Costs Profit