OVERVIEW
Marketing is one of the most complex areas of business decision-making. Many input variables are qualitative in nature and cannot be accurately measured, e.g., changing consumer patterns, increasing competition, and sales forecasting in an uncertain economic environment. Some managers view marketing as an art form, with the qualities of experience and good judge- ment being considered more important than analytical model-building. However, spreadsheet modelling is an ideal tool for combining managerial knowledge with uncertain marketing information. Because many marketing decisions are based on a mixture of both quantitative details (e.g., past sales data) and qualitative predictions of a volatile market-place, they are called semi-structured. A semi-structured problem is one where the decision-maker has factual data to analyse but must use his or her own judgement to arrive at a satisfactory solution.
Sensitivity or ‘what-if’ analysis is a typical semi-structured approach in which the decision- maker asks a series of ‘what-if’ questions in order to determine how key factors respond to assumed changes in conditions. The ability to experiment with quantitative data in order to gain greater insight into a semi-structured situation lets the decision-maker see the consequences of certain actions. For example, how would a 5% increase in production costs affect product pricing?
Many companies have multiple and often contradictory goals. For example, they may seek to maximise sales at minimum cost or, perhaps to maximise profits while minimising risk. Goal programming (GP) which is a variation of linear programming, allows the decision-maker to specify multiple objectives which can then be placed in whatever order the decision-maker wishes. Although GP answers are non-optimal, they come as close as possible to meeting goals in the order specified by the decision-maker.
ORGANISING AND PRESENTING DATA
Statistics play an important part in marketing decisions. Descriptive statistics consists of tech- niques and measures that help decision-makers describe data. Unfortunately too much data can confuse rather than clarify, i.e., the decision-maker can’t see the information for the
data! Frequency distributions, histograms, pie charts, scatter plots, tables and bar charts are some of the tools that decision-makers use to help convert data into meaningful informa- tion. The easiest method of organising data is to construct a frequency distribution, using classes. A class is simply a specified interval of interest, usually having an upper and lower limit.
EXAMPLE 6.1 Interpreting sales data
Fred Doherty has been asked by his boss to make a presentation at next week’s end-of-year meeting of the Little Manufacturing Company. The presentation concerns the company’s sales figures for this year, and how they compare with last year’s sales. As sales manager, Fred must be in a position to answer any questions that may arise during the meeting. Fred has decided to use a standard line chart presentation containing both sets of sales figures for easier interpretation and analysis (see Figure 6.1). During the end-of-year meeting, Fred is asked to explain (i) the reason for the sudden fall-off in last year’s May sales, and (ii) why have sales improved so much in the latter part of this year?
Fred reminds the first questioner of the flood damage that was done to the company’s warehouse during last year’s storms. With so much stock damaged, sales for May were seriously affected. However, the sales force made considerable efforts during July and August to recoup lost sales. This year’s improved sales from September onwards are due to the introduction of a new sales strategy in mid-August. To date, the new dynamic sales approach is producing very satisfactory results.
Measures of Location and Spread
Presentation techniques such as frequency histograms can be misleading when making com- parisons between two different sets of data. For example, by varying the number and width of class intervals, histograms from two quite different sets of data may appear very similar. Other statistical techniques are needed to test for differences between data groups. The most common of these attempt to determine whether the two groups have the same distribution, i.e., have they the same central location and spread? The central location is simply the middle or centre of a set of data.
Common measures of central location are the mode, median and mean. The mean is often referred to as the arithmetic average. Statistical measures of spread are also called measures of variation (or dispersion) because they focus on fluctuations that occur on either side of the central location. The best known measures of spread are the range, variance and standard deviation, which is the square root of the variance.
EXAMPLE 6.2 Calculating the mean, variance, and standard deviation
A sales manager has examined past records for product X, and has calculated that sales (in ’000s) for the new version of product X will follow the pattern as shown in the probability table below:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 A B C D E F G H I J K
Example 6.1 - The Little Manufacturing Company
Month <- Sales (£'000s) -> Input data cells are shaded
Last Year This Year
January 7.0 7.2 Using ChartWizard to obtain graphical output
February 5.5 6.1 [1] Highlight the range B5:D16.
March 4.5 5.1 [2] Click the ChartWizard button (with coloured
April 6.0 5.7 columns) on the standard toolbar.
May 2.2 7.1 [3] Click chart type 'Line' and choose sub-type at
June 5.0 5.6 top of column 1. Then click on 'Next' button.
July 8.5 6.2 [4] Click 'Series' tab and enter 'Last Year' in the name
August 7.0 6.6 box for Series 1 and 'This Year' for Series 2.
September 6.1 7.7 [5] Click 'Titles' tab and enter y-axis description; next October 6.7 8.2 click 'Legend' tab and choose placement 'bottom'; November 4.6 8.4 click 'Gridlines' tab and clear 'Major gridlines' box. December 5.1 8.8 [6] Click 'Finish' button. Re-position and re-size chart.
2.0 3.0 4.0 5.0 6.0 7.0 8.0 9.0
January February March April May June July August
September OctoberNovember December
Sales (£'000s)
Last Year This Year
Figure 6.1 Graphical presentation of sales data.
Sales, Si 5 10 15 20 25 30
Probability, Pi 0.1 0.3 0.3 0.15 0.1 0.05
The sales manager is interested in the spread of data, i.e., how tightly packed or scattered is the data around the mean? She has decided to set up a simple spreadsheet as shown in Figure 6.2 to find the expected value (or mean), variance, and standard deviation of a probability distribution. The table’s sales data, Si, and probabilities, Pi, are used to define the standard
A 1 2 3 4 5 6 7 8 100 25 0 25 100 225 9 10 11 12 13 14 15 16 17 18 F6 D8 F10 F11 SUMPRODUCT(D3:I3, D4:I4) (D3 −$F6)^2 E8:I8 SUMPRODUCT(D4:I4, D8:I8) SQRT(F10) 19 B C D E F G H I J K
Example 6.2 - Finding the mean, variance, and standard deviation of sales data Sales ('000s), Si
(Si − E)2 =
Probability, Pi
Expected value, E, or Mean =
Variance = Standard deviation =
Cell Formula Copied to
42.5 6.52 15 5 0.1 10 0.3 15 0.3 20 0.15 25 0.1 30 0.05
User input cells are shaded
Figure 6.2 Mean, variance, and standard deviation of a probability distribution.
Mean or expected value, E=
n
i=1
PiSi n= 6 (number of values in sales distribution table)
Variance,σ2=n
i=1
Pi(Si− E)2
Standard deviation,σ = square root of the variance
CORRELATION ANALYSIS AND LINEAR REGRESSION
Correlation analysis and linear regression are two of the most widely used statistical tools for determining the linear relationship between two or more variables. When there are only two variables involved, the technique for prediction is called simple regression analysis. The quantitative measure of strength in a linear relationship between two variables is called the correlation coefficient. The closer the correlation coefficient is to+1.0 or −1.0, the stronger the linear relationship. If two variables have no linear relationship, the correlation between them is zero.
As an example, consider the sales manager of a manufacturing company who notices that there is a wide variation in monthly sales figures. He (or she) also notices that the company’s advertising expenditures change each month. The sales manager is interested in determining if a relationship exists between sales and advertising. If he could successfully define the
relationship, he might be able to use this information to improve predictions of monthly sales and, therefore, do a better job of planning.
A popular method for selecting the best regression line for a sample of data is the ‘least squares’ approach. It is a line-fitting technique that attempts to optimise the linear relationship between two variables. The ‘least squares’ method states that the best regression line is the one that minimises the sum of the squared distances between the observed (x, y) points and the regression line. Consider the following case study.
CASE STUDY 6.1 Predicting sales with Excel’s CORREL and TRENDLINE functions
Sharon Smith has just taken over from Harry Locke as sales manager of the KleenUp Company which manufactures household detergents and sells them directly to retail stores via a large force of salespeople. The owners have been concerned for some time about the company’s varying quarterly sales figures and have asked Sharon to look into the situation. To help clarify her ideas, Sharon has tabulated sales data for ten randomly selected sales areas as shown in Table 6.1.
The tabulated sales data is rather uninspiring, and Sharon has decided to plot the data to get a clearer picture. While looking through Harry Locke’s notes on previous sales data, Sharon notices that he was convinced that there could well be a direct link between sales figures and the number of stores visited by the company’s sales force. Unfortunately, he had no way of proving if such a connection did in fact exist, and as Harry was close to retirement age, his enthusiasm was not what it used to be!
Being new to the job, Sharon wants to impress the owners with her initiative. She has decided to find out if there is indeed a close relation between area sales and the number of retail outlets visited by the salespeople. If true, then Sharon could improve sales revenue by making sure that her sales force visit more stores in future. Not only would she be helping the owners but also her own reputation.
Sharon recently attended a business computing course and knows that statistical software can help her make some sense of the data which she has collected. For example, she would like
Table 6.1
No. of retail Last quarter’s Population of
Sales area stores visited sales (£’000s) sales area (’000s)
1 39 15 110 2 44 9 65 3 50 10 90 4 64 12 100 5 65 3 160 6 55 13 130 7 66 15 105 8 12 2 20 9 92 20 240 10 81 17 95
a way of measuring how close a relationship there is between the two variables, i.e., she needs to find if there is a strong correlation. Sharon starts checking through her course handouts and discovers that Excel’s statistical function CORREL gives her what she wants. Firstly, she enters her data table into a worksheet (see Figure 6.3). Then Sharon uses the following steps to create a scattergraph in order to see what the data looks like: