DSS – DECISION SUPPORT SYSTEMS
DSS are interactive computer-based systems
and subsystems intended to help decision makers use communications,
technologies, data, documents, knowledge and /or models to successfully
complete decision process tasks.
DSS development.
Estimating a firm’s
demand is very important in helping its managing board to make right operating
decisions.
Historical data of Total Industry Demand (TID), is used to predict
the Firm’s demand in the next quarter. Estimating the Average price and Average
Advertising for the industry and given number of firms in the industry we
chouse our firm’s price and advertising. Using the formulas of linear
regression developed for the previous project, we calculate Firm’s Demand as FD = TID*MS
MS(Market
Share) = RD(Relative Demand)/N(Number of firms on the market)
FD = TID*RD/N - Using the results of linear regression done in the past, we calculate TID = 13668.7 + 627*Qt and
RD = 16.18 – 16.5Prel
+ 0.78Arel + 0.53RD1.
Snapshot of
spreadsheet model below shows the values of outputs for the number of Decision
and Estimated inputs. For this particular example the Firm’s price is higher
than estimated average price, and the Firm’s expenditure on advertising is
lower than that of average of the industry. Given the number of Firms in the
Industry, we have results for the Firm’s market share which is 6.5%.
|
Inputs |
|
|
Quarter#
"t" |
20 |
|
|
|
|
Estimates for Industry (for Qtr
"t") |
|
|
Estimated Average Price |
363 |
|
Estimated Average Advertising |
110000 |
|
Number of Firms in Industry |
10 |
|
|
|
|
Firm's Decisions (for Qtr.
"t") |
|
|
Price |
368 |
|
Advertising |
100000 |
|
|
|
|
Historical Data for Qtr = t-1 |
|
|
Firm's Demand |
3000 |
|
Total Industry Demand |
32850 |
|
|
|
|
|
|
|
Calculations |
|
|
Relative Price (Qtr = t) |
1.013774 |
|
Relative Advertising (Qtr = t) |
0.909091 |
|
|
|
|
Average Demand (Qtr = t-1) |
3285 |
|
Firm's
Relative Demand (Qtr = t-1) |
0.913242 |
|
Outputs |
|
|
|
|
|
|
|
|
|
Total Industry Demand |
|
|
26221.9 |
|
|
|
|
|
|
Relative Demand |
|
|
0.654002 |
|
|
|
|
|
|
Market Share |
|
|
0.0654 |
|
|
|
|
|
|
Firm's Estimated Demand |
|
|
1714.918 |
|
|
|
|
|
|
Average Demand |
|
2622.19 |
|
|
TID
Model's Coefficients |
||
|
Variable |
Coefficient |
|
|
Constant |
13668.70 |
|
|
Quarter |
627.66 |
|
|
|
|
|
|
RD
Model's Coefficients |
||
|
|
|
|
|
|
Coefficients |
|
|
Intercept |
16.18 |
|
|
Prel |
-16.50 |
|
|
Arel |
0.78 |
|
|
RD1 |
0.53 |
|
Sensitivity
(What – if) Analysis.
Let’s assume that our
Firm’s goal is to have at least 10.5% of the total market share. DSS model can
be used to determine what changes should be made in price charged by the Firm
and its advertising expenditures. We use Estimated Average Price for the
Industry 363, and Estimated Average Advertising 110000, and manipulate numbers
in reasonable range until we reach desired results.
|
Inputs |
|
|
Quarter#
"t" |
20 |
|
|
|
|
Estimates for Industry (for Qtr
"t") |
|
|
Estimated Average Price |
363 |
|
Estimated Average Advertising |
110000 |
|
Number of Firms in Industry |
10 |
|
|
|
|
Firm's Decisions (for Qtr.
"t") |
|
|
Price |
362 |
|
Advertising |
118000 |
|
|
|
|
Historical Data for Qtr = t-1 |
|
|
Firm's
Demand |
3000 |
|
Total Industry Demand |
32850 |
|
|
|
|
|
|
|
Calculations |
|
|
Relative Price (Qtr = t) |
0.997245 |
|
Relative Advertising (Qtr = t) |
1.072727 |
|
|
|
|
Average Demand (Qtr = t-1) |
3285 |
|
Firm's Relative Demand (Qtr = t-1) |
0.913242 |
|
Outputs |
|
|
|
|
|
|
|
|
|
Total
Industry Demand |
|
|
26221.9 |
|
|
|
|
|
|
Relative Demand |
|
|
1.054153 |
|
|
|
|
|
|
Market
Share |
|
|
0.105415 |
|
|
|
|
|
|
Firm's
Estimated Demand |
|
|
2764.191 |
|
|
|
|
|
|
Average
Demand |
|
2622.19 |
|
As
it can be seen from the spreadsheet, reaching desired
Results
in Percentage of Market Share requires Price reduction below the Estimated Average, and Increase in
Advertising.
Goal Seeking Analysis.
Using results of the
previous analysis as a starting point, we use Goal Seek analysis (Excel Tools),
in order to determine what changes should be done in Firm’s price in order to
have 11.5% market share.
|
Inputs |
|
|
Quarter#
"t" |
20 |
|
|
|
|
Estimates for Industry (for Qtr
"t") |
|
|
Estimated Average Price |
363 |
|
Estimated Average Advertising |
110000 |
|
Number of Firms in Industry |
10 |
|
|
|
|
Firm's Decisions (for Qtr.
"t") |
|
|
Price |
359.85 |
|
Advertising |
118000 |
|
|
|
|
Historical Data for Qtr = t-1 |
|
|
Firm's
Demand |
3000 |
|
Total Industry Demand |
32850 |
|
|
|
|
|
|
|
Calculations |
|
|
Relative Price (Qtr = t) |
0.991322 |
|
Relative Advertising (Qtr = t) |
1.072727 |
|
|
|
|
Average Demand (Qtr = t-1) |
3285 |
|
Firm's Relative Demand (Qtr = t-1) |
0.913242 |
|
Outputs |
|
|
|
|
|
|
|
|
|
Total
Industry Demand |
|
|
26221.9 |
|
|
|
|
|
|
Relative
Demand |
|
|
1.151855 |
|
|
|
|
|
|
Market
Share |
|
|
0.115185 |
|
|
|
|
|
|
Firm's
Estimated Demand |
|
|
3020.381 |
|
|
|
|
|
|
Average
Demand |
|
2622.19 |
|
As it can be seen,
Firm will have to drop price to 359.85, while maintaining higher than average
Advertising Expenditure, what can probably be unrealistic.
Or, the Advertising Expenditures will
have to go up drastically, which also could be unrealistic to implement.
|
Inputs |
|
|
Quarter#
"t" |
20 |
|
|
|
|
Estimates for Industry (for Qtr
"t") |
|
|
Estimated Average Price |
363 |
|
Estimated Average Advertising |
110000 |
|
Number of Firms in Industry |
10 |
|
|
|
|
Firm's Decisions (for Qtr.
"t") |
|
|
Price |
362 |
|
Advertising |
130980 |
|
|
|
|
Historical Data for Qtr = t-1 |
|
|
Firm's
Demand |
3000 |
|
Total Industry Demand |
32850 |
|
|
|
|
|
|
|
Calculations |
|
|
Relative Price (Qtr = t) |
0.997245 |
|
Relative Advertising (Qtr = t) |
1.190727 |
|
|
|
|
Average Demand (Qtr = t-1) |
3285 |
|
Firm's Relative Demand (Qtr = t-1) |
0.913242 |
|
Outputs |
|
|
|
|
|
|
|
|
|
Total
Industry Demand |
|
|
26221.9 |
|
|
|
|
|
|
Relative
Demand |
|
|
1.146093 |
|
|
|
|
|
|
Market
Share |
|
|
0.114609 |
|
|
|
|
|
|
Firm's
Estimated Demand |
|
|
3005.273 |
|
|
|
|
|
|
Average
Demand |
|
2622.19 |
|
Scenario Analysis.
Using the Results of
Sensitivity Analysis, we can evaluate various scenarios. Using DSS Model, we
can see what happens to the Firm’s demand and its market share if Estimated
Average Price for the industry and Average Advertising
changes. We use the worst and best case scenarios when Average Price drops to
360, Average Advertising rises 1150000,
and Average Industry Price rises to 366, while Average Advertising drops to
105000.
Worst Case: Competition drops prices and
increases advertising. (360, 115000)
|
Inputs |
|
|
Quarter#
"t" |
20 |
|
|
|
|
Estimates for Industry (for Qtr
"t") |
|
|
Estimated Average Price |
360 |
|
Estimated Average Advertising |
115000 |
|
Number of Firms in Industry |
10 |
|
|
|
|
Firm's Decisions (for Qtr.
"t") |
|
|
Price |
368 |
|
Advertising |
100000 |
|
|
|
|
Historical Data for Qtr = t-1 |
|
|
Firm's
Demand |
3000 |
|
Total Industry Demand |
32850 |
|
|
|
|
|
|
|
Calculations |
|
|
Relative Price (Qtr = t) |
1.022222 |
|
Relative Advertising (Qtr = t) |
0.869565 |
|
|
|
|
Average Demand (Qtr = t-1) |
3285 |
|
Firm's Relative Demand (Qtr = t-1) |
0.913242 |
|
Outputs |
|
|
|
|
|
|
|
|
|
Total
Industry Demand |
|
|
26221.9 |
|
|
|
|
|
|
Relative
Demand |
|
|
0.483849 |
|
|
|
|
|
|
Market
Share |
|
|
0.048385 |
|
|
|
|
|
|
Firm's
Estimated Demand |
|
|
1268.744 |
|
|
|
|
|
|
Average
Demand |
|
2622.19 |
|
Best Case: Competition Increases price and
drops advertising. (366, 105000)
|
Inputs |
|
|
Quarter#
"t" |
20 |
|
|
|
|
Estimates for Industry (for Qtr
"t") |
|
|
Estimated Average Price |
366 |
|
Estimated Average Advertising |
105000 |
|
Number of Firms in Industry |
10 |
|
|
|
|
Firm's Decisions (for Qtr.
"t") |
|
|
Price |
368 |
|
Advertising |
100000 |
|
|
|
|
Historical Data for Qtr = t-1 |
|
|
Firm's
Demand |
3000 |
|
Total Industry Demand |
32850 |
|
|
|
|
|
|
|
Calculations |
|
|
Relative Price (Qtr = t) |
1.005464 |
|
Relative
Advertising (Qtr = t) |
0.952381 |
|
|
|
|
Average Demand (Qtr = t-1) |
3285 |
|
Firm's Relative Demand (Qtr = t-1) |
0.913242 |
|
Outputs |
|
|
|
|
|
|
|
|
|
Total
Industry Demand |
|
|
26221.9 |
|
|
|
|
|
|
Relative
Demand |
|
|
0.824804 |
|
|
|
|
|
|
Market
Share |
|
|
0.08248 |
|
|
|
|
|
|
Firm's
Estimated Demand |
|
|
2162.792 |
|
|
|
|
|
|
Average
Demand |
|
2622.19 |
|
As it can be observed
from the two Spreadsheet Models above, both the Firm’s Market Share and Firm’s
Estimated Demand is almost two times more in case of the Best Case Scenario
than the Worst Case Scenario. The facts indicating high influence of rival
companies’ decisions on the outcomes of the Firm’s Markets Share and its
Demand.
As we saw, decisions
made regarding the inputs ( Firm’s Price and
Advertising) while keeping Estimated Average Price and Average Advertising (363,
11000) yielded results as MS = 6.5%
And FD = 1714.9.Keeping
our Decisions constant we observe range of changes between worst and the best
case scenarios in Market Share 4.8% - 8.2% and Firm’s Demand 1268.7 – 2162.8.
The limitations of
the model: It is based on our Estimates regarding Industry’s Average Price and
Average Advertising. Any drastic change in competitor(s) pricing, advertising, or other
operational strategies my introduce unexpected complications for our Firm.
To Enhance the DSS model, we could introduce additional variables in order to predict the Firm’s Demand and its Market Share more accurately.