Hello, the scenario below is for Business Modeling Simulation. An Excel spreadsheet has to be made based on the information below.

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 a 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 $200.

To make the requested histogram, you can either mimic what was done in the newsvendor spreadsheet simulation - provided below, or use a different approach via whatever built-in Excel facilities you'd like.(HINT:To decide whether a customer gets a loyalty discount, explore the Excel IF function with the first argument's being a random number RAND() distributed uniformly between 0 and 1; do similarly todecide on a coupon discount.)

Use the IF test function in Excel to determine if a customer does or does not get the 4% or the 7% discount.

=IF (Logic_Test,Value_if_True,Value_if_False)

Here, 'Logic_Test' refers to the expression that is to be evaluated.

'Value_if_True' is the output of IF Statement if the 'Logic_Test' is TRUE.

'Value_if_False' is the output of IF Statement if the 'Logic_Test' is FALSE.

The grading rubric for the Excel spreadsheet for this assignment is based on the following:

Discount Logic

Raw Purchase Logic

Average, Standard Deviation, Min and Max

and construct a histogram