integrated financial model Excel

kok12

casestudy1.pdf

Home >Business & Finance homework help >Accounting homework help >integrated financial model Excel

Mastering Financial Modeling: A Professional’s Guide to Building Financial Models in Excel. Eric Soubeiga

Text book . case study 3 . Phone factory revisited

Let us fast-forward the Phone Factory case study by two years. The plant has now been built,

and we are ready to start operations. The market for mobile phones has turned out to be much

larger. Let us fast-forward the Phone Factory case study by two years. The plant has now been

built, and we are ready to start operations. The market for mobile phones has turned out to be

much larger than we had initially anticipated (two years ago). As a result, the factory has been

built with a much bigger production capacity. Furthermore, demand for mobile phone handsets

is so strong that the starting selling price is 50 percent higher than we had initially forecast.

Sales are forecast to reach $6 million for the first year (about 30,000 handsets sold at an

average of $200).

With the factory now up and running, your manager has asked for a revised 10-year financial

model setting out projections for the profit and loss, balance sheet, and cash flow for the Phone

Factory. The model will be presented to the board of your company.

Question : We need to build an integrated financial model including the profit and loss statement, the balance sheet, and the cash flow statement. The model will take inputs, make calculations, and produce the required outputs in the form of a profit and loss statement, a

balance sheet, and a cash flow statement.

Here are the main model assumptions.

HIGH-LEVEL MODEL MAP

We need to build an integrated financial model including the profit and loss statement, the balance sheet, and the cash flow statement. The model will take inputs, make calculations, and

produce the required outputs in the form of a profit and loss statement, a balance sheet, and a

cash flow statement.

FIGURE 8.1 HIGH-LEVEL MODEL MAP

8.4 CREATING THE FRONT SHEET

Excel solution should look like this

FIGURE 8.2 FRONT COVER OR TITLE Note that the sheet does not show any gridlines. This was achieved by turning off the

Gridlines tick box within the View tab of the ribbon.

8.5 CREATING THE INPUTS & WORKINGS SHEET

We rename Sheet2 as “Inputs & Workings” and color it as yellow. Then we enter the

assumptions provided in Section 8.2 into the sheet as shown in Figures 8.3 and 8.4.

https://jigsaw.vitalsource.com/books/9780071808514/epub/ops/ch08.html#ch08fig3

https://jigsaw.vitalsource.com/books/9780071808514/epub/ops/ch08.html#ch08fig4

FIGURE 8.3 ASSUMPTIONS—PART 1

FIGURE 8.4 ASSUMPTIONS—PART 2

Set the width for columns A, B, and C to 2.57, and set the column D width to 32.29.

Freeze the pane at cell H3.

Enter the title “The Phone Factory” in cell B1, and enter the subtitle “Integrated Financial

Model ($m)” in cell C2.

Enter the timeline on row 2 as illustrated.

8.6 WRITING MODEL SPECIFICATIONS

Following the principle of modularization explained in Chapter 5, we consider the following modules: Profit and Loss, Debt, Fixed Assets, Balance Sheet, and Cash Flow.

8.6.1 Profit and Loss Module

Sales

Year 1 sales are given.

Year-over-year sales growth is given.

For each period of time t, calculate sales_t as sales_t–1 times (1 + sales growth_t).

Gross Profit

For each period of time t, calculate gross profit_t as gross profit margin_t times sales_t.

Earnings Before Interest, Taxes, Depreciation, and Amortization (EBITDA)

For each period of time t, calculate EBITDA_t as EBITDA margin_t times sales_t.

Depreciation

This is given.

Earnings Before Interest and Taxes (EBIT)

For each period of time t, calculate EBIT_t as EBITDA_t minus depreciation_t.

Interest

For each period of time t, the interest charge will be calculated in the Debt module (discussed in the next section).

Earnings Before Tax (EBT)

For each period of time t, calculate EBT_t as EBIT_t minus interest_t.

https://jigsaw.vitalsource.com/books/9780071808514/epub/ops/ch05.html#ch05

Tax

For each period of time t, calculate tax_t as tax rate times EBT_t if EBT_t is positive. Otherwise, tax_t = 0.

Profit After Tax (PAT)

For each period of time t, calculate PAT_t as EBT_t minus tax_t.

Dividend

For each period of time t, calculate dividend_t as dividend payout ratio times PAT_t if there is no debt outstanding. Otherwise, dividend_t = 0.

Net Profit

For each period of time t, calculate net profit_t as PAT_t minus dividend_t.

Retained Profit (Retained Earnings)

This will be calculated using a control account whereby retained profit carried forward_t =

retained profit brought forward_t plus net profit_t.

Retained profit brought forward_t = retained profit carried forward_t–1.

Retained profit brought forward_year 1 = retained profit carried forward in the opening

balance sheet.

8.6.2 Debt Module

As in Chapter 5, our Debt module will include a control account for “tracking” or monitoring the principal and a control account for tracking or monitoring the interest on the debt.

The debt control account will be calculated as follows:

Debt opening balance_t = debt closing balance_t–1, with debt opening balance_year 1 =

debt balance in opening balance sheet.

Debt drawdown_t = 0.

Debt repayment_t is given by the amortization schedule.

Debt closing balance_t = debt opening balance_t plus debt drawdown_t plus debt

repayment_t.

The interest control account will be calculated as follows:

Interest opening balance_t = interest closing balance_t–1, with interest opening

balance_year 1 = 0.

Interest charge_t = interest rate times (debt opening balance_t + debt drawdown_t).

https://jigsaw.vitalsource.com/books/9780071808514/epub/ops/ch05.html#ch05

Interest paid_t = – interest charge_t.

Interest closing balance_t = interest opening balance_t plus interest charge_t plus interest

paid_t.

8.6.3 Fixed Assets Module

As in Chapter 5, it is best practice to calculate fixed assets using a control account for tracking or monitoring the book value of fixed assets.

