# The use of excel iteration simulations in assessing risk of marketing new products.

Executive Summary

This paper addresses the increasing need for risk assessment in all business areas. Then the marketing techniques that can establish unit sales price, possible quantity sold, and associated standard deviations for a new product are reviewed. Once mean unit sales price, unit sales quantity, and the associated standard deviations are determined, potential total sales dollars can be estimated.

This is accomplished through the use of an Excel iterations simulation established by Gornik-Tomaszewski in 2014. Applying this process enables marketers to estimate mean total sales dollars and minimum and maximum total sales. In addition, this easily-applied, highly-comprehensible method provides a frequency distribution of total possible sales dollars and probabilities of sales outcome ranges.

Introduction

The first part of this paper describes the need for risk assessment in determining the total possible sales outcomes of marketing a new product. The second part summarizes how marketers determine the potential sales price and the possible quantities sold for a new product.

Most important, the final part of the paper introduces a possible new way for marketers to assess the risk associated with the prospective sales price and quantity to be sold, using an Excel iterations simulation to quantify the risk by calculating the probabilities associated with a new product introduction.

A second major advantage of this simulation model is that it can easily produce multiple iterations, with other randomly selected values of the uncertain elements in the model. In this case, the uncertain variables would be the unit sales price, the possible quantities sold, and their standard deviations (Gornik-Tomaszewski and Shoaf, 2015).

These replications can further represent to marketers the variation in results caused by uncertain inputs to the process, in order to better consider the level of risk inherent in marketing a new product.

Objective

The objective of this paper is to review risk assessment literature and marketing techniques to estimate a new product's unit sales price and sales quantity. Finally, the paper uses Excel iterations to establish mean, standard deviation, and minimum and maximum total sales dollars. In addition, a frequency distribution of potential total sales is provided along with probability percentages of sales ranges.

Literature Review

Risk Assessment

Risk analysis is part of every decision we make. We are constantly faced with uncertainty, ambiguity, and variability. And even though we have unprecedented access to information, we cannot accurately predict the future (Lucintel, n.d.; Richards, n.d.).

In reference to risk assessment, Gornik-Tomaszewski and Shoaf describe the consequences of the lack of risk analysis skill and experience in the financial service industry. This was a challenge for the majority of top management (Harner, 2009).

Management of risk within this industry was normally governed by computerized risk measurement systems based on intricate financial models. While this existed for financial products, knowledge of risk assessment was absent among employees (Gornik-Tomaszewski and Shoaf, 2015; Beasley, et. al., 2010).

The aftermath of the financial crisis in the early 21st century exposed the absence of risk management in all industries. The importance of risk management in managerial duties was only beginning to emerge as a significant competency and was lacking in top management who ascended to their positions by the time of the financial crisis (Beasley, et. al., 2010).

The use of market research methods to decide price and quantity as well as to evaluate associated risk allows organizations to gain an understanding of the risk exposure this product could bring. Doing so could facilitate the strategic decision-making process by reducing risk.

The potential benefits of applying market research tools when determining product price and quantity are frequently overlooked. Some methods, such as risk analysis, are vastly underused and disregarded when it comes to decision-making (Manning, 2010).

Quantitative analytic tools incorporate complex mathematical, scientific, and statistical frameworks, providing detailed and thorough quantification and measurement of risk. These measurements help create a comprehensive and sound strategy that will guide company goals and achievements.

Very often, however, risk assessments are not performed due to their complexity. Many do not understand their business application, stemming from lack of training (Hulett, 2004).

An important component of evaluating risk of possible outcomes is probabilities. A probability distribution is a statistical model that shows the possible outcomes of a particular event or course of action as well as the statistical likelihood of each event.

For example, a company might have a probability distribution for the change in sales given a particular marketing campaign. The values on the "tails," or the left and right end of the distribution, are much less likely to occur than those in the middle of the curve (Richards, n.d.).

Probability distributions can be used to create scenario analyses. A scenario analysis uses probability distributions to create several theoretically-distinct possibilities for the outcome of a particular course of action or future event. One practical use for probability distributions and scenario analysis in business is to predict future sales levels. It is essentially impossible to predict the precise value of a future sales level; however, businesses still need to be able to plan for future events.

