Financial Model

Financial Model

Given the model that specifies the growth in sales and other expenses as follows;

Sales  growth

10%

Current  assets/Sales

15%

Current  liabilities/Sales

8%

Net Fixed  assets/Sales

77%

1/27

Financial Model

Costs of  goods sold/Sales

50%

Depreciation  rate

10%

Interest  rate on debt

10%

Interest  paid on cash and marketable securities

8%

Tax rate

40%

Dividend  payout ratio

2/27

Financial Model

40%

3/27

Financial Model

We can calculate all the current assets, current liabilities, fixed assets and the cost of production, this is because the percentage given depends on sales and given that the level of sales is 1,000 then we can provide the values for the five years, this is clearly shown in the excel worksheet.

Depreciation is charged on fixed assets and therefore when we calculate the value of fixed assets then we will be in a position to calculate the depreciation level. Taxes will be levied on profits and dividends level will be derived from profits after taxation. However to get the amount of cash and marketable securities we will have to first derive the balance sheet where the assets will equal liabilities.

Having calculated the sales level for the five years and also the cost of production we will derive the profit and loss account which gives us the gross profit, profit before and after taxation

PROFIT AND  LOSS ACCOUNT

4/27

Financial Model

0

1

2

3

4

5

sales

1,000

1100

1210

5/27

Financial Model

1331

1464.1

1610.51

cost of  sales

500

550

605

665.5

732.05

805.255

gross  profit

6/27

Financial Model

500

550

605

666

732

805

7/27

Financial Model

depreciation

77

84.7

93.17

102.487

112.7357

124.0093

interest on  debts

8

8.8

8/27

Financial Model

9.68

10.648

11.7128

12.88408

profit  before taxation

415

457

502

552

608

668

9/27

Financial Model

taxation

166

182.6

200.86

220.946

243.0406

267.3447

profit  after taxation

249

274

301

10/27

Financial Model

331

365

401

divideds

99.6

109.56

120.516

132.5676

145.82436

160.4068

retained  earnings

11/27

Financial Model

149

164

181

199

219

241

The balance sheet will therefore take the following form:

BALANCE  SHEET

12/27

Financial Model

13/27

Financial Model

year

0

1

2

3

4

14/27

Financial Model

5

assets

15/27

Financial Model

fixed assets

770

847

931.7

1024.87

1127.357

1240.093

16/27

Financial Model

less  depreciation

77

84.7

93.17

102.487

112.7357

124.0093

net fixed  assets

693

762.3

838.53

17/27

Financial Model

922.383

1014.621

1116.083

current assets

150

165

181.5

199.65

219.615

241.5765

profit

18/27

Financial Model

249

274

301

331

365

401

19/27

Financial Model

total  assets

1,092

1,201

1,321

1,453

1,599

1,759

20/27

Financial Model

21/27

Financial Model

liabilities

22/27

Financial Model

current  liabilities

80

88

96.8

106.48

117.128

128.8408

retained  earnings

23/27

Financial Model

149

164

181

199

219

241

loans

863

949

1,044

1,148

1,263

24/27

Financial Model

1,389

total  liabilities

1,092

1,201

25/27

Financial Model

1,321

1,453

1,599

1,759

The profit levels over the four years is expected to rise due to the rise in sales, sales levels will rise from 1,000 to 1610 in the fifth year, the sales rise can be graphically be expressed as follows

The profit rise over the years can also be graphically analysed as follows:

According to the balance sheet we expect a rise in the level of liabilities such as creditors and loans, this is graphically shown below:

REFERENCE:

26/27

Financial Model

Simon Benninga (  ) No Negative Cash, second edition, MIT press, UK

27/27