The fixed assets control account will be calculated as follows:

Fixed assets opening balance_t = fixed assets closing. balance_t–1, with fixed assets

opening balance_year 1 = fixed assets balance in opening balance sheet.

Capital expenditures (capex) increase the book value of fixed assets. Capex_t = capex as %

of sales_t times sales_t.

Depreciation decreases the book value of fixed assets. Depreciation_t is already given.

Fixed assets closing balance_t = fixed assets opening balance_t plus capex_t minus

depreciation_t.

8.6.4 Balance Sheet Module

Fixed assets closing balance_t is calculated in the fixed assets module just discussed.

Working capital_t = working capital as percent of sales_t times sales_t.

closing cash balance_t is calculated in the Cash Flow module (discussed later).

Closing debt balance_t is calculated in the Debt module (discussed earlier).

Net assets_t = fixed assets closing balance_t plus working capital_t plus closing cash

balance_t minus debt closing balance_t.

Share capital_t = share capital in opening balance sheet.

Retained profit carried forward_t is calculated in the Profit and Loss module (discussed

earlier).

Net worth_t = share capital_t plus retained profit carried forward_t.

Since net worth must equal net assets, let us add at the bottom of the balance sheet a

balance sheet check that checks whether the balance sheet balances in each period. To this

end, we include two additional lines as follows:

Balance_t = net assets_t minus net worth_t.

Check_t = if (ABS (Balance_t) <= 0.01, TRUE, FALSE), where Abs(x) is the absolute value of x,

a Microsoft Excel function returning the magnitude of a number regardless of its sign.

https://jigsaw.vitalsource.com/books/9780071808514/epub/ops/ch05.html#ch05

The way the formula has been expressed means that as long as the absolute value of the

difference between net assets_t and net worth_t is within 0.01, we consider such a

difference to be immaterial.

8.6.5 Cash Flow Module

As already discussed, there are three elements in an integrated financial model: the profit and

loss statement, the balance sheet, and the cash flow statement. With the profit and loss

statement and the balance sheet now established, the cash flow statement can be derived from

the other two statements in such a way that the resulting financial model is integrated. Indeed,

if the financial model is integrated, then a change in any element of the financial model will

result in a change elsewhere in the financial model. For example, an increase in working capital

in the balance sheet will result in a cash outflow in the cash flow statement, resulting in a

reduction in the closing cash balance. As another example, a decrease in net profit (in the profit

and loss statement) will result in a decrease in net worth (in the balance sheet). Another

example: debt repayment (a cash outflow in the cash flow statement) will result in a reduced

debt balance in the balance sheet.

Deriving the cash flow statement based on the profit and loss statement and the balance

sheet is known as the indirect method of calculating the cash flow statement. This is in contrast to the direct method of calculating the cash flow statement, in which a summary of receipts and payments is given. The latter is more useful in the context of a financial controller monitoring

cash receipts and cash payments for a given business. The former is more useful for building an

integrated financial model.

In this book, we will use the indirect method for calculating cash flows. The indirect cash flow

statement starts with EBITDA as the top line and flows down the waterfall to get to net cash

flow as follows:

EBITDA_t. This is calculated in the profit and loss module.

Movement in working capital_t. This equals working capital_t–1 minus working capital_t,

or working capital at the start of the year minus working capital at the end of the year.

Operating cash flow_t. This equals EBITDA_t plus movement in working capital_t.

Capex_t. This is calculated in the Fixed Asset module.

Cash flow before taxation and financing_t. This equals operating cash flow_t minus

capex_t.

Tax_t. This is calculated in the Profit and Loss module.

Cash flow available for debt service_t. This equals cash flow before taxation and

financing_t minus tax_t.

Interest paid_t. This is calculated in the Debt module.

Debt repayment_t. This is calculated in the Debt module.

Cash flow available for shareholders_t. This equals cash flow available for debt service_t

minus interest paid_t minus debt repayment_t.

Dividend_t. This is calculated in the Profit and Loss module.

Net cash flow_t. This equals cash flow available for shareholders_t minus dividend_t.

We also need to include a control account calculating the closing cash balance_t.

The closing cash balance_t equals the opening cash balance_t plus net cash flow_t.

The opening cash balance_t equals the closing cash balance_t–1, with the opening cash

balance_year 1 given in the opening balance sheet.

8.7 MODEL CALCULATIONS MAP

As in Chapter 4, a high-level map of the model calculations can be useful for illustrating how the different modules relate to one another. Figure 8.5 is an illustration of the model calculations map.

FIGURE 8.5 MODEL CALCULATIONS MAP

8.8 MODEL ASSUMPTIONS

It is useful to state all the key assumptions made in the model. These include the following:

Interest charge is paid the same year.

Tax charge is paid the same year.

Dividend charge is paid the same year.

There are no increases or reductions in share capital.

There is no disposal of assets.

There are no intangibles (and therefore no amortization thereof).

https://jigsaw.vitalsource.com/books/9780071808514/epub/ops/ch04.html#ch04

https://jigsaw.vitalsource.com/books/9780071808514/epub/ops/ch08.html#ch08fig5

8.9 ADDING THE CALCULATION MODULES TO THE INPUTS & WORKINGS

SHEET

The Inputs & Workings sheet contains the inputs already. Having gone through how the model calculations would work, we can now add the different calculation modules to the Inputs &

Workings sheet.

8.9.1 Profit and Loss Module

Let’s create the Profit and Loss section right below the Assumptions section in the Inputs &

Calculations sheet, say on row 36. We can now add the different lines of the Profit and Loss

statement following the specifications described earlier.

Sales

We can write in cell I38 (column I is the column for the first period, or Year 1) the formula

“=IF(I$2=1,I6,H38*(1+I7))”, which basically means that if we are in Year 1, then sales =

sales_ year 1 (which is $6 million); otherwise, sales = sales from the previous year times