Using a scenario analysis based on a probability distribution can help a company frame its possible future values in terms of a likely sales level and worst-case and best-case scenarios. By doing so, the company can base its business plans on the likely scenario but still be aware of the alternative possibilities (Richards, n.d.).

In addition to predicting future sales levels, probability distribution can be a useful tool for evaluating risk. Consider, for example, a company considering entering a new business line. If the company needs to generate \$500,000 in revenue in order to break even and their probability distribution tells them that there is a 90 percent chance that revenues will be more than \$500,000, the company knows roughly what level of risk it is facing if it decides to pursue that new business line (Richards, n.d.).

When a normal distribution is employed, the user simply defines the mean, and a standard deviation to describe the variation about the mean. Values in the middle near the mean are most likely to occur. It is symmetric and is used to describe many natural phenomena, such as people's heights, inflation rates and energy prices (Richards, n.d.).

Marketing techniques to determine sales price, quantity, and standard deviations

In attempting to use risk analysis from a marketing perspective related to the introduction of a new product, total sales comprise an estimate of the potential unit sales price and number of units. The following marketing methods can be used to determine unit sales price, number of units, and their standard deviations.

Unit Sales Price

Stenger (2008) details many of the most common pricing strategies. For a new product, Stenger recommends the Gabor-Granger model, Price Sensitivity Measurements, and Monadic Concept cells to price the product individually. For competitive product pricing, he suggests Brand-Price Trade-Off (BPTO) and Simple Discrete Choice (Stenger, 2008).

The Gabor-Granger model depends on answers from closed-ended questions to determine purchase price and intent. The indirect Gabor-Granger technique assigns a price to an item and, using a scale from one to five (with one being the most willing), asks consumers to rate how willing they would be to purchase the item.

If the consumer is willing to pay the predetermined price, the survey is re-administered with a higher established price to see whether the respondent is willing to pay more, and if so, how much more. If consent is not given for a higher price, the test is repeated with a lower value. The Gabor-Granger technique finds the highest price consumers will pay and their likelihood of paying each value.

The benefits of indirect pricing models include better accuracy, simple administration, and reasoning for purchase decisions. The ordinal nature of the scale, however, precludes calculation of means and standard deviations based on inherent measurement limitations (Lipovetsky, Magnan and Polzi, 2011).

The van Westendorp Model is an example that uses Price Sensitivity Measurements. This method is commonly referred to as the "psychological price" model. This method is most commonly used to balance value against price. It analyzes the common concept that low price is an indicator of low quality and vice versa.

The model assumes that reasonable prices exist in every category and for all levels of perceived quality. With this in mind, consumers have intrinsic upper and lower bounds for price and quality.

To gather results, four indirect questions are posed to establish prices that are classified as "too cheap," "cheap," "expensive," and "too expensive." Results are plotted on a graph and the matrix of intersection establishes results. The point of "optimal pricing" is defined by the intersection of "too cheap" and "too expensive," while the "indifference price point" is defined by "cheap" and "expensive" (Lipovetsky, Magnan and Polzi, 2011).

In the Monadic Concept, each respondent is shown only one proposal, with one price, and asked his purchase interest. If more prices need to be measured, new respondents must be added to the experiment and polled.

While easy to understand, execute, and incorporate, this model is very expensive to apply. Further, measures of purchase intent are known to predict much more reliably on the negative end of the scale than on the positive end (Stenger, 2008).

Brand-Price Trade-Off (BPTO) and Simple Discrete Choice are common competitive pricing models. The differentiating factor between the Gabor-Granger and BPTO model is that respondents are presented fixed prices for competing products, instead of for a single item, and asked which they would be most likely to buy.

As with the Gabor-Granger model, once a selection is made, the experiment is repeated with a change in price. By analyzing customer decisions, researchers can weigh how economic impacts of price changes serve as "balanced value" positions for price positioning ("Pricing Research", n.d.).

Discrete Choice Models are frequently preferred over BPTO, as they provide methodical information on price sensitivity and competition (Stenger, 2008; "Pricing Research", n.d.).

Unit Sales

Determining the size of a potential market is arguably one of the most important components of marketing a new product. Many firms mistakenly focus on product quality and neglect quantity estimations. They fail to realize that "qualitative information cannot reliably be used for quantitative decisions" and survival of a start-up.

