Question

Supermarket customers load their carts with goods totaling between $5 and $200, uniformly (and continuously) distributed; call this the raw order amount. Assume that customers purchase independently of each other. At checkout, 63% of customers have loyalty card that gives them 4% off their raw order amount. Also at checkout, 18% of customers have coupons that give them 7% off their raw order amount. These two discounts occur independently of each other, and a given customer could have one or the other of them, both of them, or neither of them, to get to their net order amount (what they actually pay). Construct a spreadsheet simulation to simulate 100 customers and collect statistics on the net order amount; these statistics should include the average, standard deviation, minimum, maximum, and a histogram to describe the distribution of the net order amounts between $0 and $250. To make the requested histogram, you can either mimic what was done in the newsvendor spreadsheet simulation, or use a different approach via whatever built in excel facilities you'd like. Excel sheet with formulas would be best thanks.

Answer 1

I shall frame the answers step-by-step for clear understanding:

First I have created 100 random samples between 5 and 200

Formula: RANDBETWEEN(5,200)

Next, I have applied 4% loyalty discount for top 63 customers, as the numbers are random, then I can safely choose top 63 customers for the selection of loyalty

Now, for the Coupon enabled customers I have chosen 9 from the loyalty ones and 9 from the non-loyalty ones. Hence I got the coupon enabled customers.

Now, I have got the Net price from these columns. Then I executed the Descriptive statistics from Data add-on in excel and got the summary measures.

Next, from the Data Analysis tool pack, I got the Histogram, and I created bins with bin length 25.

Now with the help of bins and Data, I have created the histogram.

******************************************************************************************************************************************
Now based on the values, I got the following summary measures. I can not show the numbers or data as the answer limit is being longer than 65K

Column1
Mean	100.5303
Standard Error	5.472699093
Median	100.32
Mode	50.88
Standard Deviation	54.72699093
Sample Variance	2995.043536
Kurtosis	-1.133755541
Skewness	-0.042374901
Range	195.2
Minimum	4.8
Maximum	200
Sum	10053.03
Count	100

The bins I have used here is stated below:

Bin	Frequency
0	0
25	11
50	11
75	14
100	14
125	12
150	13
175	15
200	10
225	0
250	0
More	0

Accordingly, I got the histogram here

As per the limitation, the excel sheet can not be uploaded, hence I have stated all the formulas and tools which have been used here.

Hope this answer has helped you. If you have some queries regarding this, do let me know in the comment section.

Hit like if the answer helped you.

Thanks !!