(1 + sales growth % for the current year).

Gross profit

We can write in cell I39 the formula “=I$38*I8”, which is saying that gross profit equals

sales times gross profit margin.

EBITDA

We can write in cell I40 the formula “=I$38*I9”, which says that EBITDA equals sales times

the EBITDA margin.

EBITDA growth

We can write in cell I41 the formula “=IF(I$2=1,””,I40/H40–1)”, which calculates the

year-over-year annual growth from Year 2 onward. The IF formula ensures that if we are

in Year 1, then the year-over-year growth is not applicable, and so the answer is “”,

which is Excel’s formula for an empty cell.

Depreciation

We can write in cell I42 the formula “= –I13”, which picks up the opposite of the

corresponding value of depreciation from row 13, hence the negative sign. This reminds

us that whereas sales and gross profit are positive in that they increase the profitability

of the project, depreciation goes in the opposite direction (that is, it is a cost to the

project).

EBIT

We can write in cell I43 the formula “=I40+I42”, which calculates the profit (or earnings)

before interest and taxes as EBITDA less depreciation. Note that EBIT is defined as

EBITDA less depreciation and amortization. Since there is no amortization, EBIT is simply

EBITDA less depreciation.

Interest

As illustrated in the model calculation map in Figure 8.5, the interest charge is calculated in the Debt module. So we skip the interest line for now.

EBT

We can write in cell I45 the formula “=I43+I44”, which calculates EBT as EBIT less interest

(note that interest will be negative, as it reduces profit—that is, it is a cost to the

business).

Taxes

We can write in cell I46 the formula “=–IF(I45>0,$F$14*I45,0)”, which calculates the taxes

as tax rate times EBT if EBT is positive. In other words, taxes are incurred only if the

project recorded a profit.

PAT

We can write in cell I47 the formula “=I46+I45”, which calculates PAT as EBT

less taxes.

Dividend

We can skip the formula for now, as the dividend is applicable only after the

debt has been fully repaid. We will update the dividend line once the Debt module has

been completed.

Net profit

https://jigsaw.vitalsource.com/books/9780071808514/epub/ops/ch08.html#ch08fig5

We can write in cell I49 the formula “=I47+I48”, which calculates net profit as

PAT less dividend.

Retained profit b/f

We can write in cell I50 the formula “=IF(I$2=1,$F$33,H51)”, which means that

if we are in Year 1, then the retained profit brought forward equals the retained profit

from the opening balance sheet. Otherwise, retained profit brought forward equals the

retained profit carried forward at the end of the previous period.

Retained profit c/f

We can write in cell I51 the formula “=I50+I49”, which calculates the retained

profit carried forward as the retained profit brought forward plus net profit.

Copy cells I38:I51 and paste them into the block I38:R51. The Profit and Loss module is

illustrated in Figure 8.6.

FIGURE 8.6 PROFIT AND LOSS MODULE Note that the profit and loss figures in Figure 8.6 are not yet final, as the interest and

dividend lines have not been completed.

8.9.2 Debt Module

Let’s create the Debt module right below the Profit and Loss module in the Inputs & Workings

sheet, say on row 53. We can now run a control account for the principal and a control account

for the interest in line with the specifications described earlier.

Debt Control Account

Debt balance b/f

https://jigsaw.vitalsource.com/books/9780071808514/epub/ops/ch08.html#ch08fig6

In cell I56, we can write the formula “=IF(I$2=1,–$F$29,H59)”, which basically

means that if we are in Year 1, then the debt brought forward is equal to the debt

amount in the opening balance sheet (which is in cell F29). Otherwise, debt brought

forward is equal to debt carried forward from the previous year.

Debt drawdown

In cell I57, we can write the formula “=I19”, which basically picks up the debt

drawdown amount from row 19.

Debt repayment

In cell I58, we can write the formula “=–I18”, which basically picks up the

opposite of the entry in row 18 (debt repaid). Note the importance of including the

negative sign for debt repayment. Whereas debt drawdown increases the debt balance

outstanding, debt repaid will reduce the debt balance outstanding; hence the need to

use a negative sign.

Debt balance c/f

In cell I59, we can write the formula “=SUM (I56:I58)”, which basically

calculates the debt carried forward as debt brought forward plus debt drawdown

less debt repaid.

Interest Control Account

Interest b/f

In cell I62, we can write the formula “=H65”, which basically means that

interest brought forward equals interest carried forward from the previous year.

Interest charge

In cell I63, we can write the formula “=SUM(I56:I57)*$F$17”, which calculates

the interest charge as the sum of the debt balance brought forward plus the debt

drawdown times the annual interest rate (which is in cell F17).

Interest paid

In cell I64, we can write the formula “=–I63”, which reflects our assumption

that interest charged (or incurred) is paid in the same year. Note the importance of

including the negative sign for interest paid. Like debt repayment in the debt control

account, the effect of interest paid on the interest balance is the opposite of that of the

interest charge.

Interest c/f

In cell I65, we can write the formula “=SUM(I62:I64)”, which basically calculates

interest carried forward as interest brought forward plus interest charge less interest

paid.

Copy cells I56:I65 and paste them into the block I56:R65.

8.9.3 Fixed Assets Module

Let’s create the Fixed Assets module right below the Debt module, say on row 67. We can now

run a control account for the fixed assets in line with the specifications described earlier.

Fixed Assets Control Account

Fixed assets balance b/f

In cell I68, we can write the formula “=IF(I$2=1,$F$26,H71)”, which basically

means that if we are in Year 1, then fixed assets brought forward is equal to the fixed

assets amount in the opening balance sheet (which is in cell F26). Otherwise, fixed assets

brought forward equals fixed assets carried forward from the previous year.

Capex

In cell I69, we can write the formula “=I10*I38”, which basically calculates

capex as capex as a percent of sales times the sales figure.

Depreciation

In cell I70, we can write the formula “=I42”, which basically picks up the

