FINANCIAL PLANNING AND ANALYSIS - BLANK MODEL SET: THE BOOK'S OWN INPUTS

Type these back into the blank files and the book's results come back. Cell references are sheet!cell.
Percentages are given as decimals: 0.68 is 68 per cent.

The_Five_Comparisons_Blank.xlsx  (Chapters 2, 7, 8 and 12)

   Inputs!B4                 216340000
   Inputs!B7                 210000000
   Inputs!B8                 218900000
   Inputs!B9                 221000000
   Inputs!B10                208600000
   Inputs!B11                232500000
   Phasing!B5                195000000
   Phasing!C5                220000000
   Phasing!E5                198000000
   Phasing!G5                191400000
   Phasing!B6                205000000
   Phasing!C6                228000000
   Phasing!E6                209000000
   Phasing!B7                210000000
   Phasing!C7                232500000
   Phasing!E7                218900000
   Phasing!B8                230000000
   Phasing!C8                249500000
   Phasing!E8                214100000
   Phasing!G8                236000000
   Budget Cost!B4            118
   Budget Cost!B5            6.5
   Budget Cost!B6            620
   Budget Cost!B7            14
   Budget Cost!B8            93152000
   Budget Cost!B9            840000000
   Budget Cost!B10           3800800
   Budget Cost!B11           5952000

   Result: spread 10.6610 per cent of revenue; nine months 64,160,000 below the plan; the cycle costs 475,540.

Price_Volume_and_Mix_Blank.xlsx  (Chapters 1, 4, 5, 6, 15 and 16)

   Inputs!B5                 1200
   Inputs!B6                 1380
   Inputs!B7                 92000
   Inputs!B8                 88000
   Inputs!B9                 62560
   Inputs!B12                68000000
   Inputs!B13                62500000
   Inputs!B14                0.68
   Inputs!B15                31600000
   Inputs!B16                32400000
   Inputs!B17                0.45
   Inputs!B20                34000000
   Inputs!B21                34800000
   Inputs!B22                21000000
   Inputs!B23                19600000
   Inputs!B24                17500000
   Inputs!B25                18300000
   Inputs!B28                14
   Inputs!B29                100000
   Inputs!B30                74400000
   Inputs!B31                1
   Inputs!B34                840000000
   Inputs!B35                383152000
   Inputs!B36                290000000
   Inputs!B37                616340000
   Inputs!B38                276413000
   Inputs!B39                236000000
   Inputs!B40                0.1
   Inputs!B43                0.012
   Inputs!B44                84
   Inputs!B45                2312000
   Inputs!B46                166840000
   Inputs!B47                24000
   Inputs!B48                0.08
   Inputs!B49                0.01

   Result: gross profit variance -3,600,800; mix -1.5733 and price -1.4278 points; leverage 4.1132x; breakevens 635,779,011.98 and 680,556,519.78; the gross profit plan pays 140,760 less.

Segment_Contribution_and_Allocation_Blank.xlsx  (Chapters 3, 9, 13 and 14)

   Inputs!B5                 441600000
   Inputs!C5                 0.32
   Inputs!D5                 62000000
   Inputs!E5                 68000000
   Inputs!F5                 9000000
   Inputs!B6                 272000000
   Inputs!C6                 0.68
   Inputs!D6                 24000000
   Inputs!E6                 12000000
   Inputs!F6                 5000000
   Inputs!B7                 126400000
   Inputs!C7                 0.45
   Inputs!D7                 18000000
   Inputs!E7                 4000000
   Inputs!F7                 12000000
   Inputs!B9                 76000000
   Inputs!B10                12000000
   Inputs!B11                0.08
   Inputs!B12                5520000
   Inputs!B13                180
   Inputs!B14                24000
   Inputs!B15                4800
   Inputs!B16                84
   Inputs!B17                1380
   Inputs!B18                92000
   Discount!B18              0.1

   Result: instruments -37,642,285.71 on revenue, -25,717,899.36 on gross profit; closure costs 9,659,200 in year one; payback 4.4114 years; 20.4548 per cent.

Forecast_Accuracy_Blank.xlsx  (Chapters 10 and 11)

   Inputs!B5                 0.018
   Inputs!B6                 0.034
   Inputs!B7                 -0.009
   Inputs!B8                 0.046
   Inputs!B9                 0.022
   Inputs!B10                -0.013
   Inputs!B11                0.029
   Inputs!B18                236000000
   Inputs!B19                216340000
   Inputs!B20                221000000
   Inputs!B23                616340000
   Inputs!B24                276413000
   Inputs!B25                92187200
   Inputs!B26                840000000
   Inputs!B27                383152000
   Inputs!B28                290000000
   Inputs!B29                230000000
   Inputs!B30                200000

   Result: mean error 1.8568 per cent, bias to noise 0.9159x; the fourth quarter corrects to 231,697,943.56; reforecast EBIT 86,977,755.48.

The eighth forecast observation is computed from Inputs!B19 and B20; overtype it if yours differs.

