Reference no: EM132847453

Supermarket Calendar Week Average Sales in $ ('000s)

Average Weekly Sales over 5 years 1 $350.00 Normal Distribution 2 $360.00 Standard Deviation 5 3 $350.00 3Sigma Mininmum 44.5% of Sales 15 4 $380.00 3Sigma Maximum 44.5% of Sales 15 5 $370.00 Probability of Loyalty Card 0.63 6 $380.00 Discount Loyalty Card 0.04 7 $350.00 8 $380.00 9 $360.00 10 $330.00 11 $350.00 12 $390.00 13 $390.00 14 $380.00 15 $350.00 16 $340.00 17 $380.00 18 $400.00 19 $360.00 20 $510.00 21 $500.00 22 $380.00 23 $390.00 24 $370.00 25 $360.00 26 $370.00 27 $550.00 28 $480.00 29 $380.00 30 $350.00 31 $330.00 32 $340.00 33 $380.00 34 $360.00 35 $390.00 36 $340.00 37 $370.00 38 $350.00 39 $370.00 40 $330.00 41 $340.00 42 $320.00 43 $380.00 44 $360.00 45 $480.00 46 $510.00 47 $550.00 48 $550.00 49 $400.00 50 $500.00 51 $480.00 52 $550.00

How much do loyalty programs cost the supermarkets? A deterministic and stochastic mathematical excel model exercise.

Your neighbor supermarket business is a low-margin operation, with the average profit margin of 2 percent. In analyzing historical sales data for the past 5 years, you noticed weekly sales have been declining about 2-5% due to new local competition. You realized that one of your competition has put in place a 'reward' loyalty card program. In order to stop sales decline, you decide to follow suite and put in place a reward loyalty card program too. But, you want to estimate the impact to your average profit margin.

Monthly Average Sales from Regression Analysis

Supermarket Regression Analysis Data.xlsx

Assignment: Your are to build an excel math model sheet of the supermarket annual business results based on the attached sales history. The sales history file is based on a regression analysis performed by the sales business analyst department. It has Average (Mean) monthly sales from past five years.

Loyalty Card - you expect that 63% of your customers will sign up. The loyalty card will give them 4% off their sales order.

Helpful Guidelines:

For the Deterministic Model, use the Average Weekly Sales. To get discount per week, multiply each average weekly sale by the average discount of 0.63 * 0.04. Calculate new sales, and then the difference in profit margin (The cost of the Loyalty Program). Tabulate it.

For the Stochastic Model, use a Normal Distribution Curve, with the Mean being the Average Weekly Sales, and the weekly Standard Deviation being $5,000. That is, all weekly sales will fall between the Average - 3 Standard Deviation and Average + 3 Standard Deviation. For example, if average weekly says is 100,000 then the 99% of weekly sales would fall between 85,000 and 115,000. Use the RANDBETWEEN function in excel to get this random sales number. To get discount per week, once again use a RAND () function to get a 0 (no loyalty card) or a 1 (loyalty card) and calculate the random discount based on loyalty card info. Calculate new sales, and difference in profit margin (The cost of the Loyalty Program). Now, since this is a stochastic model, you need to run it three times (use the F9 function to re-calculate spreadsheet). Make sure you tabulate each iteration so you can get a MEAN of the Cost of Loyalty Program.

Calculate the new profit margin based on the results from your deterministic model and your stochastic model.

Input Data:

At checkout, 63% of customers have a loyalty card that gives them 4% off their raw order amount.

Normal Distribution - Mean Weekly Sales, with a Standard Deviation of $5000