depreciation amount from the Profit and Loss module. Note the importance of including

the negative sign for depreciation. Whereas capex increases the book value (value on the

balance sheet) of the fixed assets, depreciation has the opposite effect on their book

value. Because the content of cell I42 is already a negative number, there is no need to

add another negative sign to it (if we did, we would count depreciation as positive for the

book value of fixed assets, which would be incorrect).

Fixed assets balance c/f

In cell I71, we can write the formula “=SUM(I68:I70)”, which basically calculates

fixed assets carried forward as being equal to fixed assets brought forward plus capex

less depreciation.

Copy cells I68:I71 and paste them into the block I68:R71. The Debt and Fixed Assets modules

are illustrated in Figure 8.7.

https://jigsaw.vitalsource.com/books/9780071808514/epub/ops/ch08.html#ch08fig7

FIGURE 8.7 DEBT AND FIXED ASSETS MODULES

Note:

Now that the Debt module has been completed, we can go back to the Profit and Loss module

and complete the interest and dividend lines as follows:

Interest

In cell I44, we can write the formula “=–I63”, which picks up the interest charge

from the Debt module. Interest is a cost in the Profit and Loss module; hence the

negative sign.

Dividend

In cell I48, we can write the formula “=–IF(I56>0,0,I47*$F$22)”, which means

that the dividend is 0 if the debt balance is positive. Otherwise, the dividend equals PAT

times the dividend payout. Just like interest, the dividend is a cost in the Profit and Loss

module; hence the negative sign.

Copy cell I44 and paste it into the block I44:R44 for interest. Copy cell I48 and

paste it into the block I48:R48 for dividend.

This portion of the revised Profit and Loss module is illustrated in Figure 8.8.

FIGURE 8.8 PORTION OF THE REVISED PROFIT AND LOSS MODULE

https://jigsaw.vitalsource.com/books/9780071808514/epub/ops/ch08.html#ch08fig8

8.9.4 Balance Sheet Module

Let’s create the Balance Sheet module right below the Debt and Fixed Assets modules in the

Inputs & Workings sheet, say on row 73. We can now add the different lines of the balance

sheet following the specifications described earlier.

Fixed assets

In cell I75, we can write the formula “=I71”, which picks up the book value of

fixed assets from the Fixed Assets module.

Working capital

In cell I76, we can write the formula “=I11*I38”, which calculates working

capital as working capital as a percentage of sales times sales.

Cash

We skip cash for now, as we have yet to complete the Cash Flow module.

Debt

In cell I78, we can write the formula “= –I59”, which picks up the opposite of

the debt balance carried forward from the Debt module. Note that debt is shown as

negative, as it is a liability, whereas fixed assets is shown as positive, as it is an asset.

Net assets

In cell I79, we can write the formula “=SUM(I75:I78)”, which calculates net

assets as equal to fixed assets plus working capital plus cash less debt.

Share capital

In cell I81, we can write the formula “=$F$32”, which picks up share capital

from the opening balance sheet.

Retained profit c/f

In cell I82, we can write the formula “=I51”, which picks up retained profit

(retained earnings) carried forward from the Profit and Loss module.

Net worth

In cell I83, we can write the formula “=SUM(I81:I82)”, which calculates net

worth as equal to share capital plus retained profit carried forward.

Balance

In cell I85, we can write the formula “=I79–I83”, which calculates the difference

between net assets and net worth. This is for the purpose of checking whether the

balance sheet balances.

Balance sheet error check

In cell I86, we can write the formula “=IF(ABS(I85)<=0.01, TRUE,FALSE)”, which

shows TRUE if the absolute value of the difference between net assets and net worth is

less than or equal to 0.01. Otherwise, the formula shows FALSE.

In addition to calculating the balance sheet check in cell I86, we also run a

conditional formatting in cell I86 such that the cell takes on a green background when

the value of the cell is TRUE. Otherwise, the cell’s background is red. An example of

conditional formatting is given in Chapter 6 (Section 6.4).

https://jigsaw.vitalsource.com/books/9780071808514/epub/ops/ch06.html#ch06

Copy cells I75:I86 and paste them into the block I75:R86. The Balance Sheet module is

illustrated in Figure 8.9.

FIGURE 8.9 BALANCE SHEET MODULE

Note:

The balance sheet figures are not yet final, as the cash line has not yet been completed.

8.9.5 Cash Flow Module

Let’s create the Cash Flow module right below the Balance Sheet module in the Inputs &

Workings sheet, say on row 88. We can now add the different lines of the Cash Flow module

following the specifications described earlier.

EBITDA

In cell I90, we can write the formula “=I40”, which picks up the EBITDA figure

from the Profit and Loss module.

Movement in working capital

In cell I91, we can write the formula “=H76–I76”, which calculates the

movement in working capital as working capital at the end of the previous period (H76)

less working capital at the end of the current period (I76).

Operating cash flow

In cell I92, we can write the formula “=I91+I90”, which calculates the operating

cash flow as EBITDA (I90) plus movement in working capital (I91).

Capex

https://jigsaw.vitalsource.com/books/9780071808514/epub/ops/ch08.html#ch08fig9

In cell I93, we can write the formula “= –I69”, which picks up capex as the

opposite of the capex figure in the Fixed Assets module.

Cash flow before taxation and financing

In cell I94, we can write the formula “=I93+I92”, which calculates cash flow

before taxation and financing as operating cash flow plus capex. Note that since capex is

shown in the Cash Flow module as a negative number (that is, a cash outflow), the capex

figure is added to (hence the + sign), rather than deducted from, operating cash flow.

Taxes

In cell I95, we can write the formula “=I46”, which picks up taxes from the Profit

and Loss module. We assume that taxes are paid the year in which they are incurred.

Cash flow available for debt service

In cell I96, we can write the formula “=I95+I94”, which calculates cash flow