The same can be said of the introduction of a new product (Waldes, 1995). Failure to establish a proper market and identify potential customers can lead to business failure. Conducting market research allows marketers to assess prospective demand. It enables companies to get an estimate of quantity needed (Dahl, 2011).

A starting point begins by firms asking themselves a series of questions:

- Does my product satisfy a market need?

- Who are potential customers and where can they be found?

- Does competition exist and do I have a competitive advantage?

Next, they should solicit product feedback though surveys and personal interviews. Last, they should test their theory. The "most valuable feedback you can get is whether someone will actually hand over money for" the product (Dahl, 2011).

In addition, in his article "The Missing Link," John Waldes claims that in order for businesses to predict quantitative estimations, they must first assemble a "tool kit" with two essential/vital components. The first tool is a "market model that simulates the outcome of potential marketing strategies."

The second tool "must test the market attractiveness of fully developed marketing strategies" or methods known as "controlled experiments."

Other essential characteristics of a market model that provide a manager with decision support include building the model with a multitude of choice factors, integrating the competitive market environment, maintaining the integrity of respondent choice structure, and incorporating a sufficient number of respondents to accurately represent the variance in choice structure. This "tool kit" better enables a firm to correctly estimate market size (Waldes, 1995).

Creating a product prototype helps to establish the Minimum Viable Product (MVP). As defined by Eric Ries, "A Minimum Viable Product is that version of a new product that allows a team to collect the maximum amount of validated learning about customers with the least effort" ("Minimum Viable Product", n.d.).

It is a company-building approach and development technique that creates a product with sufficient features to satisfy early adopters. "The final, complete set of features is only designed and developed after considering feedback from the product's initial users."(Janssen, n.d.) This is where, "the company has to come up with a version of its product that is complete enough to demonstrate the value it brings to the users" or a Minimum Viable Product (Dobrila, 2012).

This is analogous to a company's creating a product website to size up the market and determine how many customers are willing to pay for the item before it exists. The MVP can either confirm or refute the value and growth hypotheses (Dobrila, 2012).

Excel Iteration Simulation Example in Assessing the Risk of Marketing a New Product

In attempting to use an Excel iteration simulation in assessing risk, the unit sales price, unit quantity and their associated standard deviations based on the employment of the marketing techniques discussed above is vital. Once these items are determined, a simple simulation model using Excel iterations as proposed by Gornik-Tomaszewski and Shoaf can be used to assess risk.

Although they used this method for accounting and finance issues, they also stated that this method could be used in many business decisions (Gornik-Tomaszewski, et al., 2014).

In this simulation example (Exhibit 1), the mean unit sales price and quantity are set at \$1,000 and 2000, respectively. The standard deviations are assumed to be \$100 for unit sales price and 200 for unit quantity. These numbers are merely examples of possible outcomes that might be projected.

At this point, the iterations for unit sales price and quantity are determined using Excel's random number generator. For the unit sales price, the Excel function is =NORM.INV(RAND(), (\$B\$5), \$B\$6), with B5 being the mean unit sales price and B6 being the unit sales price standard deviation.

The sales quantity function is =NORM. INV(RAND(), (\$C\$5), \$C\$6).

C5 is the mean quantity and C6 is the quantity standard deviation.

Determining the size of a potential market is arguably one of the most important components of marketing a new product.

Using these Excel functions assumes that the inherent uncertainty in these two factors is stochastic (random) in nature, and the probability distribution for each of these random variables is normal (Gornik-Tomaszewski, 2014).

Once these functions are used, as recommended by Gornik-Tomaszewski, 100 iterations are used to determine possible outcomes. Instead of giving a single estimated value for these two variables, the 100 iterations estimates possible outcomes with the proposed mean of the variables and the uncertainty as reflected in the standard deviations (Gornik-Tomaszewski, 2014).

After the unit sales price and unit quantity are estimated for each iteration, the total sales dollars (Total Sales) are determined by multiplying each unit sales price by its corresponding unit quantity. The iteration results are then summarized to determine the mean (=AVERAGE(D12:D111)), standard deviation (=STDEVPA(D12:D111)), minimum sales dollars (=MIN(D11:D111)), and maximum total sales dollars (=MAX(D11:D111)).

