In my last blog below, I wrote on the use of What-If
Analysis to reach conclusions about pricing by a small professional services
company.
Data generated by the Excel What-If Analysis Tool for the below
article can be nicely used to gain insights on marketing expenditures, when
used with decision tree analysis.
For example, assume that the company in year 1 has a $90 per
hour fee it charges, a demand of 800 hours, a fixed cost of $10,000 (rent,
utilities, office expenses, etc, but not including the salary), a variable cost
equal to 30% of revenues (which corresponds to US Census survey data that shows
that professional service companies have an average 70% gross profit margin
percentage), and breakeven profit (profit = $0). At these amounts, the company is able to pay
a $40,000 salary (as shown by What-If Analysis).
Now, the company wants to increase the $40,000 salary that
it can pay in year 2 by increasing the demand above 800 hours, while keeping
the $90 per hour fee the same. So the company
decides to increase marketing expenditures to try to increase demand. How much should the company increase the marketing
expenditures?
The use of the analytical tool, decision tree analysis, can help
answer this question. A major difficulty
in answering the question is predicting the increased demand with
certainty. To counter this uncertainty,
the decision tree analysis approach is to assign probabilities to possible
demand increases resulting from marketing.
Here is an explanation.
Assume that one outcome of the marketing program is that it is not very
successful. In using decision tree
analysis for this outcome, I have defined the outcome - not very successful -
as: 80% probability that there is no demand increase; 20% probability that
there is sufficient demand increase to increase the revenue (and therefore salary)
from $40,000 to $50,000, and there is no probability to increase the revenue
(salary) by $60,000 or $70,000. With
these probabilities, the expected revenue increase in year 2 can be computed to
be $2,000 [from $40,000 (year 1) to $42,000 in year 2]. This is computed by using this formula: 0.8
x $40,000 + 0.2 x $50,000. In other
words, there is an 80% probability that the revenue (salary) will remain at
$40,000 and a 20% probability that the revenue (salary) will go to $50,000, so
that the most likely result is $42,000 (assuming 80% and 20% are correct).
Now we can compute the likely revenue increases in three
other possible demand increase outcomes: moderate success in more demand; good
success in more demand; and very successful increase in more demand. The computations for each of these three
possible outcomes, done as above for the not very successful outcome, give
these increases from $40,000 in year 1 to: $46,250 (moderate success); $48,250
(good success), and $55,000 (very successful), in year 2. These increase values depend on the
probabilities that I have assigned to the possible outcomes.
These three possible demand outcomes are given percentage
probabilities for each of the four revenue (salary) outcomes: $40,000; 50,000;
$60,000; and $70,000. These percentages
are:
1. Moderate success:
$40,000 – 50%; $50,000 – 40%; $60,000 – 7.5%; $70,000 – 2.5%.
2. Good success:
$40,000 – 30%; $50,000 – 60%; $60,000 – 7.5%; $70,000 – 2.5%.
3. Very successful:
$40,000 – 10%; $50,000 – 40%; $60,000 – 40%; $70,000 – 10%. These percentages need to be selected by the
company.
The decision tree analysis is greatly assisted by first
doing the What-If Analysis, which will give the demand hours needed for various
increases in revenues (salaries).
Knowing these hours help in deciding on the probability that marketing
expenditures can reach these number of hours.
The demand levels for these revenues (salaries) are: $40,000 – 800 hours; $50,000 – 952 hours;
$60,000 – 1,111 hours; and $70,000 – 1,270 hours.
The results now give us some insight into how much the
company should increase the marketing expenditure. Probably less than $15,000 should be spent,
since it is unlikely that a revenue increase will be greater than $15,000 (that
is the amount of revenue increase predicted in a very successful marketing
campaign). A conservative decision for marketing
expenditures would be less than $2,000, since this is the amount of revenue
increase predicted for a not very successful marketing campaign. For year 2, the best expenditure is probably
between these two amounts. Year 2’s
results should help in better selecting the outcome probabilities in subsequent
years, if the marketing campaign continues.
The Mind Tools website (click
here) provides an explanation,
with an example, of decision tree analysis.