available for debt service as cash flow before taxation and financing plus taxes.

Interest

In cell I97, we can write the formula “=I64”, which picks up interest from the

Debt module.

Debt repayment

In cell I98, we can write the formula “=I58”, which picks up the amount of debt

repaid from the Debt module.

Cash flow available for shareholders

In cell I99, we can write the formula “=SUM(I96:I98)”, which calculates cash

flow available for shareholders as cash flow available for debt service plus interest plus

debt repayment. Note that since debt repayment and interest are shown as negative

numbers (cash outflows), they are added to (hence the + sign), rather than deducted

from, cash flow available for debt service.

Dividend

In cell I100, we can write the formula “=I48”, which picks up the dividend from

the Profit and Loss module.

Net cash flow

In cell I101, we can write the formula “=I100+I99”, which calculates net cash

flow as cash flow available for shareholders plus dividend. Note that since the dividend,

when it is not zero, is shown as a negative number (a cash outflow), it is added to (hence

the + sign), rather than deducted from, cash flow available for shareholders.

Cash balance b/f

In cell I102, we can write the formula “=IF(I$2=1,$F$28,H103)”, which calculates

cash balance brought forward as the cash balance in the opening balance sheet (F28) if

we are in period 1. Otherwise, cash balance brought forward is equal to the cash balance

carried forward in the previous period.

Cash balance c/f

In cell I103, we can write the formula “=I102+I101”, which calculates the cash

balance carried forward as the cash balance brought forward plus net cash flow.

Copy cells I90:I103 and paste them into the block I90:R103. The Cash Flow module is

illustrated in Figure 8.10.

FIGURE 8.10 CASH FLOW MODULE

Note:

With the Cash Flow module now completed, we can go back to the Balance Sheet module and

complete the cash line as follows:

Cash

In cell I77, we can write the formula “=I103”, which picks up the cash balance

carried forward from the Cash Flow module.

Copy cell I77 and paste it into the block I77:R77.

Overall balance sheet check

Since we are in the Balance Sheet module, let us create an overall balance

sheet check in the frozen pane area of the Inputs & Workings sheet, say in row 1.

Write “Balance sheet check” in cell P1. Copy any cell in the block I86:R86 and paste it

into cell R1. This copies the conditional formatting in the block I86:R86 as well as the

formula in the selected cell in the block I86:R86. Then write in cell R1 the formula

“=AND(I86:R86)”, which calculates the Boolean value of applying the AND function on cells

I86 through R86. If all these cells have a value of TRUE, then AND(I86:R86) will also be

TRUE. However, if at least one of these cells has a value of FALSE, then AND(I86:R86) will

be FALSE.

Having the balance sheet check located in the frozen pane area means that we have a

permanent display of the balance sheet status.

The revised Balance Sheet module is shown in Figure 8.11.

https://jigsaw.vitalsource.com/books/9780071808514/epub/ops/ch08.html#ch08fig10

https://jigsaw.vitalsource.com/books/9780071808514/epub/ops/ch08.html#ch08fig11

FIGURE 8.11 REVISED BALANCE SHEET

Notes:

The balance sheet balances can be seen in the block I86:R86.

The balance sheet check also shows this in cell R1.

8.10 CREATING THE OUTPUTS SHEET

Having completed all the necessary calculations, we are now ready to bring together the key

results from our calculations into the Outputs sheet.

The key outputs are the profit and loss statement, the balance sheet, and the cash flow

statement.

Copy the “Inputs & Workings” sheet, rename it Outputs, and color it green. This

way, the Outputs sheet initially has the same content as the Inputs & Workings sheet.

We will then delete the elements of the Inputs & Workings sheet that are not going to be

included in the Outputs sheet.

Since the main outputs are the profit and loss statement, the balance sheet,

and the cash flow statement, we will link the Profit and Loss, Balance Sheet, and Cash

Flow modules in the Outputs sheet to the corresponding Profit and Loss, Balance Sheet,

and Cash Flow modules in the Inputs & Workings sheet. To do the linking, we can select

the first cell in the Profit and Loss module, cell I38, in the Outputs sheet and write in it

the formula “=‘Inputs & Workings’!I38”.

Link the Profit and Loss module in the Outputs sheet to the corresponding

Profit and Loss module in the Inputs & Workings sheet. To do this, copy cell I38 and paste

its formula into the block I38:R51. This can be done using the following keyboard short

cut:

Select cell I38 and type CTRL+C (that is, press and hold the CTRL key, then,

with CTRL pressed down, type C). This copies cell I38.

Select block I38:R51 and type ALT, then E, then S. This pops the Paste

Special window, which is shown in Figure 8.12.

FIGURE 8.12 PASTE SPECIAL FORMULA

Select the Formulas option, circled in Figure 8.12, using the down arrow on the keyboard.

Confirm the selected option by typing the ENTER or RETURN key or clicking

the OK button.

Link the Balance Sheet module in the Outputs sheet to the corresponding

Balance Sheet module in the Inputs & Workings sheet. To do this, copy cell I75 and paste

its formula into block I75:R79, block I81:R83, and block I85:R86.

https://jigsaw.vitalsource.com/books/9780071808514/epub/ops/ch08.html#ch08fig12

To link the Cash Flow module in the Outputs sheet to the corresponding Cash

Flow module in the Inputs & Workings sheet, paste the formula (note that the formula in

cell I75 is still in Copy) into the block I90:R103.

Select rows 3 to 35 and delete the entire block. This now brings the Profit and

Loss module right to the top at row 3.

Delete the Debt and Fixed Assets modules by selecting and deleting rows 20 to

39. This now brings the Balance Sheet module to row 20 with the Cash Flow module

starting in row 35.

Let us make a few minor adjustments to make the Outputs sheet more user friendly.

Add an “end of sheet” divider at the bottom of the Cash Flow module. To do

this, copy row 35 (the row with the Cash Flow title) and insert it in row 52. Then rename

