Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

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

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 put in place 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

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 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 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 the 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

Step by Step Solution

3.47 Rating (154 Votes )

There are 3 Steps involved in it

Step: 1

The global COVID19 has caused turbulence round the world every verticals roundfaced a special set of ... blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image

Step: 3

blur-text-image

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

Cost Accounting A Managerial Emphasis

Authors: Horngren, Srikant Datar, George Foster, Madhav Rajan, Christ

6th Canadian edition

978-0132893534, 9780133389401, 132893533, 133389405, 978-0133392883

More Books

Students also viewed these Accounting questions

Question

What is your greatest strength?

Answered: 1 week ago