This
project is based on a model capable of forecasting the demand for a firm’s
product. This Decision Support System
analyzes various input variables prior to calculating the outputs. In order to develop a reliable Decision
Support System, regression analysis was performed to find accurate formulas to
implement into the spreadsheet model. In
order to build an accurate model, it was essential to find regression equations
for the following:
Total
Industry Demand (TID) = FD / MS
Market
Share (MS) = RD / N
Relative
Demand (RD) = MS * N
Firm
Demand (FD) = MS * TID
Data
for Widgets Inc. was based on the Modeling Firm Demand Case
was analyzed to create regression equations crucial to the operation of the
Decision Support System. Data from 10 competitors
was used in the regression analysis. The
available data for each firm was Relative Demand, Relative Price, Relative
Advertising, and Relative Demand from the prior quarter. The available data for
the entire industry was number of firms, total industry demand, quarter,
average price, and average advertising.
Here
are the following regression equations
TID
= 164336 – (445.17 * Avg. Price) + (.2627 * Avg. Advertising)
MS
= RD / # of firms or [16.13-(16.44 * Rel.Price) + .78
* Rel. Advertising + .53 * RD1] /10 (# of firms)
RD
= 16.13 – (16.44 * Rel. Price) + .78 * Rel. Advertising + .53 * RD1
FD
= (164336 – (445.17 * Avg. Price) + .2627 * Avg. Advertising) * [(16.13 –
(16.44 * Rel. Price) + .78 * Rel.
Advertising + .53 * RD1) / 10]
The
regression equations were then implemented into the following spreadsheet model
below. It is important to create a front end with separate sections for inputs
and outputs so that users are able to easily see where the relevant variables
are. The data should be on a separate sheet to avoid confusion for the end
user. Using the equations above the following DSS was created for Widgets Inc.
|
A Model For Forecasting Demand |
|
|
|
|
|
|
|
Inputs |
|
|
Outputs |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Total
Industry Demand |
|
|
28728.4 |
|
Estimates for Industry
for Current Quarter |
|
|
|
|
|
|
|
Estimated Average Price |
363 |
|
Relative
Demand |
|
|
1.043688 |
|
Estimated Average Advertising |
100000 |
|
|
|
|
|
|
Number of Firms in Industry |
10 |
|
Market
Share |
|
|
0.104369 |
|
|
|
|
|
|
|
|
|
Your Decisions for Current Quarter |
|
|
Your
Firm's Estimated Demand |
|
|
2998.35 |
|
Price |
364 |
|
|
|
|
|
|
Advertising |
90000 |
|
Average Demand |
|
2872.84 |
|
|
|
|
|
|
|
|
|
|
Historical Data for Previous Quarter |
|
|
|
|
|
|
|
Your Demand |
3000 |
|
|
|
|
|
|
Total Industry Demand |
21000 |
|
TID Model's Coefficients |
|
||
|
|
|
|
Variable |
Coefficient |
|
|
|
|
|
|
Constant |
13668.7 |
|
|
|
Calculations |
|
|
Quarter |
627.66 |
|
|
|
Relative Price (Qtr = t) |
1.002755 |
|
|
|
|
|
|
Relative Advertising (Qtr = t) |
0.9 |
|
RD Model's Coefficients |
|
||
|
|
|
|
|
|
|
|
|
Average Demand (Qtr = t-1) |
2100 |
|
|
Coefficients |
|
|
|
Your Firm's Relative Demand (Qtr = t-1) |
1.428571 |
|
Intercept |
16.18 |
|
|
|
|
|
|
Prel |
-16.5 |
|
|
|
|
|
|
Arel |
0.78 |
|
|
|
|
|
|
RD1 |
0.53 |
|
|
Summary Measures:
|
Summary
measures for selected variables |
|
|||
|
|
|
Avg_Price |
Avg_Adv |
TID |
|
|
Mean |
377.79 |
93325.79 |
20677.90 |
|
|
Median |
378.90 |
93400.000 |
19580.00 |
|
|
Standard
deviation |
6.10 |
9828.045 |
5233.01 |
|
|
Minimum |
365.00 |
76800.000 |
12020.00 |
|
|
Maximum |
387.50 |
108570.000 |
32850.00 |
Sensitivity
Analyses:
Sensitivity analysis
attempts to measure the variability of outcome to changes in the value of
individual inputs over which the company has control. This is important because all organizations
are constrained in terms of resources available to them and it makes economic
sense to concentrate resources on those inputs that are most important in terms
of potential impact on their bottom lines.
This firm has control over the price that it charges for widgets as well
as over the amount it spends on advertising.
To determine the sensitivity
of the firm’s demand and market share to changes in price, we decrease price of
the widget by 0.5% from $364 to $360.50 (holding everything else constant) and
noted a 9.2% increase in the firm’s demand from $2,998.35 to $3,236 and in
market share from 10.4% to 11.26%. This
implies an 18.4% change in demand (and market share) for every 1% change in
price.
|
A Model For Forecasting Demand |
|
|
|
|
|
|
|
Inputs |
|
|
Outputs |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Total
Industry Demand |
|
|
28728.4 |
|
Estimates for Industry
for Current Quarter |
|
|
|
|
|
|
|
Estimated Average Price |
363 |
|
Relative
Demand |
|
|
1.202779 |
|
Estimated Average Advertising |
100000 |
|
|
|
|
|
|
Number of Firms in Industry |
10 |
|
Market
Share |
|
|
0.120278 |
|
|
|
|
|
|
|
|
|
Your Decisions for Current Quarter |
|
|
Your
Firm's Estimated Demand |
|
|
3455.392 |
|
Price |
360.5 |
|
|
|
|
|
|
Advertising |
90000 |
|
Average Demand |
|
2872.84 |
|
|
|
|
|
|
|
|
|
|
Historical Data for Previous Quarter |
|
|
|
|
|
|
|
Your Demand |
3000 |
|
|
|
|
|
|
Total Industry Demand |
21000 |
|
TID Model's Coefficients |
|
||
|
|
|
|
Variable |
Coefficient |
|
|
|
|
|
|
Constant |
13668.7 |
|
|
|
Calculations |
|
|
Quarter |
627.66 |
|
|
|
Relative Price (Qtr = t) |
0.993113 |
|
|
|
|
|
|
Relative Advertising (Qtr = t) |
0.9 |
|
RD Model's Coefficients |
|
||
|
|
|
|
|
|
|
|
|
Average Demand (Qtr = t-1) |
2100 |
|
|
Coefficients |
|
|
|
Your Firm's Relative Demand (Qtr = t-1) |
1.428571 |
|
Intercept |
16.18 |
|
|
|
|
|
|
Prel |
-16.5 |
|
|
|
|
|
|
Arel |
0.78 |
|
|
|
|
|
|
RD1 |
0.53 |
|
|
To determine the sensitivity
of demand and market share to changes in advertising spending, we increase
advertising by 1% from $90,000 to $90,900 (holding everything else constant)
and noted a 0.67% increase in the firm’s demand from $2,998.35 to $3018.51 and
in market share from 10.43% to 10.50%.
From this sensitivity analysis, it can clearly be seen that this firm
needs to concentrate on maintaining the right price for its products as demand
is most sensitive to changes in the price it charges.
|
A Model For Forecasting Demand |
|
|
|
|
|
|
|
Inputs |
|
|
Outputs |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Total
Industry Demand |
|
|
28728.4 |
|
Estimates for Industry
for Current Quarter |
|
|
|
|
|
|
|
Estimated Average Price |
363 |
|
Relative
Demand |
|
|
1.050708 |
|
Estimated Average Advertising |
100000 |
|
|
|
|
|
|
Number of Firms in Industry |
10 |
|
Market
Share |
|
|
0.105071 |
|
|
|
|
|
|
|
|
|
Your Decisions for Current Quarter |
|
|
Your
Firm's Estimated Demand |
|
|
3018.517 |
|
Price |
364 |
|
|
|
|
|
|
Advertising |
90900 |
|
Average Demand |
|
2872.84 |
|
|
|
|
|
|
|
|
|
|
Historical Data for Previous Quarter |
|
|
|
|
|
|
|
Your Demand |
3000 |
|
|
|
|
|
|
Total Industry Demand |
21000 |
|
TID Model's Coefficients |
|
||
|
|
|
|
Variable |
Coefficient |
|
|
|
|
|
|
Constant |
13668.7 |
|
|
|
Calculations |
|
|
Quarter |
627.66 |
|
|
|
Relative Price (Qtr = t) |
1.002755 |
|
|
|
|
|
|
Relative Advertising (Qtr = t) |
0.909 |
|
RD Model's Coefficients |
|
||
|
|
|
|
|
|
|
|
|
Average Demand (Qtr = t-1) |
2100 |
|
|
Coefficients |
|
|
|
Your Firm's Relative Demand (Qtr = t-1) |
1.428571 |
|
Intercept |
16.18 |
|
|
|
|
|
|
Prel |
-16.5 |
|
|
|
|
|
|
Arel |
0.78 |
|
|
|
|
|
|
RD1 |
0.53 |
|
|
Widgets Inc. wants to grow to
what it considers a reasonable share of the market (at least 12%) and therefore
want to know what alternatives are available to it in order to achieve this
goal. From the analysis performed above,
we know that the most prudent thing to do vary its price as only a minimal
change in price would be need. After
substituting various prices in the DSS model, we noted that we need to decrease
price by approximately 1% to $360.50 to achieve this 12% market share
goal. This reduction in price seems
reasonable enough because to obtain the same results a 1% change to market
share has, we would have to increase advertising by approximately 22% ($20,000)
to $110,000.
Goal Seeking:
Widgets Inc. determines that
its widget is superior to most to the other products on the market and believes
as a result that it should be able to capture a bigger share of the
market. It wants to capture 15% market
share and want to know how to achieve this.
To get 15% market share, it was determined by using the Goal seeking
tool and changing the market share cell in the DSS model that it would have to
decrease its price by 3.02% to $353.96 or increase its advertising by 65%
($36,283.34) to $148,501. Obtaining this
goal seem not quite reasonable if they decrease their price by roughly %3
because that reduction would put them right below the minimum and most likely
cause the competition to raise their prices also.
There are, however,
possibility that there are combinations of changes in both price and
advertising that would generate the desired market share and the higher
associated revenue.
Scenario
Analysis:
Widgets Inc. determines the
following scenarios.
|
Scenario Summary |
|
|
|
|
|
|
|
|||
|
|
|
Current Values: |
best case |
Worst Case |
Expected |
Price Rise Only |
Price Fall only |
Advertising raise |
Advertising Fall |
|
|
Changing Cells: |
|
|
|
|
|
|
|
|
||
|
|
$B$12 |
364 |
364 |
370 |
365 |
366 |
362 |
364 |
364 |
|
|
|
$B$13 |
90000 |
148501.5027 |
80000 |
95000 |
90000 |
90000 |
102000 |
85000 |
|
|
Result Cells: |
|
|
|
|
|
|
|
|
||
|
|
$G$11 |
2998.349529 |
4309.260094 |
1990.766191 |
2979.806653 |
2737.182257 |
3259.516802 |
3267.247353 |
2886.308769 |
|
|
Notes: Current Values column represents values of
changing cells at time Scenario Summary Report was created. Changing cells for each scenario are
highlighted in gray. |
||||||||||
Future Enhancements:
This DSS model ignores
the fact that most companies are mostly concerned with profits. A future
enhancement therefore could be to incorporate other factors that companies are
likely to consider such as other costs, overhead, profits, etc.