the title as End of Sheet.

We also include a sales growth line, a gross profit margin line, and an EBITDA

margin line in the profit and loss section of the Outputs sheet. To this end, we need to

insert a row below sales, a row below gross profit, and a row below EBITDA, which we

name “Sales growth”, “Gross profit margin”, and “EBITDA margin”, respectively.

For sales growth, in cell I6, we write the formula “=IF(I2=1,””,‘Inputs &

Workings’!I7)”, which copies the corresponding sales growth figure from the Inputs &

Workings sheet.

Furthermore, we write in cell S6 formula “=(R5/I5)^(1/R2)–1”, which calculates

the compounded annual growth rate (CAGR) for sales from Year 1 to Year 10.

Similarly, for gross profit margin, we write in cell I8 the formula “=‘Inputs &

Workings’!I8”.

And similarly, for EBITDA margin, we write in cell I10 the formula “=‘Inputs &

Workings’!I9”.

Furthermore, we write in cell S11 the formula “=(R9/I9)^(1/R2)–1” to calculate

the CAGR of EBITDA from Year 1 to Year 10.

Copy cell I6 and paste it into the block I6:R6. Copy cell I8 and paste it into the block I8:R8.

Copy cell I10 and paste it into the block I10:R10.

The resulting Outputs sheet is shown in Figures 8.13 through 8.15. First we show the profit and loss statement (Figure 8.13).

FIGURE 8.13 PROFIT AND LOSS OUTPUT

FIGURE 8.14 BALANCE SHEET OUTPUT

https://jigsaw.vitalsource.com/books/9780071808514/epub/ops/ch08.html#ch08fig13

https://jigsaw.vitalsource.com/books/9780071808514/epub/ops/ch08.html#ch08fig15

https://jigsaw.vitalsource.com/books/9780071808514/epub/ops/ch08.html#ch08fig13

FIGURE 8.15 CASH FLOW OUTPUT

Next we show the balance sheet (Figure 8.14).

Finally, we show the cash flow statement (Figure 8.15).

8.11 ANALYSIS

8.11.1 Profit and Loss

The projections reflect a business that is growing fast, with sales projected to

grow at a compounded annual growth rate of 32 percent from Year 1 to Year 10.

The business is projected to be profitable as well, with an average EBITDA

margin of 35 percent over the projection period (that is, the EBITDA margins for Year 1

through to Year 10 average at 35 percent).

EBITDA is projected to grow at a CAGR of 27 percent over the projection period.

8.11.2 Balance Sheet

Debt is repaid within five years.

Net worth builds up well over the projected period to reach $89.3 million at the

end of Year 10.

https://jigsaw.vitalsource.com/books/9780071808514/epub/ops/ch08.html#ch08fig14

https://jigsaw.vitalsource.com/books/9780071808514/epub/ops/ch08.html#ch08fig15

8.11.3 Cash Flow

The business is projected to be very cash generative. Total operating cash flow

generated over the 10-year period is projected to amount to $160.7 million (that is, the

sum of operating cash flows from Year 1 through to Year 10 equals $160.7 million).

Free cash flow available for shareholders totals $72.2 million over the 10-year

period. However, please note that there is insufficient cash generation in Year 1 to meet

all cash expenditures that year, resulting in –$0.2 million. This cash shortfall of $0.2

million is met by drawing down from the opening cash balance of $1 million. As a result,

the closing cash balance at the end of Year 1 is $0.8 million.

The closing cash balance at the end of Year 10 is projected to reach $37.2

million.

than we had initially anticipated (two years ago). As a result, the factory has been built with a

much bigger production capacity. Furthermore, demand for mobile phone handsets is so strong

that the starting selling price is 50 percent higher than we had initially forecast. Sales are

forecast to reach $6 million for the first year (about 30,000 handsets sold at an average of

$200).

With the factory now up and running, your manager has asked for a revised 10-year financial

model setting out projections for the profit and loss, balance sheet, and cash flow for the Phone

Factory. The model will be presented to the board of your company.

In this chapter, you will learn to build an integrated financial model, which is basically a financial model in which the profit and loss, balance sheet, and cash flow statements are

interlinked in a consistent manner. Here are the main model assumptions.

8.2 MODEL ASSUMPTIONS

8.2.1 Operational Assumptions

The operational assumptions are given in Table 8.1.

https://jigsaw.vitalsource.com/books/9780071808514/epub/ops/ch08.html#ch08tab1

TABLE 8.1 OPERATIONAL ASSUMPTIONS

Sales growth %. The sales growth percentage is quite high, particularly in the first few years. This reflects the strong unmet demand for mobile phone handsets in the

target overseas market. Sales are projected to grow throughout the projected period,

although at a gradually slower pace, stabilizing at 8 percent per annum from Year 8

onward.

Gross profit %. This is assumed to be 80 percent throughout the projected period.

EBITDA %. This is assumed to be 45 percent during Years 1 and 2 and gradually comes down to 30 percent from Year 5 onward.

Capex as % of sales. Once the factory has been built, capital expenditure requirements are minimal. They are projected at 5 percent of sales throughout the

period and reflect the need to hire more staff as the business grows.

Working capital as % of sales. Working capital here includes stock (inventory, in American usage), trade debtors (accounts receivable), and trade creditors (accounts

payable). Working capital is projected at 20 percent of sales throughout the period and

reflects the fact that the business will require a larger amount of stock, trade debtors,

and trade creditors as the business grows.

8.2.2 Opening Balance Sheet (in Millions of Dollars)

The opening balance sheet, shown in Table 8.2, is the balance sheet at the end of the two years of construction (in other words, the balance sheet at the start of operations).

TABLE 8.2

Fixed assets. Fixed assets include property, plant, and equipment (PPE) and are valued at cost, $8 million, which is how much it cost to build the factory.

