A Modified Template for Financial Section of a Business Plan

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:

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?