## (solution) Simulation for Taks Taks Home Furnishing sales 18 cubic-foot

Simulation for Taks

Taks Home Furnishing sales 18 cubic-foot Whirlpool refrigerators, which come in three colors, white, almond and harvest gold. Each day, the store expects between 0 and 4 customers interested in buying a refrigerator to arrive at the store according to the following probability distribution:

P(0 arrivals) = 0.15

P(1 arrivals) = 0.25

P(2 arrivals) = 0.30

P(3 arrivals) = 0.20

P(4 arrivals) = 0.10

For each of these customers, there is a 60% chance that the person will decide to purchase a refrigerator. Taks also knows that 40% of customers desire a white refrigerator, 25% desire an almond refrigerator, and 35% desire a gold refrigerator. If the store is sold out of a particular color choice, the customer will leave without making a purchase. In order not to lose sales while maintaining low inventory, it is critical to estimate how many refrigerators in each color will be sold each month.

You are assigned to develop a simulation worksheet in Excel to simulate sales in Taks for a month to provide a baseline estimate for its inventory, by following these steps.

1. Referring to excel worksheet titled "demo", learn how to simulate the decision of the first customer on day 1 by carefully studying all the formulas/functions used in the example and understanding the logics behind them. Consult with Excel Help if clarification is needed.

2. Apply what you have learned from the "demo" and use the worksheet titled "simulation" to conduct the actual simulation for 30 days.

• ?  You need to enter proper formulas/functions from "A10" to "W39";
• ?  You need to tally number of refrigerators sold in each color and put them in R3-R5;
• ?  Run the simulation 10 times, (selecting an empty cell and clicking "delete" key regenerates the result) and save the 10 results for # sold in a separate sheet titled "report";
• ?  Use the average of the results from 10 simulation runs as the recommendation for inventory.

Day R# 1 0.2040 # Arrive Cust # 1 R# Pur (Y/N) 1 0.5892 yes R# color (W/A/G) 0.3002 white pdf

0.15

0.25

0.30

0.20

0.10 cdf

0.15

0.40

0.7

0.9

1 vlookup bracket

0

0.15

0.40

0.7

0.9 # arrivals

0

1

2

3

4 pdf

0.60

0.40 cdf

0.6

1 vlookup bracket

0

0.6 Decision

yes

no pdf

0.40

0.25

0.35 cdf

0.4

0.65

1 vlookup bracket

0

0.4

0.65 color

white

almond

gold # of gold refrigerator sold

1 Comment:

you can select any empty cell and press &quot;delete&quot; key in your keyboard to refresh

the random number generated by the function =rand().

by doing so, you can check whether the formulas/functions cover all the scenarios correctly. Vlookup

0

0.15

0.4

0.7

0.9

Day 1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30 R# # Arrive Cust # R# # arrival

0

1

2

3

4

Pur (Y/N) Vlookup Decision

0

yes

0.6

no R# color Cust # R# Pur (Y/N) R# vlookup bcolor

0

white

0.4

almond

0.65

gold color Cust # R# Pur (Y/N) # sold R# color Cust # R# Pur (Y/N) R# color

Solution details:

STATUS

QUALITY

Approved

Sep 13, 2020

EXPERT

Tutor