The next step would be to create a frequency distribution table. A frequency table provides the distribution of possible total sales outcomes. The table will provide data ranges and retain a link to the data.

In Excel, this can be done using the FREQUENCY formula, which calculates how often certain values occur within a specified range and then returns a vertical array of numbers. The syntax for the formula is =FREQUENCY(data_ array,bins_array), where data_ array is the data set that contains the values for which we want to count frequencies and bins_array contains the intervals we want to use to group the values (Wayne and Albright, 2011).

Because FREQUENCY returns an array, it must be entered as an array formula. That requires selecting a range of adjacent cells into which we want the returned distribution to appear.

Furthermore, the number of elements in the returned array will be one greater than the number of elements in bins_array. The extra element in the returned array returns the count of any values above the highest interval (Wayne and Albright, 2011).

The frequency table appears in cells H6 to 19 in Figure 1. Cells H6:H18 contain the upper limit for each interval, so they represent the bins_ array. The ranges in the table are established by the user in creating the model so they will correspond with the data.

In this example, the ranges selected are 0-0, 0-1,000,000, 1,000,000-1,200,000, 1,200,000-1,400,000 and so on. Note that cell H19 lists >\$3,200,000, which is the overflow cell for any values above the highest range.

Next, we need to highlight the range 16 to 119. Then type in =FREQUENCY(E12:E111,H6:H19) and then press CTRL+SHIFT+ENTER, which enters it as an array formula. If the formula is not entered as an array formula, there will be only one result in cell H6. If entered correctly, results will appear in each of the cells selected. The Total is determined by entering =SUM(I6:I19).

The final step in this Excel spreadsheet is to determine the probability percentages of a range of sales outcomes. The ranges are determined by the user. The number of range probabilities is also up to the user.

The probabilities are located in the bottom right of Exhibit 1. In this example, the total sales ranges used are from \$1,600,000 to \$3,200,000, \$1,800,000 to \$3,200,000, etc.

These probabilities are determined by using the Excel sales iterations mean and standard deviations located in cells E116 and E117. Using Cell G13 in Exhibit 1 as an example of how to calculate the probabilities of sales ranges from \$1,600,000 to \$3,200,000, the following Excel function is used:

NORM.DIST(H18,E116,E117,TRUE)-NORM. DIST(H10,E116,E117,TRUE).

Since a normal distribution was used to create the sales iterations, the normal distribution function is used. Cell H18 is the upper range (\$3,600,000), E116 is the mean sales amount of the Excel iterations (\$2,019,420), and E117 is the standard deviation (\$272,421). "True" is entered to determine the cumulative distribution function.

The second step in this function is to subtract the low range (\$1,600,000) in cell H10. The rest of the formula is the same as the first part of the function. The mean (E116), standard deviation (E117), and True are entered. The result is 94 percent.

Finally, the same function is used for the other sales ranges. The only difference is that the lower range is changed to \$1,800,000 (Cell H11), \$2,000,000 (H12) and so on.

[ILLUSTRATION OMITTED]

The results from the Exhibit 1 simulation provide important information that marketers can use to gain an understanding of the possible variation in total sales, and thus, the risks associated with a new product introduction.

First, through use of the Excel 100 iterations, the resultant mean, standard deviation, minimum and maximum are better estimations of possible outcomes than those that would be obtained through only a few iterations.

In addition, once the Excel spreadsheet is created, the calculations may be run multiple times in order to see the variation in sales outcomes. These multiple runs may also include changing the means and standard deviations for the unit sales price and quantity sold.

The second information supplied comes from the frequency distribution table. From the table, a marketer can see the normal distribution of the 100 iterations of total sales. In Exhibit 1, the frequency table shows that the highest possibility of total sales ranges from \$1,800,000 to \$2,400,000.

Finally, the most important result is the probability computations. These calculations quantify the probabilities of sales outcome ranges. In this simulation example, the probabilities of total sales ranges are 94 percent for sales ranging from \$1,600,000 to \$3,200,000.

At the same time, the probability of sales ranging from \$2,400,000 to \$3,200,000 is only eight percent. The probabilities for sales ranges between these two extremes are also determined.

These probabilities are important because with any new product introduction, the company has probably determined the minimum sales required for success. For example, if the company has determined that the minimum total sales needs needed is \$2,200,000, the probability of this outcome is only 25 percent. At the same time, if the minimum sales needed are \$1,800,000, the probability is 79 percent.