Working capital. The opening working capital is assumed to be 0, reflecting the fact that at the start of operations, there is no stock (inventory), trade debtors (accounts

receivable), or trade creditors (accounts payable).

Cash. The opening cash balance is assumed to be $1 million; in other words, the business begins operations with $1 million of cash available to fund working capital (for

example, to purchase inventory).

Debt. The factory has been built at a cost of $8 million and also has $1 million of cash, creating total capital of $9 million. Of the $9 million of capital, $6 million is debt.

Share capital. Of the $9 million of capital, $3 million is equity. The factory has total capital of $9 million. Since $6 million of the $9 million of capital is debt, $3 million is

equity.

https://jigsaw.vitalsource.com/books/9780071808514/epub/ops/ch08.html#ch08tab2

Retained profit (retained earnings, in American usage). This is assumed to be 0, reflecting the fact that the business has not begun operating to generate any profit.

Net assets. This is calculated as the sum of fixed assets, working capital, cash, and debt and equals $3 million.

Net worth. This is calculated as the sum of share capital and retained profit and equals $3 million.

Net assets must be equal to net worth. This is the case here, and therefore the

balance sheet balances.

Balance Sheet Format

The balance sheet can be presented in a variety of ways. In this book, the balance sheet is laid

out as shown on the left-hand side of Table 8.3. The right-hand side of the table shows a balance sheet layout that is often encountered in the United States. The difference between

the two formats occurs in the upper part of the balance sheet. In the format used throughout

this book, the upper part of the balance sheet gives long-term assets, then current assets, then

cash, whereas the format commonly used in the United States starts with cash, followed by

current assets and then long-term assets.

TABLE 8.3

The format used in this book lends itself to calculating the balance sheet as net assets being

funded by the shareholders’ funds, or, put differently, net assets = net worth. The format often

encountered in the United States lends itself to calculating the balance sheet as assets =

liabilities.

https://jigsaw.vitalsource.com/books/9780071808514/epub/ops/ch08.html#ch08tab3

Note that mathematically the equation assets = liabilities is equivalent to the equation net

assets = equity. Indeed, assets = liabilities means cash + current assets + long-term assets =

current liabilities + long-term liabilities (excluding equity) + equity, which can be rewritten as

cash + current assets + long-term assets – current liabilities – long-term liabilities (excluding

equity) = equity, which means that net assets = equity. Therefore, while the layout is different,

the balance sheet remains the same.

In the balance sheet example shown in Table 8.2, we do not show current assets and current liabilities separately, but instead show working capital.

8.2.3 Debt Assumptions

Interest rate: 6%

TABLE 8.4 AMORTIZATION SCHEDULE

Amortization schedule. The assumed amortization schedule is such that the loan is fully repaid in six years (see Table 8.4). However, the repayment is not in equal amounts. Instead, the amount repaid in the earlier years is small, with a bigger

proportion of the loan being repaid in later years. This is often referred to as a

back-ended amortization schedule, and it gives the business more breathing space in earlier years when cash generation is low.

Interest rate. This is assumed to be 6 percent per annum.

8.2.4 Dividend Assumptions

Dividend payout as a percentage of profit after tax (PAT) is 50 percent.

https://jigsaw.vitalsource.com/books/9780071808514/epub/ops/ch08.html#ch08tab2

https://jigsaw.vitalsource.com/books/9780071808514/epub/ops/ch08.html#ch08tab4

Dividends are payable once debt has been fully repaid.

Dividend payout. This is assumed to be 50 percent of profit after tax (PAT). In other words, half of the profit after tax is paid to the shareholders (who are the owners

of the business), with the other half being reinvested in the business.

Timing of dividend. The dividend will not be paid until the debt has been fully repaid. This reflects the fact that the debt provider wants to ensure that the free cash

generated by the business is used first and foremost to repay the debt.

8.2.5 Other Assumptions

Tax rate: 30%

Depreciation: straight-line depreciation, assuming that the factory has a useful

economic life of 20 years with a nil residual value.

Tax rate. This is assumed to be 30 percent of earnings before tax (EBT).

Depreciation. The useful economic life of the factory is assumed to be 20 years with no residual value. Depreciation is assumed to be on a straight-line basis. Thus the

book value of the factory is decreased by 1/20 of its original cost each year, or $8/20 (see

Table 8.5).

TABLE 8.5 OTHER ASSUMPTIONS

https://jigsaw.vitalsource.com/books/9780071808514/epub/ops/ch08.html#ch08tab5

8.3 HIGH-LEVEL MODEL MAP

As in Case Study 1 (Chapters 4 and 5), let us once again resist the temptation to jump straight to building the model in Excel. Instead, let us think through what is required here and plan the

model accordingly.

We need to build an integrated financial model including the profit and loss statement, the balance sheet, and the cash flow statement. The model will take inputs, make calculations, and

produce the required outputs in the form of a profit and loss statement, a balance sheet, and a

cash flow statement.

The map in Figure 8.1 describes the model.

FIGURE 8.1 HIGH-LEVEL MODEL MAP The model will be relatively simple, and so based on the map in Figure 8.1, we can envisage a

Microsoft Excel file with two sheets: Inputs & Workings, and Outputs.

8.4 CREATING THE FRONT SHEET

Let us create a new workbook. In Sheet1, we create a front sheet similar to that in Chapter 4 (see Figure 8.2).

FIGURE 8.2 FRONT COVER OR TITLE Note that the sheet does not show any gridlines. This was achieved by turning off the

Gridlines tick box within the View tab of the ribbon.

8.5 CREATING THE INPUTS & WORKINGS SHEET

https://jigsaw.vitalsource.com/books/9780071808514/epub/ops/ch04.html#ch04

https://jigsaw.vitalsource.com/books/9780071808514/epub/ops/ch05.html#ch05

https://jigsaw.vitalsource.com/books/9780071808514/epub/ops/ch08.html#ch08fig1

