Following to article of "A Template for
Financial Section of a Business Plan" posted on link: http://emfps.blogspot.com/2016/08/a-template-for-financial-section-of.htm,
the purpose of this article is to
develop and improve previous template. So, I am willing to introduce you a new ratio
(S) extracted from some new theorems in mathematics in
which by using this ratio, we will be able to focus on only one assumption and
eliminate other assumptions for our analysis. In fact, by changing other
assumptions, this ratio (S) always stays the constant.
Developing of Previous Template
In
previous template, we had only one column for our assumptions. It means, by
changing a variable (assumption), the changes for all years are the same. For
instance, if we change the growth of sales from 4% t0 6%, the NPV shows us a
growth of 6% for year 1, year 2, year 3 and so on. Please see
below pic
In reality, there is not the same growth for all
years. Therefore, I added other cells on my spreadsheet where we will have a
set of assumptions for each year. This gives us the opportunity for better
analysis by using the Monte Carlo method. Please see blow pic:
The Improve of Previous Template
In new template, I added new assumption which is the Tax rate. In previous template, we have a big
problem when EBTI is negative because we have already added tax payment to net
income. In this case, there is the concept which is named NOL.
What is NOL? It is Net Operating Loss in which we can deduct our NOL
from the taxes we paid in prior years and get a refund, or we can apply it to
future years to lower our tax bill.
This
will help us to recover some net operating losses but usually not all.
The
concept of NOL is the complicated and you cannot easily claim your NOL. Some
people believe to deduct entire NOL from future taxes (Tax loss carry forwards)
or to add tax payment to net income when EBTI is negative (my previous
template). Let us see below example:
Assume
Company X has net
sales of $2,000,000 but expenses of $2,200,000.
Its net operating loss is $2,000,000 - $2,200,000 = -$200,000.
Company
X will probably not have to pay taxes that year, because it has negative taxable income. But let's assume that next year,
Company X makes more sales and records $700,000 of taxable
income. Company X pays a corporate tax rate of 40%.
Normally,
the company would need to pay $700,000 x 40% = $280,000 in taxes. But
because it had a tax loss carry forward from last year, it can apply last year's
loss to this year's tax bill, reducing it significantly (or even to $0,
depending on the jurisdiction Company X is in).
Let's
assume that Company X can apply the entire -$200,000 tax loss carryforward to
this year's tax bill. Instead of owing $700,000 x 40% = $280,000 in taxes,
Company X now owes only ($700,000 - $200,000) x 40% = $200,000 in taxes.
But other
people say another story as follows:
"It's
rare to see a company acquired for the purpose of NOLs today (at least through
a direct acquisition). This is because of 382 limitations on the usability of
NOLs in the case of a change in control of a company's equity rendering your
NOLs almost worthless on a present value basis. NOLs are currently limited to
3.98% of the value of the company during a change of control. This number is determined
by the IRS monthly and (along with the value of the company for 382 purposes)
will be fixed at the time of the change of control. Based on your business and
applicable tax laws, you may also have to distinguish between cash taxes and
accounting taxes. The actual formula for NOI after taxes is simply: NOI -
taxes. This is equivalent to (1-taxes) * NOI if your taxes are positive, but
should be just NOI since your taxes are zero if your NOI is negative."
Therefore, we
have the pessimistic and optimistic comments. Anyway, I choose the pessimistic
situation because here is a good opportunity for our
analysis as follows:
For obtaining
zero taxes on my spreadsheet when NOI is negative, I use a simple trick in
which I add one row under item of tax payment (Adjusted tax payment) by below
formula on all months and years of cash flow statement:
=IF (B36>0;
B36; 0)-B36
Please see below pics:
In this case,
if your tax payment is negative, adjusted tax payment will be positive where
the total sum is zero. But if your tax payment is positive, adjusted tax
payment will be zero.
Then, on
spreadsheet of Income statement, I add tax payment and adjusted tax payment.
But what is
good opportunity?
Above trick
gives us a good opportunity on our analysis. If we change the tax rate for each
year but NPV, IRR and also Enterprise value do not change, it means that the
combination of other assumptions presents net operating loss in its year. For
example: you can see when I change tax rate in year 4 and year 5, NPV does not
change. Therefore, we have net losses in year 4 and year 5. (Please see below
pics)
A New Ratio (S)
Now, let me introduce you a new ratio that if you apply it on each
assumption, by changing other assumptions this ratio always stay the constant.
Example:
I consider Cost of Capital as base of assumption to generate this
new ratio. Of course, we can choose any assumption as the base of calculation
this ratio.
- First, I use form a sensitivity analysis for the Cost of capital
and NPV. (see below pic)
- Then, the ratio of "S"
is equal to NPV 3 minus NPV 2 divide NPV 4 – NPV 1
S = (NPV3 – NPV 2) / (NPV 4 – NPV 1)
We can use from IRR and also Enterprise value instead NPV.
In below pic, you can see the ratio of "S" has been calculated
by using above formula for all assumptions:
Now, I change all assumptions except the Cost of Capital in below
pic:
As you can see, the ratio of "S" will stay the constant
for all changes of assumptions.
Indeed, what is application of the ratio of "S" for our
analysis?
For answering to above question, at the first we should familiar to
a new theorem of mathematics which is the rule of 0.333333….
Then we can start our analysis by using the Monte Carlo Method.
Of course, there is another ratio which is named ratio of "P". This ratio has also the property just like ratio of "S" where by changing all assumptions ratio "P" stays the constant. The formula for ratio "P" is as follows:
Of course, there is another ratio which is named ratio of "P". This ratio has also the property just like ratio of "S" where by changing all assumptions ratio "P" stays the constant. The formula for ratio "P" is as follows:
P = (NPV 2 – NPV1) / (NPV3 – NPV2) or
P = (EV2 – EV1) / (EV3 – EV2) or
P = (IRR2 – IRR1) / (IRR3 – IRR2)
Please see below pic:
Now, I change all assumptions and you can see ratio "S"
and "P" stay the constant. Please see below pic:
Are there other ratios which have the same property?