Conclusion

With the growing emphasis on assessing risk across all business decisions, Excel iteration simulations offer a promising and extremely simple way of quantifying risk. In this paper, the method was applied to the risk assessment of total sales associated with a new product introduction.

The example utilized amply illustrates both the pedagogical and practical benefits of the method. It serves as a great learning tool for students, also offering potential application in their future business careers.

Constructing this Excel iterations spreadsheet is relatively easy and the results can be easily understood. Finally, once the spreadsheet is constructed, it can be used repeatedly for other assessments of possible total sales estimations.

References

Beasley, M., Branson, B. and B. Hancock. (2010). Are you identifying your most significant risks? Strategic Finance, Vol. 92, No. 5, 29-35.

Dahl, D. (2011, September 6). How to Assess the Market Potential of Your Idea. Retrieved May 14, 2015, from http://www.inc.com/guides/201109/how-to-assess-the-market-potential-of-your-new-business-idea.html

Dobrila, R. M. (2012). Minimum viable product and the importance of experimentation in technology startups. Technology Innovation Management Review,2 (3), 23-26.

Gornik-Tomaszewski, S. 2014. Capital budgeting simulation using Excel: Enhancing the discussion of risk in managerial accounting classes. Management Accounting Quarterly (Summer): 12-17.

Gornik-Tomaszewski, S., Shoaf, V. 2015. Building Risk Analysis Skills into the Curriculum with Simulation Examples in Excel. Global Business: Toward New Paradigm in the Time of Crisis. Katowice: Publishing House of the University of Economics in Katowice, 40-50.

Harner, Michelle M. (2010). Ignoring the Writing on the Wall: The Role of Enterprise Risk Management in the Economic Crisis, Journal of Business and Technology Law, Vol. 5, p. 45.

Hulett, D. (2004, December). Using Quantitative Risk Analysis to Support Strategic Decisions. Retrieved November 5, 2014, from http://www.projectrisk.com/white_papers/Using_Quantitative_Risk_Analysis.pdf

Janssen, C. (n.d.). What is a Minimum Viable Product (MVP)? Retrieved May 14, 2015, from http://www.techopedia.com/definition/27809/minimum-viable-product-mvp

Lipovetsky, S., Magnan, S., and Polzi, A. (2011, March 27,). Pricing Models in Marketing Research. Retrieved November 5, 2014 from http://file.scirp.org/pdf/IIM20110500007_64675493.pdf

Lucintel (n.d.) Risk Assessment. retrieved June 14, 2016 from http://www.lucintel.com/risk_assessment.aspx

Manning, T. (2010, February 4,). Marketing Risk Management: Seeing Around the Corner for Improved Performance. Retrieved June 14, 2015 from http://customerthink.com/marketing_risk_management_seeing_around_the_corner/

Minimum Viable Product, (n.d.). Retrieved May 14, 2015, from http://leanstack.com/minimum-viable-product/

Pricing Research and Pricing Optimization. (n.d.). Retrieved January 3, 2015, from http://www.dobney.com/Research/pricing_research.htm.

Richards, Leigh, (n.d.). The Role of Probability Distribution in Business Management. Retrieved June 14, 2016 from http://smallbusiness.chron.com/role-probability-distribution-business-management-26268.html

Stenger, C. (2008, August). Choosing the Best Pricing Techniques to Address Consumer Goods Pricing Challenges. Retrieved November 5, 2014, from http://www.ipsos.com/marketing/sites/www.ipsos.com.marketing/files/pdf/BestPricingTechniques.pdf

Waldes, J. (1995, Mar/Apr). The missing link. American Demographics, 18.

Wayne L. Winston and S. Christian Albright (2011). Practical Management Science: Spreadsheet Modeling and Applications. Fourth edition. Cengage Learning.

Sylvia D. Clark, The Peter J. Tobin College of Business, St. John's University, New York

clark1094@aol.com

Craig A. Latshaw, The Peter J. Tobin College of Business, St. John's University, New York

craiglatshaw35@gmail.com

Ariel Napolitano, Nielsen Innovation Practice, New York

Ariel.Napolitano@nielsen.com