https://jigsaw.vitalsource.com/books/9780071808514/epub/ops/ch04.html#ch04

https://jigsaw.vitalsource.com/books/9780071808514/epub/ops/ch08.html#ch08fig2

We rename Sheet2 as “Inputs & Workings” and color it as yellow. Then we enter the

assumptions provided in Section 8.2 into the sheet as shown in Figures 8.3 and 8.4.

FIGURE 8.3 ASSUMPTIONS—PART 1

FIGURE 8.4 ASSUMPTIONS—PART 2

Set the width for columns A, B, and C to 2.57, and set the column D width to

32.29.

Freeze the pane at cell H3.

Enter the title “The Phone Factory” in cell B1, and enter the subtitle “Integrated

Financial Model ($m)” in cell C2.

Enter the timeline on row 2 as illustrated.

8.6 WRITING MODEL SPECIFICATIONS

Following the principle of modularization explained in Chapter 5, we consider the following modules: Profit and Loss, Debt, Fixed Assets, Balance Sheet, and Cash Flow.

8.6.1 Profit and Loss Module

https://jigsaw.vitalsource.com/books/9780071808514/epub/ops/ch08.html#ch08fig3

https://jigsaw.vitalsource.com/books/9780071808514/epub/ops/ch08.html#ch08fig4

https://jigsaw.vitalsource.com/books/9780071808514/epub/ops/ch05.html#ch05

Sales

Year 1 sales are given.

Year-over-year sales growth is given.

For each period of time t, calculate sales_t as sales_t–1 times (1 + sales growth_t).

Gross Profit

For each period of time t, calculate gross profit_t as gross profit margin_t times sales_t.

Earnings Before Interest, Taxes, Depreciation, and Amortization (EBITDA)

For each period of time t, calculate EBITDA_t as EBITDA margin_t times sales_t.

Depreciation

This is given.

Earnings Before Interest and Taxes (EBIT)

For each period of time t, calculate EBIT_t as EBITDA_t minus depreciation_t.

Interest

For each period of time t, the interest charge will be calculated in the Debt module (discussed in the next section).

Earnings Before Tax (EBT)

For each period of time t, calculate EBT_t as EBIT_t minus interest_t.

Tax

For each period of time t, calculate tax_t as tax rate times EBT_t if EBT_t is positive. Otherwise, tax_t = 0.

Profit After Tax (PAT)

For each period of time t, calculate PAT_t as EBT_t minus tax_t.

Dividend

For each period of time t, calculate dividend_t as dividend payout ratio times PAT_t if there is no debt outstanding. Otherwise, dividend_t = 0.

Net Profit

For each period of time t, calculate net profit_t as PAT_t minus dividend_t.

Retained Profit (Retained Earnings)

This will be calculated using a control account whereby retained profit carried

forward_t = retained profit brought forward_t plus net profit_t.

Retained profit brought forward_t = retained profit carried forward_t–1.

Retained profit brought forward_year 1 = retained profit carried forward in the

opening balance sheet.

8.6.2 Debt Module

As in Chapter 5, our Debt module will include a control account for “tracking” or monitoring the principal and a control account for tracking or monitoring the interest on the debt.

The debt control account will be calculated as follows:

Debt opening balance_t = debt closing balance_t–1, with debt opening

balance_year 1 = debt balance in opening balance sheet.

Debt drawdown_t = 0.

Debt repayment_t is given by the amortization schedule.

Debt closing balance_t = debt opening balance_t plus debt drawdown_t plus

debt repayment_t.

The interest control account will be calculated as follows:

https://jigsaw.vitalsource.com/books/9780071808514/epub/ops/ch05.html#ch05

Interest opening balance_t = interest closing balance_t–1, with interest opening

balance_year 1 = 0.

Interest charge_t = interest rate times (debt opening balance_t + debt

drawdown_t).

Interest paid_t = – interest charge_t.

Interest closing balance_t = interest opening balance_t plus interest charge_t

plus interest paid_t.

8.6.3 Fixed Assets Module

As in Chapter 5, it is best practice to calculate fixed assets using a control account for tracking or monitoring the book value of fixed assets.

The fixed assets control account will be calculated as follows:

Fixed assets opening balance_t = fixed assets closing. balance_t–1, with fixed

assets opening balance_year 1 = fixed assets balance in opening balance sheet.

Capital expenditures (capex) increase the book value of fixed assets. Capex_t =

capex as % of sales_t times sales_t.

Depreciation decreases the book value of fixed assets. Depreciation_t is already

given.

https://jigsaw.vitalsource.com/books/9780071808514/epub/ops/ch05.html#ch05

Fixed assets closing balance_t = fixed assets opening balance_t plus capex_t

minus depreciation_t.

8.6.4 Balance Sheet Module

Fixed assets closing balance_t is calculated in the fixed assets module just

discussed.

Working capital_t = working capital as percent of sales_t times sales_t.

Closing cash balance_t is calculated in the Cash Flow module (discussed later).

Closing debt balance_t is calculated in the Debt module (discussed earlier).

Net assets_t = fixed assets closing balance_t plus working capital_t plus closing

cash balance_t minus debt closing balance_t.

Share capital_t = share capital in opening balance sheet.

Retained profit carried forward_t is calculated in the Profit and Loss module

(discussed earlier).

Net worth_t = share capital_t plus retained profit carried forward_t.

Since net worth must equal net assets, let us add at the bottom of the balance sheet a

balance sheet check that checks whether the balance sheet balances in each period. To this

end, we include two additional lines as follows:

Balance_t = net assets_t minus net worth_t.

Check_t = if (ABS (Balance_t) <= 0.01, TRUE, FALSE), where Abs(x) is the

absolute value of x, a Microsoft Excel function retuning the magnitude of a number

regardless of its sign. The way the formula has been expressed means that as long as the

absolute value of the difference between net assets_t and net worth_t is within 0.01, we

consider such a difference to be immaterial.