The Parfitt and Collins model estimates repeat purchase brand share as the simple product of cumulative penetration percentage, repeat purchase percentage, and buying rate index.
The actual macro for the spreadsheet contains only an input routine. All computations are contained in the formulas found in cells J 11 to J 14 (see Figure 8-16). The macro identifies the number of periods to be entered, erases the unneeded row ranges and then moves the cursor from column location to column location (within the row), requesting the user to input the required data. The macro is shown in Figure 8-17.
Fourt and Woodlock ModelThe Fourt and Woodlock model estimates repeat purchase brand share as the simple product of the cumulative penetration percentage, repeat purchase percentage, and buying rate index.
The actual macro for the spreadsheet contains only an input routine. All computations are contained in the formulas found in cells C14 to F19 (see Figure 8-18). The macro determines the basic information in the upper half of the spreadsheet.
The model defines additional penetration for any, period as
Only two values are necessary for the equation-r and P. For example, suppose we believed that our new product would eventually be tried by 43 percent of all households (P = 0.43), and that in each period 10 percent of the remaining potential buyers would actually buy (r = 0.1). Using Fourt and Woodlock's equation, the new-buyer penetration of this market for the first period is
Q1 = rP(1 - r)^ 1-1 = r P = (0.1)(0.43) = 0.043
This is multiplied by the market size in units to give first purchase unit sales for the first period. First purchase unit sales for each period appear in column C of the model spreadsheet.
The repeat purchase unit sales figures appearing in column D are computed as first purchase sales times the repeat purchase ratio. For the first period, there would be no repeat purchase, since all purchases are made for the first time. In the second period, 2,086 units (48.5 percent of purchases in the first period, 4,300) would be repeated.
The cumulative sales figures appearing in column E are computed as the cumulative sum of first purchase and repeat purchase amounts appearing in columns C and D.
The dollar sales forecast by period is computed as the sum of first purchase unit sales and repeat purchase unit sales, multiplied by the price per unit.
The BCG analysis computes and graphs the portfolio grid associated with the BCG approach to product portfolio analysis. The spreadsheet is constructed in three basic sections: input, geometry calculations, and plotting. Up to five circles are to be plotted, variable in location on the x-y coordinates and in size.
The worksheet macro collects the four required data points for each brand to be plotted in the portfolio. Next, the geometry is computed to identify the area of the brand circle, the x and y coordinates of the circle center, the circle radius, and the scale increment factor. The algorithm identifies the center of the circle and then proceeds to determine the location of 24 plot points that lie, equally spaced, in a circle around each center. In addition, the x and y coordinate labels, circle labels, and grid lines are positioned and entered. The spreadsheet layout is shown in Figure 8-19. The data and computation sections are detailed in Figure 820.
MDPREF does multidimensional scaling of preference data. It is a metric model based on a principal components analysis (Eckart-Young decomposition). In this analysis, a data matrix of dimensions i subjects (usually attributes) by j stimuli (usually brands) is decomposed into two smaller matrices, each of which is an approximation of the original data matrix in a least squares sense.
The first resulting matrix is called a principal component score (or factor score) matrix of size i by r, where r is the number of underlying dimensions. This matrix depicts the i subjects in the r principal component dimensions and is designated as PCS.
The second matrix is called the principal component loading matrix (or factor loading matrix), and is of size r by j. This matrix depicts the j stimuli in the r principal component dimensions and is designated as PCL.
The MDPREF approach. The PC-MDS version of MDPREF uses the "first score matrix," which is the i-subject-by-j-stimuli matrix of evaluation scores. The first score matrix is used to produce the PCS and PCL matrices discussed above. Subsequent to this analysis, a second score matrix is produced, having dimensions i by j. The second score matrix contains derived projections of stimuli (brands) onto subject (attribute) vectors. The values of the second score matrix agree as nearly as possible, in a least squares sense, with the first scores matrix.
MDPREF is highly valued as an analytical procedure because the resulting values in the PCS and PCL matrices project the stimuli (brands) onto subject (attribute) vectors within the multidimensional stimuli-subject space. The model allows for visual evaluation of the j stimuli in r dimensional space, where r < j. The input section of the spreadsheet and the following output section, both annotated with interpretive comments on the right, are shown in Figure 8-21.
