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

Your neighbor's supermarket business is a low-margin operation, with an 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 competitors has implemented a 'reward' loyalty card program. In order to stop the sales decline, you decide to follow suit and put in place a reward loyalty card program too. But, you want to estimate the impact on your average profit margin.

Monthly Average Sales from Regression Analysis

Supermarket Regression Analysis Data.xlsx Download Supermarket Regression Analysis Data.xlsx

Assignment: You are to build an excel math model sheet of the supermarket's 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 had Average (Mean) monthly sales for the 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 a 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 the 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 a 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 the 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 the 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

Supermarket Average Weekly Sales over 5 years Normal Distribution Standard Deviation 3Sigma Mininmum 44.5% of Sales 3Sigma Maximum 44.5% of Sales Probability of Loyalty Card Discount Loyalty Card 5 15 15 0.63 0.04 Calendar Week AN 10399399599222232252222222225289799399599858 3 4 5 6 11 14 16 34 36 40 41 49 Average Sales in $ ('000) $350.00 $360.00 $350.00 $380.00 $370.00 $380.00 $350.00 $380.00 $360.00 $330.00 $350.00 $390.00 $390.00 $380.00 $350.00 $340.00 $380.00 $400.00 $360.00 $510.00 $500.00 $380.00 $390.00 $370.00 $360.00 $370.00 $550.00 $480.00 $380.00 $350.00 $330.00 $340.00 $380.00 $360.00 $390.00 $340.00 $370.00 $350.00 $370.00 $330.00 $340.00 $320.00 $380.00 $360.00 $480.00 $510.00 $550.00 $550.00 $400.00 $500.00 $480.00 $550.00