M N O R S U V W X AB 1 The Widget Company Inc (USD $m) 2 Inputs E Jun 15 0 Jun 16 Jun 18 Jun 19 Jun 22 Jun 21 6 Jun 17 2 Jun 23 Jun 20 lun 20 5 P Jun 26 11 10 Jun 27 12 Jun 28 13 Jun 29 14 Jun 30 15 14 Keyotes All real values are real as at June 2015 so should be subject to inflation from 2016 Input Headings The Widget Company Inc Currency and units First year of forecast period (USD $m) Jun 16 Jun 15 Jun 20 Jun 21 Timeline Year Counter Jun 16 2016 1 00 Jun 17 2017 2 00 Jun 18 2018 3 00 Jun 19 2019 4 00 2020 2021 Jun 22 2022 7 00 Jun 23 2023 8 00 Jun 24 2024 9 00 Jun 25 2025 10 00 Jun 26 2026 11 00 Jun 27 2027 12 00 Jun 28 2028 13 00 Jun 29 2029 14 00 Jun 30 2030 15 00 0 00 5 00 6 00 Economic Assumptions CPI Applied to sales and admin costs only pa 4 00 4 00 4 00 3 00 3 00 3 00 3 00 3 00 3 00 3 00 3 00 3 00 3 00 3 00 3 00 Taxation Company tax rate ( ) Note this model does not deal with tax losses L 30 00 Revenue Volume Assumptions Sales (m units) Widget A Widget B Widget C Widget D Widget E Widget F Widget G Widget H units ms units ms units ms units ms units ms units ms units ms units ms 100 00 50 00 25 00 60 00 200 00 30 00 75 00 40 00 101 00 50 50 25 25 60 60 202 00 30 30 75 75 40 40 102 01 51 01 25 50 61 21 204 02 30 60 76 51 40 80 103 03 51 52 25 76 61 82 206 06 30 91 77 27 41 21 104 06 52 03 26 02 62 44 208 12 31 22 78 05 41 62 105 10 52 55 26 28 63 06 210 20 31 53 78 83 42 04 106 15 53 08 26 54 63 69 212 30 31 85 79 61 42 46 107 21 53 61 26 80 64 33 214 43 32 16 80 41 42 89 108 29 54 14 27 07 64 97 216 57 32 49 109 37 54 68 27 34 65 62 218 74 32 81 82 03 43 75 110 46 55 23 27 62 66 28 220 92 33 14 82 85 44 18 111 57 55 78 27 89 66 94 223 13 33 47 83 68 44 63 112 68 56 34 28 17 67 61 225 37 33 80 84 51 45 07 113 81 56 90 28 45 68 29 227 62 34 14 85 36 45 52 114 95 57 47 28 74 68 97 229 89 34 48 86 21 45 98 81 21 43 31 Revenue Pricing Assumptions Widget categories Widget A Widget B Widget C Widget D Widget E Widget F Widget G Widget H Category Low value Mid value High value Mid value Low value High value Mid value Mid value Pricing scenario used Base Pricing (nominal) Base Scenario D D Inputs Calculations Outputs U V W Jun 15 Jun 22 M Jun 23 Jun 21 Jun 28 Jun 16 Jun 17 12 Jun 29 Jun 18 3 Jun 30 AB 1 The Widget Company Inc (USD $m) 2 Inputs Pricing scenario used Jun 24 Jun 19 4 Jun 20 5 Jun 25 10 Jun 26 11 Jun 27 12 9 13 15 Base Pricing (nominal) Base Scenario High value Mid value Low value $ unit $ unit $ unit 150 00 110 00 60 00 156 00 114 40 62 40 162 24 118 98 64 90 167 11 122 55 66 84 172 12 126 22 68 85 177 28 130 01 7 0 91 182 60 133 91 73 04 188 08 137 93 75 23 193 72 142 06 77 49 199 53 146 33 79 81 205 52 150 72 82 21 211 69 155 24 84 67 218 04 159 89 87 21 224 58 164 69 89 83 231 32 169 63 92 53 Pricing (nominal) Growth Scenario High value Mid value Low value $ unit $ unit $ unit 180 00 140 00 90 00 187 20 145 60 93 60 194 69 151 42 97 34 200 53 155 97 100 26 206 54 160 65 103 27 212 74 165 47 106 37 219 12 170 43 109 56 225 70 175 54 112 85 232 47 180 81 116 23 239 44 186 23 119 72 246 62 191 82 123 31 254 02 197 57 127 01 261 64 203 50 130 82 269 49 209 61 134 75 277 58 215 89 138 79 Cost Of Sales ( ) High value Mid value Low value 60 00 65 00 70 00 60 00 65 00 70 00 60 00 65 00 70 00 60 00 65 00 70 00 60 00 65 00 70 00 60 00 65 00 70 00 60 00 65 00 70 00 60 00 65 00 70 00 60 00 65 00 70 00 60 00 65 00 70 00 60 00 65 00 70 00 60 00 65 00 70 00 60 00 65 00 70 00 60 00 65 00 70 00 60 00 65 00 70 00 14 00 Overheads Direct sales costs Marketing costs Other (per year) $ per unit of sales $m 5 00 5 00 7 00 7 00 7 00 7 00 7 00 7 00 7 00 7 00 7 00 7 00 7 00 7 00 7 00 750 00 Working Capital Days receivable days Inventory days days Days payable days All working capital calculated on a (days actual) basis 33 00 42 00 37 00 33 00 42 00 37 00 33 00 42 00 37 00 33 00 42 00 37 00 33 00 42 00 37 00 33 00 42 00 37 00 33 00 42 00 37 00 33 00 42 00 37 00 33 00 42 00 37 00 33 00 42 00 37 00 33 00 42 00 37 00 33 00 42 00 37 00 33 00 42 00 37 00 33 00 42 00 37 00 33 00 42 00 37 00 Capital Expenditure One off capex Regular capex $m Sm 8 840 00 624 00 0 00 648 96 0 00 674 92 0 00 695 17 0 00 716 02 10 447 94 737 50 0 00 759 63 0 00 782 42 0 00 805 89 0 00 830 06 12 112 02 854 97 0 00 880 62 0 00 907 03 0 00 934 24 0 00 962 27 Depreciation Rates PP E accounting (straight line) PP E tax (diminishing value) years pa 7 00 20 00 1 Financing Interest rates Cash on hand Term loan 1 interest Term loan 2 interest Term loan 3 interest Revolver facility 4 00 9 00 10 00 11 00 13 00 4 00 9 00 10 00 11 00 13 00 4 00 9 00 10 00 11 00 13 00 4 00 9 00 10 00 11 00 13 00 4 00 9 00 10 00 11 00 13 00 4 00 9 00 10 00 11 00 13 00 4 00 9 00 10 00 11 00 13 00 4 00 9 00 10 00 11 00 13 00 4 00 9 00 10 00 11 00 13 00 4 00 9 00 10 00 11 00 13 00 4 00 9 00 10 00 11 00 13 00 4 00 9 00 10 00 11 00 13 00 4 00 9 00 10 00 11 00 13 00 4 00 9 00 10 00 11 00 13 00 4 00 9 00 10 00 11 00 13 00 Other Term loan 1 drawdown (start of year) Term loan 1 repayment (end of year) Term loan 2 drawdown (start of year) Term loan 2 repayment (end of year) Term loan 3 drawdown (start of year) Term loan 3 repayment (end of year) Equity contribution Equity distributions ( of cash available) 4 000 00 (200 00) 0 00 0 00 0 00 0 00 2 500 00 40 00 0 00 (200 00 0 00 0 00 0 00 0 00 0 00 50 00 0 00 (200 00) 0 00 0 00 0 00 0 00 0 00 60 00 0 00 200 00) 0 00 0 00 0 00 0 00 0 00 70 00 0 00 (200 00) 0 00 0 00 0 00 0 00 0 00 80 00 0 00 (200 00) 5 000 00 (250 00) 0 00 0 00 0 00 80 00 0 00 (200 00 0 00 (250 00 0 00 0 00 0 00 80 00 0 00 (200 00) 0 00 (250 00) 0 00 0 00 0 00 80 00 0 00 (200 00) 0 00 (250 00) 0 00 0 00 0 00 80 00 0 00 (200 00) 0 00 (250 00) 0 00 0 00 0 00 80 00 0 00 (200 00) 0 00 (250 00) 6 000 00 (600 00) 0 00 80 00 0 00 (200 00) 0 00 (250 00) 0 00 (600 00) 0 00 80 00 0 00 (200 00) 0 00 (250 00) 0 00 (600 00) 0 00 80 00 0 00 (200 00) 0 00 (250 00) 0 00 (600 00) 0 00 80 00 80 00 0 00 (200 00) 0 00 (250 00) 0 00 (600 00) 0 00 80 00 heet End N U V W 26 Jun 15 Jun 16 Jun 17 12 Jun 18 Jun 19 Jun 20 45 Jun 21 Jun 22 1 The Widget Company Inc (USD $m) 2 Calculations 3 4 Operations Jun 23 Jun 24 Jun 25 10 Jun 26 11 Jun 27 12 Jun 28 13 Jun 29 14 Jun 30 15 Volumes Sales (m units) Widget A Widget B Widget C Widget D Widget E Widget F Widget G Widget H Total volume Low value Mid value High value Mid value Low value High value Mid value Mid value units ms 100 00 50 00 25 00 60 00 200 00 30 00 75 00 40 00 580 00 101 00 50 50 25 25 60 60 202 00 30 30 75 75 40 40 585 80 102 01 51 01 25 50 61 21 204 02 30 60 76 51 40 80 591 66 103 03 51 52 25 76 61 82 206 06 30 91 77 27 41 21 597 57 104 06 52 03 26 02 62 44 208 12 31 22 78 05 41 62 603 55 105 10 52 55 26 28 63 06 210 20 31 53 78 83 42 04 609 59 106 15 53 08 26 54 63 69 212 30 31 85 79 61 42 46 615 68 107 21 53 61 26 80 64 33 214 43 32 16 80 41 42 89 621 84 108 29 54 14 27 07 64 97 216 57 32 49 81 21 43 31 628 06 109 37 54 68 27 34 65 62 218 74 32 81 82 03 43 75 634 34 110 46 55 23 27 62 66 28 220 92 33 14 82 85 44 18 640 68 111 57 55 78 27 89 66 94 223 13 33 47 83 68 44 63 647 09 112 68 56 34 28 17 67 61 225 37 33 80 84 51 45 07 653 56 113 81 56 90 28 45 68 29 227 62 34 14 85 36 45 52 660 09 114 95 57 47 28 74 68 97 229 89 34 48 86 21 45 98 666 70 High value Mid value Low value units ms units ms units ms 55 00 225 00 300 00 300 00 55 55 227 25 303 00 303 00 56 11 229 52 306 03 306 03 56 67 231 82 309 09 309 09 57 23 234 14 312 18 312 18 57 81 236 48 315 30 315 30 58 38 238 84 318 46 318 46 58 97 241 23 321 64 321 64 59 56 243 64 324 86 324 86 60 15 246 08 328 11 328 11 60 75 248 54 331 39 331 39 61 36 251 03 334 70 334 70 61 98 253 54 338 05 338 05 62 60 256 07 341 43 341 43 63 22 258 63 344 84 344 84 4,829 07 Revenues Pricing scenario used Base Pricing (nominal) Growth Scenario High value Mid value Low value $ unit $ unit $ unit 150 00 110 00 60 00 156 00 114 40 62 40 162 24 118 98 64 90 167 11 122 55 66 84 172 12 126 22 68 85 177 28 130 01 70 91 182 60 133 91 73 04 188 08 137 93 75 23 193 72 142 06 77 49 199 53 146 33 79 81 205 52 150 72 82 21 211 69 155 24 84 67 218 04 159 89 87 21 224 58 164 69 89 83 231 32 169 63 92 53 Sm $m High value Mid value Low value Total revenue 8,250 00 24,750 00 18,000 00 51,000 00 8,665 80 25,997 40 18,907 20 53,570 40 9,102 56 27,307 67 19,860 12 56,270 35 9,469 39 28,408 17 20,660 49 58,538 04 9,851 01 29,553 02 21,493 10 60,897 13 10,248 00 30,744 00 22,359 28 63,351 28 10,661 00 31,982 99 23,260 35 65,904 34 11,090 63 3 3,271 90 24,197 75 68,560 28 11,537 59' 34,612 76' 25,172 92 71,323 26 12,002 55 36,007 65 26,187 38 74,197 59 12,486 25 37,458 76 27,242 74 77,187 75 12,989 45 38,968 35 28,340 62 80,298 42 13,512 92 40,538 77 29,482 74 8 3,534 44 14,057 50 42,172 49 30,670 90 86,900 88 14,624 01 43,872 04 31,906 94 90,402 99 $m Cost of Sales High value Mid value Low value 60 00 65 00 70 00 60 00 65 00 70 00 60 00 65 00 70 00 60 00 65 00 70 00 60 00 65 00 70 00 60 00 65 00 70 00 60 00 65 00 70 00 60 00 65 00 70 00 60 00 65 00 70 00 60 00 65 00 70 00 60 00 65 00 70 00 60 00 65 00 70 00 60 00 65 00 70 00 60 00 65 00 70 00 60 00 65 00 70 00 7 8 High value Mid value Low value Total cost of sales 4,950 00 16,087 50 12,600 00 33,637 50 5,199 48 16,898 31 13,235 04 35,332 83 5,461 53 17,749 98 13,902 09 37,113 60 5,681 63 18,465 31 14,462 34 38,609 28 5,910 60 19,209 46 15,045 17 40,165 24 6,148 80 19,983 60 15,651 49 41,783 90 6,396 60 20,788 94 16,282 25 43,467 79 6,654 38 21,626 74 16,938 42 45,219 54 6,922 55 7,201 53 22,498 29 23,404 97 17,621 04 18,331 17 47,041 8948,937 67 7,491 75 24,348 20 19,069 92 50,909 86 ,793 67 25,329 43 19,838 43 52,961 53 ,107 75 26,350 20 20,637 92 55,095 88 8 ,434 50 27,412 12 21,469 63 57,316 24 8,774 41 28,516 82 22,334 86 59,626 09 Total Sales Sales Cost of sales Gross profit Sm Sm Sm 51,000 00 (33,637 50) 17,362 50 53,570 40 (35,332 83) 18,237 57 56,270 35 (37,113 60) 19 156 74 58,538 04 (38,609 28) 19,928 76 60,897 13 (40,165 24) 20,731 89 63,351 28 (41,783 90) 21,567 38 65,904 34 (43,467 79) 22 436 55 68,560 28 (45,219 54) 23,340 74 71,323 26 (47,041 89) 24,281 37 74,197 59 (48,937 67) 25,259 91 77,187 75 (50,909 86) 26,277 89 80,298 42 83,534 44 (52,961 53) (55,095 88) 27,336 89 28,438 56 86,900 88 (57,316 24) 29,584 64 90,402 99 (59,626 09) 30,776 90 Inputs Calculations Outputs Jun 16 1 G Jun 17 2 H Jun 18 3 Jun 19 4 Jun 20 5 Jun 21 6 Jun 22 7 Jun 23 8 Jun 24 9 Jun 25 10 Jun 26 11 Jun 27 12 Jun 28 13 Jun 29 14 Jun 30 15 4A B 1 The Widget Company Inc (USD $m) 2 Calculations 51 Expense Schedule 53 Sales 54 Overhead expense per unit Sales expense 52 $ per unit 14 00 14 56 8,444 80 $m 15 14 8,870 42 15 75 9,317 49 16 22 ,692 98 16 71 10,083 61 17 21 10,489 98 17 72 10,912 72 18 26 11,352 51 18 80 11,810 01 19 37 1937 12,285 96 19 95 12,781 08 21 16 13,831 99 9 21 80 14,389 42 22 45 14,969 32 13,296 16 of sales Marketing Marketing costs Sales Marketing expense 5 00 51,000 00 2,550 00 6 00 53,570 40 3,214 22 7 00 56,270 35 3,938 92 7 00 58,538 04 4,097 66 7 00 60,897 13 4,262 80 7 00 63,351 28 4,434 59 7 00 65,904 34 4,613 30 7 00 68,560 28 4,799 22 7 00 71,323 26 4,992 63 7 00 74,197 59 5,193 83 7 00 77,187 75 5,403 14 7 00 80,298 42 5,620 89 7 00 83,534 44 5,847 41 7 00 7 00 86,900 88 90,402 99 6,083 066,328 21 Other Other costs Sm 750 00 780 00 811 20 843 65 8 68 96 895 03 921 88 949 53 978 02 1,007 36 1,037 58 1,068 71 1,100 77 1,133 79 1,167 81 1,202 84 64 Total expenses 11 774 80 12 895 84 14,100 06 14 659 60 15,241 43 15 846 44 16,475 56 17,129 75 17,810 00 18,517 37 19 252 93 20 017 82 20 813 20 21 640 29 22 500 37 66 67 Working capital 68 Days in year 366 00 365 00 365 00 365 00 366 00 3 65 00 365 00 365 00 366 00 365 00 365 00 365 00 366 00 365 00 365 00 Current Assets Sales Days receivable Accounts receivable $m days Sm 51,000 00 33 00 4,598 36 53,570 40 33 00 4,843 35 56,270 35 33 00 5,087 46 58,538 04 33 00 5,292 48 60,897 13 33 00 5,490 72 63,351 28 33 00 5,727 65 65,904 3468,560 28 33 00 33 00 5,958 476,198 60 71,323 26 33 00 6,430 79 74,197 59 33 00 6,708 28 77,187 75 33 00 6,978 62 80,298 42 33 00 7,259 86 83,534 44 33 00 7,531 79 86,900 88 33 00 7,856 79 90,402 99 33 00 8,173 42 Cost of sales Inventory days Inventory $m days Sm 33,637 50 42 00 3,860 04 35,332 83 42 00 4,065 70 37,113 60 42 00 4,270 61 38,609 28 42 00 4,442 71 40,165 24 42 00 4,609 13 41,783 90 42 00 4,808 01 43,467 79 45,219 54 47,041 89 42 00 42 00 42 00 5,001 775,203 345,398 25 48,937 67 42 00 5,631 18 50,909 86 52,961 53 55,095 88 42 00 42 00 42 00 5,858 126,094 206,322 48 57,316 24 59,626 09 42 00 42 00 6,595 296,861 08 Current Liabilities Cost of sales Total expenses Days payable Accounts payable $m $m days 33,637 50 11,774 80 37 00 4,590 86 83 84 35,332 83 12,895 84 37 00 4,888 93 37,113 60 14,100 06 37 00 5,191 52 38,609 28 14,659 60 37 00 5,399 86 40,165 24 15,241 43 37 00 41,783 90 15,846 44 37 00 5,841 98 43,467 7945,219 54 16,475 56 1 7,129 75 37 00 37 00 6,076 45 6 ,320 34 47,041 8948,937 67 17,810 00 18,517 37 37 00 37 00 6,556 07 6,837 91 50,909 86 19,252 93 37 00 7,112 39 52,961 53 20,017 82 37 00 7,397 91 55,095 88 20,813 20 37 00 7,673 87 57,316 24 21,640 29 37 00 8,003 81 59,626 09 22,500 37 37 00 8 ,325 15 Sm 85 86 87 Net Working Capital Networking capital Change in net working capital 0 00 88 3,867 54 (3,867 54) 4,020 11 (152 57) 4,166 54 (146 43) 4,335 33 (168 79) 4,498 63 (163 30) 4,693 68 (195 05) 4,883 80 (190 12) 5,081 61 (197 81) 5,272 97 (191 36) 5,501 55 (228 58) 5,724 35 (222 80) 5,956 156 (231 81) ,180 406 (224 25) ,448 27 (267 87) 6,709 36 (261 08) 89 U V W A B 1 The Widget Company Inc (USD $m) 2 Calculations 90 PP E Jun 16 1 Jun 17 2 Jun 18 3 Jun 19 4 Jun 20 5 Jun 21 6 Jun 22 7 M Jun 23 8 N Jun 24 9 O Jun 25 10 P Jun 26 11 Q Jun 27 12 R Jun 28 13 Jun 29 14 Jun 30 15 91 93 Accounting Depreciation Opening accounting net book value 0 00 8,112 00 7,316 25 5,504 78 4,478 07 12,322 87 9,633 33 8,206 81 6,781 355,357 90 14,318 25 11,168 72 9,513 95 7,861 44 0 00 96 One off capex Regular capex Capital expenditure incurred (nominal) $m $m $m 8,840 00 624 00 9,464 00 0 00 648 96 648 96 674 92 674 92 0 00 695 17 695 17 0 00 716 02 716 02 10,447 94 7 37 50 11,185 44 0 00 759 63 759 63 0 00 782 42 782 42 0 00 805 89 805 89 0 00 830 06 830 06 12,112 02 854 97 12,966 99 0 00 880 62 880 62 0 00 907 03 907 03 0 00 934 24 934 24 0 00 962 27 962 27 98 PP E accounting (straight line) years Capital expenditure assumed to be made on the first day of the period Year 100 101 102 103 104 105 106 107 0 00 92 71 96 42 99 31 102 29 1,597 92 108 52 111 77 108 109 110 1,352 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 1,352 00 1,352 00 92 71 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 1,444 71 1,352 00 92 71 96 42 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 1,541 13 1,352 00 92 71 96 42 99 31 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 1,640 43 1,352 00 92 71 96 42 99 31 102 29 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 1,742 72 1,352 00 92 71 96 42 99 31 102 29 1,597 92 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 3,340 64 1,352 00 92 71 96 42 99 31 102 29 1,597 92 108 52 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 3,449 16 0 00 0 00 96 42 99 31 102 29 1,597 92 108 52 111 77 115 13 0 00 0 00 0 00 0 00 0 00 0 00 2,231 35 0 00 0 00 0 00 99 31 102 29 1,597 92 108 52 111 77 115 13 118 58 0 00 0 00 0 00 0 00 0 00 2,253 52 0 00 0 00 0 00 0 00 102 29 1,597 92 108 52 111 77 115 13 118 58 1,852 43 0 00 0 00 0 00 0 00 4,006 64 0 00 0 00 0 00 0 00 0 00 1,597 92 108 52 111 77 115 13 118 58 1,852 43 125 80 0 00 0 00 0 00 4,030 15 0 00 0 00 0 00 0 00 0 00 0 00 108 52 111 77 115 13 118 58 1,852 43 125 80 129 58 0 00 0 00 2,561 80 0 00 0 00 0 00 0 00 0 00 0 00 0 00 111 77 115 13 118 58 1,852 43 125 80 129 58 133 46 0 00 2,586 75 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 115 13 118 58 1,852 43 125 80 129 58 133 46 137 47 2,612 44 111 0 00 112 113 114 115 0 00 0 00 0 00 0 00 0 00 0 00 2,208 94 0 00 8 112 00 7 316 25 6 450 04 5 504 78 4 478 07 12,322 87 9 633 33 8 206 81 6,781 35 5 ,357 90 14,318 25 11,168 72 9 513 95 7 861 44 6 211 27 9 7 0 00 9,464 00 1,892 80 7,571 20 116 117 118 Accounting depreciation 119 120 Closing accounting net book value 121 122 Taxation Depreciation 123 124 Opening tax net book value 125 Capital expenditure incurred 126 Tax depreciation 127 Closing tax net book value 1281 129 Financing 130 131 Term loans 132 133 Term loan 1 134 Opening balance 135 Term loan 1 drawdown (start of year) 136 Term loan 1 repayment (end of year) 137 Ending balance 7,571 20 648 96 1,644 03 6,576 13 6,576 13 674 92 1,450 21 5,800 84 5,800 84 695 17 ,299 20 5,196 80 5,196 80 716 02 1,182 56 4 730 26 4,730 26 11,185 44 3,183 14 12,732 56 12,732 56 759 63 2,698 44 10,793 75 10,793 75 7 82 42 2,315 23 9 260 93 ,260 93 8 05 89 2,013 36 8,053 46 8,053 46 830 06 1,776 70 7 106 82 ,106 82 12,966 99 4,014 76 16,059 05 16,059 05 880 62 3,387 93 13,551 73 13,551 73 907 03 2,891 75 11,567 01 11,567 01 934 24 2,500 25 10 001 00 10,001 00 962 27 ,192 66 34,443 04 8,770 62 1 2 0 00 0 00 4,000 00 (200 00) 3,800 00 3,800 00 0 00 (200 00) 3,600 00 3,600 00 0 00 (200 00) 3,400 00 3,400 00 0 00 (200 00) 3,200 00 3,200 00 0 00 (200 00) 3,000 00 3,000 00 0 00 (200 00) 2,800 00 2,800 00 0 00 (200 00) 2,600 00 2,600 00 0 00 (200 00) 2,400 00 2,400 00 0 00 (200 00) 2,200 00 2,200 00 0 00 (200 00) 2,000 00 2,000 00 0 00 (200 00) 1,800 00 1,800 00 0 00 (200 00) 1,600 00 1,600 00 0 00 (200 00) 1,400 00 1,400 00 0 00 (200 00) 1,200 00 1,200 00 0 00 (200 00) 1,000 00 0 00 K U V W Jun 15 Jun 16 1 Jun 17 2 Jun 18 3 Jun 19 4 Jun 20 5 Jun 21 6 Jun 22 7 M Jun 23 8 N Jun 24 9 O Jun 25 10 P Jun 26 11 Q Jun 27 12 R Jun 28 13 S Jun 29 14 Jun 30 15 LA B 1 The Widget Company Inc (USD $m) 2 Calculations 129 Financing 130 131 Term loans 132 Term loan 1 Opening balance 135 Term loan 1 drawdown (start of year) 136 Term loan 1 repayment (end of year) Ending balance 133 0 00 4,000 00 (200 00) 3,800 00 3,800 00 0 00 (200 00) 3,600 00 3,600 00 0 00 (200 00) 3,400 00 3,400 00 0 00 (200 00) 3,200 00 3,200 00 0 00 (200 00) 3,000 00 3,000 00 0 00 (200 00) 2,800 00 2,800 00 0 00 (200 00) 2,600 00 2,600 00 0 00 (200 00) 2,400 00 2,400 00 0 00 (200 00) 2,200 00 2,200 00 0 00 (200 00) 2,000 00 2,000 00 0 00 (200 00) 1,800 00 1,800 00 0 00 (200 00) 1,600 00 1,600 00 0 00 (200 00) 1,400 00 1,400 00 0 00 (200 00) 1,200 00 1,200 00 0 00 (200 00) 1,000 00 0 00 Term loan 2 Opening balance Term loan 2 drawdown (start of year) Term loan 2 repayment (end of year) Ending balance 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 5,000 00 (250 00) 4,750 00 4,750 00 0 00 (250 00) 4,500 00 4,500 00 0 00 (250 00) 4,250 00 4,250 00 0 00 (250 00) 4,000 00 4,000 00 0 00 (250 00) 3,750 00 3,750 00 0 00 (250 00) 3,500 00 3,500 00 0 00 (250 00) 3,250 00 3,250 00 0 00 (250 00) 3,000 00 3,000 00 0 00 (250 00) 2,750 00 2,750 00 0 00 (250 00) 2,500 00 3,600 00 147 Term loan 3 Opening balance Term loan 3 drawdown (start of year) Term loan 3 repayment (end of year) Ending balance 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 6,000 00 (600 00) 5,400 00 5,400 00 0 00 (600 00) 4,800 00 4,800 00 0 00 (600 00) 4 200 00 0 00 4,200 00 0 00 (600 00) 3,600 00 149 (600 00) 3,000 00 Free Cash Flows Opening cash Cash Flows from Operating Activities Cash Flows from Investing Activities Free Cash Flow Available (Required) 156 0 00 9,911 53 7,124 97 4,613 382,814 171,585 834,619 77 755 213,889 453,933 574,272 02 4,598 955 ,777 595 ,947 41 9,464 00 648 96 674 92 695 17 716 02 11,185 44 759 63 10 219 21 14 449 94 11,733 45 9 580 57 8 129 14 18,548 86 11,326 81 2,175 36 5,556 76 782 42 ,514 54 1,612 91 5,822 83 8 05 89 8 241 63 1,558 33 6,006 67 830 06 8,395 06 1,589 01 7,069 35 12,966 99 21,625 35 5,315 07 7 182 97 880 62 13,378 65 2,465 73 4,486 96 907 03 7 859 73 1,361 95 1,650 66 7,007 137,247 47 9 34 24 962 27 9,303 32 9 860 41 8 159 0 00 Financing Cash Flows Equity contribution Term loan drawdown repayment Scheduled financing cash flows 2,500 00 3,800 00 6,300 00 0 00 (200 00) (200 00) 0 00 (200 00) (200 00) (200 00) (200 00) 0 00 (200 00) (200 00) 0 00 4,550 00 4,550 00 0 00 (450 00) (450 00) 0 00 (450 00) (450 00) 0 00 (450 00) (450 00) 0 00 (450 00) (450,00) 0 00 4,950 00 4,950 00 0 00 (1,050 00) (1,050 00) 0 00 (1,050 00) (1,050 00) 0 00 (1,050 00) (1 050 00) 0 00 (1,050 00) (1 050 00) 162 163 164 Cash Flow Available (Required) 16,519 21 14,249 94 11,533 45 9,380 57 7 ,929 14 23,098 86 10,876 81 8 ,064 54 7,791 63 7,945 0626,575 35 12,328 65 6,809 73 8,253 32 8,810 41 0 00 Revolver Opening balance Borrowings Payments Ending balance 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 168 169 170 171 172 173 174 Equity Distribution to shareholders 6,607,68 7,124 97 6,920 076 ,566 40 6,343 31 18,479 09 8 ,701 45 6 ,451 63 6,233 30 6,356 05 21,260 28 9,862 92 5,447 78 6,602 66 7,048 33 U V W Jun 15 G Jun 17 2 H Jun 18 3 I Jun 19 4 J Jun 20 5 K Jun 21 6 L Jun 22 7 M Jun 23 8 N Jun 24 9 O Jun 25 10 P Jun 26 11 Q Jun 27 12 R Jun 28 13 S Jun 29 14 Jun 30 15 179 180 181 9,911 53 4 00 396 46 7,124 97 4 00 285 00 4,613 38 4 00 184 54 2,814 171,585 834,619 77 4 00 4 00 4 00 112 57 63 43 184 79 2,175 36 4 00 87 01 1,612 91 4 00 64 52 1,558 33 4 00 62 33 1,589 01 4 00 63 56 5,315 07 4 00 212 60 2,465 73 4 00 98 63 1 361 95 4 00 54 48 1 650 66 4 00 66 03 F 1 The Widget Company Inc (USD $m) Jun 16 2 Calculations 1 174 175 Interest 176 177 Cash on hand 178 Assume calculation based on opening balance Opening cash 0 00 Cash on hand 4 00 Interest revenue 0 00 182 Term loan 1 184 Assumes drawdowns happen at the start of the year, repayment at the end of the year 185 Opening balance 0 00 186 Term loan 1 drawdown (start of year) 4,000 00 187 Term loan 1 interest 9 00 188 Interest expense (360 00) 189 190 Term loan 2 191 Assumes drawdowns happen at the start of the year, repayment at the end of the year Opening balance 0 00 193 Term loan 2 drawdown (start of year) 0 00 194 Term loan 2 interest 10 00 Interest expense 0 00 183 3,800 00 0 00 9 00 (342 00) 3,600 00 0 00 9 00 (324 00) 3,400 00 0 00 9 00 (306 00) 3,200 00 0 00 9 00 (288 00) 3,000 00 0 00 9 00 (270 00) 2,800 00 0 00 9 00 (252 00) 2,600 00 0 00 9 00 (234 00) 2,400 00 0 00 9 00 (216 00) 2,200 00 0 00 9 00 (198 00) 2,000 00 0 00 9 00 (180 00) 1,800 00 0 00 9 00 (162 00) 1,600 00 0 00 9 00 144 00 1,400 00 0 00 9 00 (126 00) 1,200 00 0 00 9 00 (108 00) 192 0 00 4,500 00 4,000 00 0 00 0 00 0 00 0 00 10 00 0 00 0 00 0 00 10 00 0 00 0 00 0 00 10 00 0 00 0 00 10 00 0 00 5,000 00 10 00 (500 00) 4,750 00 0 00 10 00 (475 00) 4,250 00 0 00 10 00 (425 00) 3,750 00 0 00 10 00 (375 00) 3,500 00 0 00 10 00 (350 00) 3,250 00 0 00 10 00 325 00 3,000 00 0 00 10 00 (300 00) 2,750 00 0 00 10 00 (275 00) 195 10 00 (450 00) 0 00 10 00 (400 00) 0 00 Term loan 3 Assumes drawdowns happen at the start of the year, repayment at the end of the year Opening balance 0 00 Term loan 3 drawdown (start of year) 0 00 Term loan 3 interest 11 00 Interest expense 0 00 0 00 0 00 11 00 0 00 0 00 0 00 11 00 0 00 0 00 0 00 11 00 0 00 0 00 11 00 0 00 0 00 0 00 11 00 0 00 0 00 0 00 11 00 0 00 0 00 0 00 11 00 0 00 0 00 0 00 11 00 0 00 0 00 0 00 11 00 0 00 6,000 00 11 00 (412 50) 5,400 00 0 00 11 00 (385 00) 4,800 00 0 00 11 00 528 00 4,200 00 0 00 11 00 (330 00) 3,600 00 0 00 11 00 (302 50) 0 00 Revolver facility Assume calculation based on opening balance Opening balance Revolver facility Interest expense 0 00 13 00 0 00 0 00 13 00 0 00 0 00 13 00 0 00 0 00 13 00 0 00 0 00 13 00 0 00 0 00 13 00 0 00 0 00 13 00 0 00 0 00 13 00 0 00 0 00 13 00 0 00 0 00 13 00 0 00 0 00 13 00 0 00 0 00 13 00 0 00 0 00 13 00 0 00 0 00 13 00 0 00 0 00 13 00 0 00 209 210 211 Interest revenue (expense) (360 00) 54 46 (39 00) (121 46) (175 43) (706 57) (542 21) (596 99) (576 48) (535 67) (903 94) (684 40) 997 00 (701 52) (619 47) U V W E Jun 15 Jun 16 Jun 22 M Jun 23 A B 1 The Widget Company Inc (USD $m) 2 Calculations 211 212 Tax Jun 17 2 Jun 18 Jun 19 34 Jun 20 5 Jun 21 6 Jun 24 9 0 Jun 25 10 Jun 26 11 Q Jun 27 12 R Jun 28 13 Jun 29 14 Jun 30 15 213 30 00 Company tax rate ( ) Note this model does not deal with tax losses 214 215 216 217 218 P L tax Profit (Loss) before tax 3,875 70 3,951 48 3,476 56 3,507 26 3,572 30 1,673 73 1,969 623,405 08 3,663 54 3,953 36 2,114 382,604 526,060 56 4,656 08 5,044 62 219 Income tax benefit (expense) (1,162 71) (1,185 44) (1,042 97) (1,052 18) (1,071 69) (502 12) (590 89) (1,021 52) (1,099 06) (1,186 01) (634 31) (781 36) (1,818 17) (1,396 82) (1,513 38) 220 221 222 223 224 225 Adjustments to arrive at taxable profit Adjustment for timing differences in depreciation Accounting depreciation Tax depreciation Timing difference (1,352 00) (1,892 80) 540 80 (1,444 71) (1,644 03) 199 32 (1,541 13) (1,450 21) (90 92) (1,640 43) (1,299 20) (341 23) (1,742 72) (1,182 56) (560 16) (3,340 64) (3,183 14) (157 50) (3,449 16) (2,698 44) (750 72) (2,208 94) (2,315 23) 106 30 (2,231 35) (2,013 36) (217 99) (2,253 52) (1,776 70) (476 81) (4,006 64) (4,014 76) 8 13 (4,030 15) (3,387 93) (642 22) (2,561 80) (2,891 75) 329 95 (2,586 75) (2,500 25) (86 50) (2,612 44) (2,192 66) (419 79) 226 227 228 Adjusted profit before tax Tax payable 4,416 50 (1,324 95) 4 150 80 (1,245 24) 3,385 64 (1,015 69) 3,166 02 (949 81) 3,012,14 (903 64) 1,516 23 (454 87) 1,218 89 (365 67) 3,511 37 (1,053 41) 3,445 55 (1,033 66) 3,476 55 (1,042 96) 2,122 51 (636 75) 1,962 31 (588 69) 6,390,51 (1,917 15) 4,569 58 (1,370 87) 4,624 83 (1,387 45) 230 231 232 233 234 Depreciation timing differences Opening balance Timing difference Closing balance 0 00 540 80 540 80 540 80 199 32 740 12 7 40 12 (90 92) 649 21 649 21 (341 23) 307 97 307 97 (560 16) (252 19) (252 19) (157 50) (409 69) (409 69) (750 72) (1,160 41) (1,160 41) 106 30 (1,054 12) (1,054 12) (217 99) (1,272 11) (1,272 11) (476 81) (1,748 92) (1,748 92) 8 13 (1,740 79) (1,740 79) (642 22) (2,383 01) (2,383 01) 329 95 (2,053 06) (2,053 06) (86 50) (2,139 56) (2,139 56) (419 79) (2,559 35) 0 00 235 236 (162 24) (222 04) (194 76) (92 39) 75 66 122 91 22 91 348 12 348 12 316 24 316 24 381 63 524 68 524 68 522 24 522 24 714 90 714 90 6 15 92 615 92 641 87 767 81 222 04 0 00 Deferred tax assetliability (depreciation timing differences) 237 238 Deferred tax asset (depreciation timing differences) 239 Deferred tax liability (depreciation timing differences) 240 241 Sheet End 242 162 24 0 00 0 00 194 76 0 00 92 39 0 00 0 00 75 66 0 00 122 91 0 00 348 12 0 00 316 24 0 00 381 63 0 00 524 68 0 00 522 24 0 00 714 90 0 00 615 92 641 87 0 00 767 81 U V W sunny leon Jun 16 Jun 15 H Jun 18 elementele pe lumen Jun 17 Jun 19 dengan M Jun 23 Jun 20 tempat Jun 21 N Jun 24 bermain Jun 22 AB 1 The Widget Company Inc (USD $m) 2 Outputs 3 4 Profit and Loss luas Jun 25 dan luar negeringen Jun 26 Jun 28 som heter Jun 29 Jun 27 12 dengan Jun 30 bunga Sales Cost of Sales Gross Margin 51,000 00 (33,637 50) 17,362 50 53,570 40 (35,332 83) 18,237 57 56,270 35 (37,113 60) 19,156 74 58,538 04 (38,609 28) 19,928 76 60,897 13 (40,165 24) 20,731 89 63,351 28 (41,783 90) 21,567 38 65,904 34 (43,467 79) 22,436 55 68,560 28 (45,219 54) 23,340 74 71,323 26 (47,041 89) 24,281 37 74,197 59 (48,937 67) 25,259 91 77,187 75 (50,909 86) 26,277 89 80,298 42 (52,961 53) 27,336 89 83,534 44 (55,095 88) 28,438 56 86,900 88 (57,316 24) 29,584 64 90,402 99 (59,626 09) 30,776 90 Overhead Expenses Sales Marketing Other Total overhead expenses (8,444 80) (2,550 00) (780 00) (11,774 80) (8,870 42) (3,214 22) (811 20) (12,895 84) (9,317 49) (3,938 92) (843 65) (14,100 06) (9,692 98) (4,097 66) (868 96) (14,659 60) (10,083 61) (4 262 80) (895 03) (15,241 43) (10,489 98) (4,434 59) (921 88) (15,846 44) (10,912 72) (4,613 30) (949 53) (16,475 56) (11,352 51) (4,799 22) (978 02) (17,129 75) (11,810 01) (4,992 63) (1,007 36) (17,810 00) (12,285 96) (5,193 83) (1,037 58) (18,517 37) (12,781 08) (5,403 14) (1,068 71) (19,252 93) (13,296 16) (5,620 89) (1,100 77) (20,017 82) (13,831 99) (5,847 41) (1,133 79) (20,813 20) (14,389 42) (6,083 06) (1,167 81) (21,640 29) (14,969 32) (6,328 21) (1,202 84) (22,500 37) EBITDA 5,587 70 5,341 73 5,056 68 5 ,269 16 5,490 46 5,720 94 5,960 99 6,211 00 6,471 37 6,742 55 7,024 96 7,319 07 7,625 37 7,944 35 8 276 53 Depreciation Interest revenue (expense) Profit (Loss) before tax (1,352 00) (360 00) 3,875 70 (1,444 71) 54 46 3,951 48 (1,541 13) (39 00) 3,476 56 (1,640 43) (121 46) 3,507 26 (1,742 72) (175 43) 3,572 30 (3,340 64) (706 57) 1,673 73 (3,449 16) (542 21) 1,969 62 (2 208 94) (596 99) 3,405 08 (2,231 35) (576 48) 3,663 54 (2 253 52) (535 67) 3,953 36 (4 006 64) (903 94) 2,114 38 (4,030 15) (684 40) 2,604 52 (2,561 80) 997 00 6,060 56 (2,586 75) (701 52) 4,656 08 (2,612 44) (619 47) 5,044 62 Income tax benefit (expense) Net Profit (Loss) after tax (1,162 71) 2 712 99 (1,185 44) 2 766 04 (1,042 97) 2 433 59 (1,052 18) 2 455 08 (1,071 69) 2 500 61 (502 12) 1,171 61 (590 89) ,378 73 (1,021 52) 2 383 55 (1,099 06) 2 564 48 (1,186 01) 2 767 35 (634 31) 1,480 07 (781 36) 1 823 17 (1,818 17) 4 242 39 (1,396 82) 3 259 25 (1,513 38) 3,531 23 1 Balance Sheet 0 00 Current Assets Cash at Bank Accounts receivable Inventory Total Current Assets 5 9,911 53 4,598 36 3,860 04 18,369 93 6,938 07 4,843 35 4,065 70 15,847 12 4,349 86 5,087 46 4,270 61 13,707 92 2,568 40 5,292 48 4,442 71 12,303 60 1,416 18 5,490 72 4,609 13 11,516 03 4,461 93 ,727 65 4,808 01 14,997 59 1,813 80 5,958 47 5,001 77 12,774 05 1,207 54 1 160 71 1 214 73 6,198 606,430 796,708 28 5,203 34 5,398 25 5,631 18 1 2,609 48 12,989 74 13,554 19 4 968 14 1,930 84 1,254 97 6,978 62 7,259 86 7,531 79 5,858 126,094 206 ,322 48 1 7,804 88 15,284 90 15,109 24 1,215 99 1 311 54 7,856 79 8 ,173 42 6,595 296,861 08 15,668 08 16,346 05 4 Non Current Assets Plant and equipment Deferred tax asset (depreciation timing differences) Total Non Current Assets 8,112 00 162 24 8,274 24 7,316 25 222 04 7,538 296 6,450 04 194 76 ,644 81 5,504 78 92 39 5,597 17 ,478 07 0 00 4,478 07 12,322 87 9,633 33 8,206 81 0 00 0 00 0 00 12,322 879,633 338,206 81 6,781 35 0 00 6,781 355 5,357 9014,318 25 0 00 0 00 ,357 90 14,318 25 11,168 72 9 0 00 11,168 729 ,513 95 0 00 ,513 95 7,861 44 0 00 7,861 44 6,211 27 0 00 6,211 27 Total Assets 34 918 41 30,923 69 26,997 54 23,497 93 20,472 18 39 643 33 32,040 72 29 023 11 26,552 44 24 269 98 46,441 38 37,622 34 34 137 13 31,390 96 28,768 59 Current Liabilities Accounts payable Revolver borrowings Total Current Liabilities 4,590 86 0 00 4,590 86 4,888 93 0 00 4,888 93 5,191 525,399 865,601 22 0 00 0 00 0 00 5,191 52 5,399 86 5,601 225 5,841 98 0 00 ,841 98 6,076 456,320 34 0 00 0 00 6,076 45 6 320 34 6,556 07 0 00 6 556 07 6,837 917,112 397,397 91 7,673 87 0 00 0 00 0 00 0 00 6 837 91 7 112 397 397 917 673 87 8,003 818 ,325 15 0 00 0 00 8 003 818 325 15 0 Non Current Liabilities Term loans Deferred tax liability (depreciation timing differences) Total Non Current Liabilities 3,800 00 0 00 3,800 00 3,600 00 0 00 3,600 00 3,400 00 0 00 3,400 00 3,200 00 0 00 3,200 00 3,000 00 75 66 3,075 66 7,550 00 122 91 7,672 91 7,100 00 348 12 7,448 12 6,650 00 316 24 6,966 24 6,200 00 381 63 6,581 636 5,750 00 5 24 68 ,274 68 ,700 00 522 24 11,222 24 9,650 00 714 90 10,364 90 8,600 00 6 15 92 9,215 92 7,550 00 641 87 8,191 87 6,500 00 767 81 7,267 81 Total Liabilities 8,390 86 8,488 93 8,591 52 8,599 86 8,676 88 13,514 89 13,524 57 13,286 57 13,137 70 13,112 58 18,334 63 17,762 81 16,889 79 16,195 68 15,592 95 Net Assets 26,527 55 22,434 76 18,406 01 14,898 07 11,795 30 26,128 45 18,516 14 15,736 54 13,414 74 11,157 40 28,106 75 19,859 53 17,247 35 15,195 28 13,175 64 Shareholders Equity Equity Opening balance Equity contribution Closing balance 0 00 2,500 00 2,500 00 2,500 00 0 00 2,500 00 2,500 00 0 00 2,500 00 2,500 00 0 00 2,500 00 2,500 00 0 00 2,500 00 2,500 00 0 00 2,500 00 2,500 00 0 00 2,500 00 2,500 00 0 00 2,500 00 2,500 00 0 00 2,500 00 2,500 00 0 00 2,500 00 2,500 00 0 00 2,500 00 2,500 00 0 00 2,500 00 2,500 00 0 00 2,500 00 2,500 00 0 00 2,500 00 2,500 00 0 00 2,500 00 0 00 Retained Earnings Opening retained earnings Net profit (loss) after tax Distributions to shareholders Closing retained earnings 0 00 2 712 99 (6,607 68) (3,894 69) (3,894 69) (7,865 45) (11,702 55) (14,941 18) 2,967 31 2,687 692,754 312,825 01 (6,938 07) (6,524 79) (5,992 94) (5,664 73) (7,865 45) (11,702 55) (14,941 18) (17,780 90) (17,780 90) 1,505 21 (17,847 74) (34,123 43) (34,123 43) 2,267 18 (7,255 20) (39,111 46) (39,111 46) (40,661 37) (41,887 48) (43,185 50) (60,845 48) (65,492 40) (66,269 88) (66,761 93) 3,280 24 3,416 71 3,560 92 2,212 58 3,076 454,242 394,371 92 4,510 16 (4,830 16) (4,642 82) (4,858 94) (19,872 56) (7,723 37) (5,019 87) (4,863 97) (5,246 18) (40,661 37) (41,887 48) (43,185 50) (60,845 48) (65,492 40) (66,269 88) (66,761 93) (67,497 95) 0 00 Total Equity (1,394 69) (5,365 45) (9,202 55) (12,441 18) (15,280 90) (31,623 43) (36,611 46) (38,161 37) (39,387 48) (40,685 50) (58,345 48) (62,992 40) (63,769 88) (64 261 93) (64 997 95) Question 10 Analyst comment The balance sheet doesn't balance Error(s) in Outputs 27 67 What is the total net as sets ($m) in the June 2030 year under the Base scenario a 8,085 44 b 8,085 55 c d 8,085 66 8,085 77 e 8,085 88 f 8,085 99 M N O R S U V W X AB 1 The Widget Company Inc (USD $m) 2 Inputs E Jun 15 0 Jun 16 Jun 18 Jun 19 Jun 22 Jun 21 6 Jun 17 2 Jun 23 Jun 20 lun 20 5 P Jun 26 11 10 Jun 27 12 Jun 28 13 Jun 29 14 Jun 30 15 14 Keyotes All real values are real as at June 2015 so should be subject to inflation from 2016 Input Headings The Widget Company Inc Currency and units First year of forecast period (USD $m) Jun 16 Jun 15 Jun 20 Jun 21 Timeline Year Counter Jun 16 2016 1 00 Jun 17 2017 2 00 Jun 18 2018 3 00 Jun 19 2019 4 00 2020 2021 Jun 22 2022 7 00 Jun 23 2023 8 00 Jun 24 2024 9 00 Jun 25 2025 10 00 Jun 26 2026 11 00 Jun 27 2027 12 00 Jun 28 2028 13 00 Jun 29 2029 14 00 Jun 30 2030 15 00 0 00 5 00 6 00 Economic Assumptions CPI Applied to sales and admin costs only pa 4 00 4 00 4 00 3 00 3 00 3 00 3 00 3 00 3 00 3 00 3 00 3 00 3 00 3 00 3 00 Taxation Company tax rate ( ) Note this model does not deal with tax losses L 30 00 Revenue Volume Assumptions Sales (m units) Widget A Widget B Widget C Widget D Widget E Widget F Widget G Widget H units ms units ms units ms units ms units ms units ms units ms units ms 100 00 50 00 25 00 60 00 200 00 30 00 75 00 40 00 101 00 50 50 25 25 60 60 202 00 30 30 75 75 40 40 102 01 51 01 25 50 61 21 204 02 30 60 76 51 40 80 103 03 51 52 25 76 61 82 206 06 30 91 77 27 41 21 104 06 52 03 26 02 62 44 208 12 31 22 78 05 41 62 105 10 52 55 26 28 63 06 210 20 31 53 78 83 42 04 106 15 53 08 26 54 63 69 212 30 31 85 79 61 42 46 107 21 53 61 26 80 64 33 214 43 32 16 80 41 42 89 108 29 54 14 27 07 64 97 216 57 32 49 109 37 54 68 27 34 65 62 218 74 32 81 82 03 43 75 110 46 55 23 27 62 66 28 220 92 33 14 82 85 44 18 111 57 55 78 27 89 66 94 223 13 33 47 83 68 44 63 112 68 56 34 28 17 67 61 225 37 33 80 84 51 45 07 113 81 56 90 28 45 68 29 227 62 34 14 85 36 45 52 114 95 57 47 28 74 68 97 229 89 34 48 86 21 45 98 81 21 43 31 Revenue Pricing Assumptions Widget categories Widget A Widget B Widget C Widget D Widget E Widget F Widget G Widget H Category Low value Mid value High value Mid value Low value High value Mid value Mid value Pricing scenario used Base Pricing (nominal) Base Scenario D D Inputs Calculations Outputs U V W Jun 15 Jun 22 M Jun 23 Jun 21 Jun 28 Jun 16 Jun 17 12 Jun 29 Jun 18 3 Jun 30 AB 1 The Widget Company Inc (USD $m) 2 Inputs Pricing scenario used Jun 24 Jun 19 4 Jun 20 5 Jun 25 10 Jun 26 11 Jun 27 12 9 13 15 Base Pricing (nominal) Base Scenario High value Mid value Low value $ unit $ unit $ unit 150 00 110 00 60 00 156 00 114 40 62 40 162 24 118 98 64 90 167 11 122 55 66 84 172 12 126 22 68 85 177 28 130 01 7 0 91 182 60 133 91 73 04 188 08 137 93 75 23 193 72 142 06 77 49 199 53 146 33 79 81 205 52 150 72 82 21 211 69 155 24 84 67 218 04 159 89 87 21 224 58 164 69 89 83 231 32 169 63 92 53 Pricing (nominal) Growth Scenario High value Mid value Low value $ unit $ unit $ unit 180 00 140 00 90 00 187 20 145 60 93 60 194 69 151 42 97 34 200 53 155 97 100 26 206 54 160 65 103 27 212 74 165 47 106 37 219 12 170 43 109 56 225 70 175 54 112 85 232 47 180 81 116 23 239 44 186 23 119 72 246 62 191 82 123 31 254 02 197 57 127 01 261 64 203 50 130 82 269 49 209 61 134 75 277 58 215 89 138 79 Cost Of Sales ( ) High value Mid value Low value 60 00 65 00 70 00 60 00 65 00 70 00 60 00 65 00 70 00 60 00 65 00 70 00 60 00 65 00 70 00 60 00 65 00 70 00 60 00 65 00 70 00 60 00 65 00 70 00 60 00 65 00 70 00 60 00 65 00 70 00 60 00 65 00 70 00 60 00 65 00 70 00 60 00 65 00 70 00 60 00 65 00 70 00 60 00 65 00 70 00 14 00 Overheads Direct sales costs Marketing costs Other (per year) $ per unit of sales $m 5 00 5 00 7 00 7 00 7 00 7 00 7 00 7 00 7 00 7 00 7 00 7 00 7 00 7 00 7 00 750 00 Working Capital Days receivable days Inventory days days Days payable days All working capital calculated on a (days actual) basis 33 00 42 00 37 00 33 00 42 00 37 00 33 00 42 00 37 00 33 00 42 00 37 00 33 00 42 00 37 00 33 00 42 00 37 00 33 00 42 00 37 00 33 00 42 00 37 00 33 00 42 00 37 00 33 00 42 00 37 00 33 00 42 00 37 00 33 00 42 00 37 00 33 00 42 00 37 00 33 00 42 00 37 00 33 00 42 00 37 00 Capital Expenditure One off capex Regular capex $m Sm 8 840 00 624 00 0 00 648 96 0 00 674 92 0 00 695 17 0 00 716 02 10 447 94 737 50 0 00 759 63 0 00 782 42 0 00 805 89 0 00 830 06 12 112 02 854 97 0 00 880 62 0 00 907 03 0 00 934 24 0 00 962 27 Depreciation Rates PP E accounting (straight line) PP E tax (diminishing value) years pa 7 00 20 00 1 Financing Interest rates Cash on hand Term loan 1 interest Term loan 2 interest Term loan 3 interest Revolver facility 4 00 9 00 10 00 11 00 13 00 4 00 9 00 10 00 11 00 13 00 4 00 9 00 10 00 11 00 13 00 4 00 9 00 10 00 11 00 13 00 4 00 9 00 10 00 11 00 13 00 4 00 9 00 10 00 11 00 13 00 4 00 9 00 10 00 11 00 13 00 4 00 9 00 10 00 11 00 13 00 4 00 9 00 10 00 11 00 13 00 4 00 9 00 10 00 11 00 13 00 4 00 9 00 10 00 11 00 13 00 4 00 9 00 10 00 11 00 13 00 4 00 9 00 10 00 11 00 13 00 4 00 9 00 10 00 11 00 13 00 4 00 9 00 10 00 11 00 13 00 Other Term loan 1 drawdown (start of year) Term loan 1 repayment (end of year) Term loan 2 drawdown (start of year) Term loan 2 repayment (end of year) Term loan 3 drawdown (start of year) Term loan 3 repayment (end of year) Equity contribution Equity distributions ( of cash available) 4 000 00 (200 00) 0 00 0 00 0 00 0 00 2 500 00 40 00 0 00 (200 00 0 00 0 00 0 00 0 00 0 00 50 00 0 00 (200 00) 0 00 0 00 0 00 0 00 0 00 60 00 0 00 200 00) 0 00 0 00 0 00 0 00 0 00 70 00 0 00 (200 00) 0 00 0 00 0 00 0 00 0 00 80 00 0 00 (200 00) 5 000 00 (250 00) 0 00 0 00 0 00 80 00 0 00 (200 00 0 00 (250 00 0 00 0 00 0 00 80 00 0 00 (200 00) 0 00 (250 00) 0 00 0 00 0 00 80 00 0 00 (200 00) 0 00 (250 00) 0 00 0 00 0 00 80 00 0 00 (200 00) 0 00 (250 00) 0 00 0 00 0 00 80 00 0 00 (200 00) 0 00 (250 00) 6 000 00 (600 00) 0 00 80 00 0 00 (200 00) 0 00 (250 00) 0 00 (600 00) 0 00 80 00 0 00 (200 00) 0 00 (250 00) 0 00 (600 00) 0 00 80 00 0 00 (200 00) 0 00 (250 00) 0 00 (600 00) 0 00 80 00 80 00 0 00 (200 00) 0 00 (250 00) 0 00 (600 00) 0 00 80 00 heet End N U V W 26 Jun 15 Jun 16 Jun 17 12 Jun 18 Jun 19 Jun 20 45 Jun 21 Jun 22 1 The Widget Company Inc (USD $m) 2 Calculations 3 4 Operations Jun 23 Jun 24 Jun 25 10 Jun 26 11 Jun 27 12 Jun 28 13 Jun 29 14 Jun 30 15 Volumes Sales (m units) Widget A Widget B Widget C Widget D Widget E Widget F Widget G Widget H Total volume Low value Mid value High value Mid value Low value High value Mid value Mid value units ms 100 00 50 00 25 00 60 00 200 00 30 00 75 00 40 00 580 00 101 00 50 50 25 25 60 60 202 00 30 30 75 75 40 40 585 80 102 01 51 01 25 50 61 21 204 02 30 60 76 51 40 80 591 66 103 03 51 52 25 76 61 82 206 06 30 91 77 27 41 21 597 57 104 06 52 03 26 02 62 44 208 12 31 22 78 05 41 62 603 55 105 10 52 55 26 28 63 06 210 20 31 53 78 83 42 04 609 59 106 15 53 08 26 54 63 69 212 30 31 85 79 61 42 46 615 68 107 21 53 61 26 80 64 33 214 43 32 16 80 41 42 89 621 84 108 29 54 14 27 07 64 97 216 57 32 49 81 21 43 31 628 06 109 37 54 68 27 34 65 62 218 74 32 81 82 03 43 75 634 34 110 46 55 23 27 62 66 28 220 92 33 14 82 85 44 18 640 68 111 57 55 78 27 89 66 94 223 13 33 47 83 68 44 63 647 09 112 68 56 34 28 17 67 61 225 37 33 80 84 51 45 07 653 56 113 81 56 90 28 45 68 29 227 62 34 14 85 36 45 52 660 09 114 95 57 47 28 74 68 97 229 89 34 48 86 21 45 98 666 70 High value Mid value Low value units ms units ms units ms 55 00 225 00 300 00 300 00 55 55 227 25 303 00 303 00 56 11 229 52 306 03 306 03 56 67 231 82 309 09 309 09 57 23 234 14 312 18 312 18 57 81 236 48 315 30 315 30 58 38 238 84 318 46 318 46 58 97 241 23 321 64 321 64 59 56 243 64 324 86 324 86 60 15 246 08 328 11 328 11 60 75 248 54 331 39 331 39 61 36 251 03 334 70 334 70 61 98 253 54 338 05 338 05 62 60 256 07 341 43 341 43 63 22 258 63 344 84 344 84 4,829 07 Revenues Pricing scenario used Base Pricing (nominal) Growth Scenario High value Mid value Low value $ unit $ unit $ unit 150 00 110 00 60 00 156 00 114 40 62 40 162 24 118 98 64 90 167 11 122 55 66 84 172 12 126 22 68 85 177 28 130 01 70 91 182 60 133 91 73 04 188 08 137 93 75 23 193 72 142 06 77 49 199 53 146 33 79 81 205 52 150 72 82 21 211 69 155 24 84 67 218 04 159 89 87 21 224 58 164 69 89 83 231 32 169 63 92 53 Sm $m High value Mid value Low value Total revenue 8,250 00 24,750 00 18,000 00 51,000 00 8,665 80 25,997 40 18,907 20 53,570 40 9,102 56 27,307 67 19,860 12 56,270 35 9,469 39 28,408 17 20,660 49 58,538 04 9,851 01 29,553 02 21,493 10 60,897 13 10,248 00 30,744 00 22,359 28 63,351 28 10,661 00 31,982 99 23,260 35 65,904 34 11,090 63 3 3,271 90 24,197 75 68,560 28 11,537 59' 34,612 76' 25,172 92 71,323 26 12,002 55 36,007 65 26,187 38 74,197 59 12,486 25 37,458 76 27,242 74 77,187 75 12,989 45 38,968 35 28,340 62 80,298 42 13,512 92 40,538 77 29,482 74 8 3,534 44 14,057 50 42,172 49 30,670 90 86,900 88 14,624 01 43,872 04 31,906 94 90,402 99 $m Cost of Sales High value Mid value Low value 60 00 65 00 70 00 60 00 65 00 70 00 60 00 65 00 70 00 60 00 65 00 70 00 60 00 65 00 70 00 60 00 65 00 70 00 60 00 65 00 70 00 60 00 65 00 70 00 60 00 65 00 70 00 60 00 65 00 70 00 60 00 65 00 70 00 60 00 65 00 70 00 60 00 65 00 70 00 60 00 65 00 70 00 60 00 65 00 70 00 7 8 High value Mid value Low value Total cost of sales 4,950 00 16,087 50 12,600 00 33,637 50 5,199 48 16,898 31 13,235 04 35,332 83 5,461 53 17,749 98 13,902 09 37,113 60 5,681 63 18,465 31 14,462 34 38,609 28 5,910 60 19,209 46 15,045 17 40,165 24 6,148 80 19,983 60 15,651 49 41,783 90 6,396 60 20,788 94 16,282 25 43,467 79 6,654 38 21,626 74 16,938 42 45,219 54 6,922 55 7,201 53 22,498 29 23,404 97 17,621 04 18,331 17 47,041 8948,937 67 7,491 75 24,348 20 19,069 92 50,909 86 ,793 67 25,329 43 19,838 43 52,961 53 ,107 75 26,350 20 20,637 92 55,095 88 8 ,434 50 27,412 12 21,469 63 57,316 24 8,774 41 28,516 82 22,334 86 59,626 09 Total Sales Sales Cost of sales Gross profit Sm Sm Sm 51,000 00 (33,637 50) 17,362 50 53,570 40 (35,332 83) 18,237 57 56,270 35 (37,113 60) 19 156 74 58,538 04 (38,609 28) 19,928 76 60,897 13 (40,165 24) 20,731 89 63,351 28 (41,783 90) 21,567 38 65,904 34 (43,467 79) 22 436 55 68,560 28 (45,219 54) 23,340 74 71,323 26 (47,041 89) 24,281 37 74,197 59 (48,937 67) 25,259 91 77,187 75 (50,909 86) 26,277 89 80,298 42 83,534 44 (52,961 53) (55,095 88) 27,336 89 28,438 56 86,900 88 (57,316 24) 29,584 64 90,402 99 (59,626 09) 30,776 90 Inputs Calculations Outputs Jun 16 1 G Jun 17 2 H Jun 18 3 Jun 19 4 Jun 20 5 Jun 21 6 Jun 22 7 Jun 23 8 Jun 24 9 Jun 25 10 Jun 26 11 Jun 27 12 Jun 28 13 Jun 29 14 Jun 30 15 4A B 1 The Widget Company Inc (USD $m) 2 Calculations 51 Expense Schedule 53 Sales 54 Overhead expense per unit Sales expense 52 $ per unit 14 00 14 56 8,444 80 $m 15 14 8,870 42 15 75 9,317 49 16 22 ,692 98 16 71 10,083 61 17 21 10,489 98 17 72 10,912 72 18 26 11,352 51 18 80 11,810 01 19 37 1937 12,285 96 19 95 12,781 08 21 16 13,831 99 9 21 80 14,389 42 22 45 14,969 32 13,296 16 of sales Marketing Marketing costs Sales Marketing expense 5 00 51,000 00 2,550 00 6 00 53,570 40 3,214 22 7 00 56,270 35 3,938 92 7 00 58,538 04 4,097 66 7 00 60,897 13 4,262 80 7 00 63,351 28 4,434 59 7 00 65,904 34 4,613 30 7 00 68,560 28 4,799 22 7 00 71,323 26 4,992 63 7 00 74,197 59 5,193 83 7 00 77,187 75 5,403 14 7 00 80,298 42 5,620 89 7 00 83,534 44 5,847 41 7 00 7 00 86,900 88 90,402 99 6,083 066,328 21 Other Other costs Sm 750 00 780 00 811 20 843 65 8 68 96 895 03 921 88 949 53 978 02 1,007 36 1,037 58 1,068 71 1,100 77 1,133 79 1,167 81 1,202 84 64 Total expenses 11 774 80 12 895 84 14,100 06 14 659 60 15,241 43 15 846 44 16,475 56 17,129 75 17,810 00 18,517 37 19 252 93 20 017 82 20 813 20 21 640 29 22 500 37 66 67 Working capital 68 Days in year 366 00 365 00 365 00 365 00 366 00 3 65 00 365 00 365 00 366 00 365 00 365 00 365 00 366 00 365 00 365 00 Current Assets Sales Days receivable Accounts receivable $m days Sm 51,000 00 33 00 4,598 36 53,570 40 33 00 4,843 35 56,270 35 33 00 5,087 46 58,538 04 33 00 5,292 48 60,897 13 33 00 5,490 72 63,351 28 33 00 5,727 65 65,904 3468,560 28 33 00 33 00 5,958 476,198 60 71,323 26 33 00 6,430 79 74,197 59 33 00 6,708 28 77,187 75 33 00 6,978 62 80,298 42 33 00 7,259 86 83,534 44 33 00 7,531 79 86,900 88 33 00 7,856 79 90,402 99 33 00 8,173 42 Cost of sales Inventory days Inventory $m days Sm 33,637 50 42 00 3,860 04 35,332 83 42 00 4,065 70 37,113 60 42 00 4,270 61 38,609 28 42 00 4,442 71 40,165 24 42 00 4,609 13 41,783 90 42 00 4,808 01 43,467 79 45,219 54 47,041 89 42 00 42 00 42 00 5,001 775,203 345,398 25 48,937 67 42 00 5,631 18 50,909 86 52,961 53 55,095 88 42 00 42 00 42 00 5,858 126,094 206,322 48 57,316 24 59,626 09 42 00 42 00 6,595 296,861 08 Current Liabilities Cost of sales Total expenses Days payable Accounts payable $m $m days 33,637 50 11,774 80 37 00 4,590 86 83 84 35,332 83 12,895 84 37 00 4,888 93 37,113 60 14,100 06 37 00 5,191 52 38,609 28 14,659 60 37 00 5,399 86 40,165 24 15,241 43 37 00 41,783 90 15,846 44 37 00 5,841 98 43,467 7945,219 54 16,475 56 1 7,129 75 37 00 37 00 6,076 45 6 ,320 34 47,041 8948,937 67 17,810 00 18,517 37 37 00 37 00 6,556 07 6,837 91 50,909 86 19,252 93 37 00 7,112 39 52,961 53 20,017 82 37 00 7,397 91 55,095 88 20,813 20 37 00 7,673 87 57,316 24 21,640 29 37 00 8,003 81 59,626 09 22,500 37 37 00 8 ,325 15 Sm 85 86 87 Net Working Capital Networking capital Change in net working capital 0 00 88 3,867 54 (3,867 54) 4,020 11 (152 57) 4,166 54 (146 43) 4,335 33 (168 79) 4,498 63 (163 30) 4,693 68 (195 05) 4,883 80 (190 12) 5,081 61 (197 81) 5,272 97 (191 36) 5,501 55 (228 58) 5,724 35 (222 80) 5,956 156 (231 81) ,180 406 (224 25) ,448 27 (267 87) 6,709 36 (261 08) 89 U V W A B 1 The Widget Company Inc (USD $m) 2 Calculations 90 PP E Jun 16 1 Jun 17 2 Jun 18 3 Jun 19 4 Jun 20 5 Jun 21 6 Jun 22 7 M Jun 23 8 N Jun 24 9 O Jun 25 10 P Jun 26 11 Q Jun 27 12 R Jun 28 13 Jun 29 14 Jun 30 15 91 93 Accounting Depreciation Opening accounting net book value 0 00 8,112 00 7,316 25 5,504 78 4,478 07 12,322 87 9,633 33 8,206 81 6,781 355,357 90 14,318 25 11,168 72 9,513 95 7,861 44 0 00 96 One off capex Regular capex Capital expenditure incurred (nominal) $m $m $m 8,840 00 624 00 9,464 00 0 00 648 96 648 96 674 92 674 92 0 00 695 17 695 17 0 00 716 02 716 02 10,447 94 7 37 50 11,185 44 0 00 759 63 759 63 0 00 782 42 782 42 0 00 805 89 805 89 0 00 830 06 830 06 12,112 02 854 97 12,966 99 0 00 880 62 880 62 0 00 907 03 907 03 0 00 934 24 934 24 0 00 962 27 962 27 98 PP E accounting (straight line) years Capital expenditure assumed to be made on the first day of the period Year 100 101 102 103 104 105 106 107 0 00 92 71 96 42 99 31 102 29 1,597 92 108 52 111 77 108 109 110 1,352 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 1,352 00 1,352 00 92 71 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 1,444 71 1,352 00 92 71 96 42 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 1,541 13 1,352 00 92 71 96 42 99 31 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 1,640 43 1,352 00 92 71 96 42 99 31 102 29 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 1,742 72 1,352 00 92 71 96 42 99 31 102 29 1,597 92 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 3,340 64 1,352 00 92 71 96 42 99 31 102 29 1,597 92 108 52 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 3,449 16 0 00 0 00 96 42 99 31 102 29 1,597 92 108 52 111 77 115 13 0 00 0 00 0 00 0 00 0 00 0 00 2,231 35 0 00 0 00 0 00 99 31 102 29 1,597 92 108 52 111 77 115 13 118 58 0 00 0 00 0 00 0 00 0 00 2,253 52 0 00 0 00 0 00 0 00 102 29 1,597 92 108 52 111 77 115 13 118 58 1,852 43 0 00 0 00 0 00 0 00 4,006 64 0 00 0 00 0 00 0 00 0 00 1,597 92 108 52 111 77 115 13 118 58 1,852 43 125 80 0 00 0 00 0 00 4,030 15 0 00 0 00 0 00 0 00 0 00 0 00 108 52 111 77 115 13 118 58 1,852 43 125 80 129 58 0 00 0 00 2,561 80 0 00 0 00 0 00 0 00 0 00 0 00 0 00 111 77 115 13 118 58 1,852 43 125 80 129 58 133 46 0 00 2,586 75 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 115 13 118 58 1,852 43 125 80 129 58 133 46 137 47 2,612 44 111 0 00 112 113 114 115 0 00 0 00 0 00 0 00 0 00 0 00 2,208 94 0 00 8 112 00 7 316 25 6 450 04 5 504 78 4 478 07 12,322 87 9 633 33 8 206 81 6,781 35 5 ,357 90 14,318 25 11,168 72 9 513 95 7 861 44 6 211 27 9 7 0 00 9,464 00 1,892 80 7,571 20 116 117 118 Accounting depreciation 119 120 Closing accounting net book value 121 122 Taxation Depreciation 123 124 Opening tax net book value 125 Capital expenditure incurred 126 Tax depreciation 127 Closing tax net book value 1281 129 Financing 130 131 Term loans 132 133 Term loan 1 134 Opening balance 135 Term loan 1 drawdown (start of year) 136 Term loan 1 repayment (end of year) 137 Ending balance 7,571 20 648 96 1,644 03 6,576 13 6,576 13 674 92 1,450 21 5,800 84 5,800 84 695 17 ,299 20 5,196 80 5,196 80 716 02 1,182 56 4 730 26 4,730 26 11,185 44 3,183 14 12,732 56 12,732 56 759 63 2,698 44 10,793 75 10,793 75 7 82 42 2,315 23 9 260 93 ,260 93 8 05 89 2,013 36 8,053 46 8,053 46 830 06 1,776 70 7 106 82 ,106 82 12,966 99 4,014 76 16,059 05 16,059 05 880 62 3,387 93 13,551 73 13,551 73 907 03 2,891 75 11,567 01 11,567 01 934 24 2,500 25 10 001 00 10,001 00 962 27 ,192 66 34,443 04 8,770 62 1 2 0 00 0 00 4,000 00 (200 00) 3,800 00 3,800 00 0 00 (200 00) 3,600 00 3,600 00 0 00 (200 00) 3,400 00 3,400 00 0 00 (200 00) 3,200 00 3,200 00 0 00 (200 00) 3,000 00 3,000 00 0 00 (200 00) 2,800 00 2,800 00 0 00 (200 00) 2,600 00 2,600 00 0 00 (200 00) 2,400 00 2,400 00 0 00 (200 00) 2,200 00 2,200 00 0 00 (200 00) 2,000 00 2,000 00 0 00 (200 00) 1,800 00 1,800 00 0 00 (200 00) 1,600 00 1,600 00 0 00 (200 00) 1,400 00 1,400 00 0 00 (200 00) 1,200 00 1,200 00 0 00 (200 00) 1,000 00 0 00 K U V W Jun 15 Jun 16 1 Jun 17 2 Jun 18 3 Jun 19 4 Jun 20 5 Jun 21 6 Jun 22 7 M Jun 23 8 N Jun 24 9 O Jun 25 10 P Jun 26 11 Q Jun 27 12 R Jun 28 13 S Jun 29 14 Jun 30 15 LA B 1 The Widget Company Inc (USD $m) 2 Calculations 129 Financing 130 131 Term loans 132 Term loan 1 Opening balance 135 Term loan 1 drawdown (start of year) 136 Term loan 1 repayment (end of year) Ending balance 133 0 00 4,000 00 (200 00) 3,800 00 3,800 00 0 00 (200 00) 3,600 00 3,600 00 0 00 (200 00) 3,400 00 3,400 00 0 00 (200 00) 3,200 00 3,200 00 0 00 (200 00) 3,000 00 3,000 00 0 00 (200 00) 2,800 00 2,800 00 0 00 (200 00) 2,600 00 2,600 00 0 00 (200 00) 2,400 00 2,400 00 0 00 (200 00) 2,200 00 2,200 00 0 00 (200 00) 2,000 00 2,000 00 0 00 (200 00) 1,800 00 1,800 00 0 00 (200 00) 1,600 00 1,600 00 0 00 (200 00) 1,400 00 1,400 00 0 00 (200 00) 1,200 00 1,200 00 0 00 (200 00) 1,000 00 0 00 Term loan 2 Opening balance Term loan 2 drawdown (start of year) Term loan 2 repayment (end of year) Ending balance 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 5,000 00 (250 00) 4,750 00 4,750 00 0 00 (250 00) 4,500 00 4,500 00 0 00 (250 00) 4,250 00 4,250 00 0 00 (250 00) 4,000 00 4,000 00 0 00 (250 00) 3,750 00 3,750 00 0 00 (250 00) 3,500 00 3,500 00 0 00 (250 00) 3,250 00 3,250 00 0 00 (250 00) 3,000 00 3,000 00 0 00 (250 00) 2,750 00 2,750 00 0 00 (250 00) 2,500 00 3,600 00 147 Term loan 3 Opening balance Term loan 3 drawdown (start of year) Term loan 3 repayment (end of year) Ending balance 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 6,000 00 (600 00) 5,400 00 5,400 00 0 00 (600 00) 4,800 00 4,800 00 0 00 (600 00) 4 200 00 0 00 4,200 00 0 00 (600 00) 3,600 00 149 (600 00) 3,000 00 Free Cash Flows Opening cash Cash Flows from Operating Activities Cash Flows from Investing Activities Free Cash Flow Available (Required) 156 0 00 9,911 53 7,124 97 4,613 382,814 171,585 834,619 77 755 213,889 453,933 574,272 02 4,598 955 ,777 595 ,947 41 9,464 00 648 96 674 92 695 17 716 02 11,185 44 759 63 10 219 21 14 449 94 11,733 45 9 580 57 8 129 14 18,548 86 11,326 81 2,175 36 5,556 76 782 42 ,514 54 1,612 91 5,822 83 8 05 89 8 241 63 1,558 33 6,006 67 830 06 8,395 06 1,589 01 7,069 35 12,966 99 21,625 35 5,315 07 7 182 97 880 62 13,378 65 2,465 73 4,486 96 907 03 7 859 73 1,361 95 1,650 66 7,007 137,247 47 9 34 24 962 27 9,303 32 9 860 41 8 159 0 00 Financing Cash Flows Equity contribution Term loan drawdown repayment Scheduled financing cash flows 2,500 00 3,800 00 6,300 00 0 00 (200 00) (200 00) 0 00 (200 00) (200 00) (200 00) (200 00) 0 00 (200 00) (200 00) 0 00 4,550 00 4,550 00 0 00 (450 00) (450 00) 0 00 (450 00) (450 00) 0 00 (450 00) (450 00) 0 00 (450 00) (450,00) 0 00 4,950 00 4,950 00 0 00 (1,050 00) (1,050 00) 0 00 (1,050 00) (1,050 00) 0 00 (1,050 00) (1 050 00) 0 00 (1,050 00) (1 050 00) 162 163 164 Cash Flow Available (Required) 16,519 21 14,249 94 11,533 45 9,380 57 7 ,929 14 23,098 86 10,876 81 8 ,064 54 7,791 63 7,945 0626,575 35 12,328 65 6,809 73 8,253 32 8,810 41 0 00 Revolver Opening balance Borrowings Payments Ending balance 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 168 169 170 171 172 173 174 Equity Distribution to shareholders 6,607,68 7,124 97 6,920 076 ,566 40 6,343 31 18,479 09 8 ,701 45 6 ,451 63 6,233 30 6,356 05 21,260 28 9,862 92 5,447 78 6,602 66 7,048 33 U V W Jun 15 G Jun 17 2 H Jun 18 3 I Jun 19 4 J Jun 20 5 K Jun 21 6 L Jun 22 7 M Jun 23 8 N Jun 24 9 O Jun 25 10 P Jun 26 11 Q Jun 27 12 R Jun 28 13 S Jun 29 14 Jun 30 15 179 180 181 9,911 53 4 00 396 46 7,124 97 4 00 285 00 4,613 38 4 00 184 54 2,814 171,585 834,619 77 4 00 4 00 4 00 112 57 63 43 184 79 2,175 36 4 00 87 01 1,612 91 4 00 64 52 1,558 33 4 00 62 33 1,589 01 4 00 63 56 5,315 07 4 00 212 60 2,465 73 4 00 98 63 1 361 95 4 00 54 48 1 650 66 4 00 66 03 F 1 The Widget Company Inc (USD $m) Jun 16 2 Calculations 1 174 175 Interest 176 177 Cash on hand 178 Assume calculation based on opening balance Opening cash 0 00 Cash on hand 4 00 Interest revenue 0 00 182 Term loan 1 184 Assumes drawdowns happen at the start of the year, repayment at the end of the year 185 Opening balance 0 00 186 Term loan 1 drawdown (start of year) 4,000 00 187 Term loan 1 interest 9 00 188 Interest expense (360 00) 189 190 Term loan 2 191 Assumes drawdowns happen at the start of the year, repayment at the end of the year Opening balance 0 00 193 Term loan 2 drawdown (start of year) 0 00 194 Term loan 2 interest 10 00 Interest expense 0 00 183 3,800 00 0 00 9 00 (342 00) 3,600 00 0 00 9 00 (324 00) 3,400 00 0 00 9 00 (306 00) 3,200 00 0 00 9 00 (288 00) 3,000 00 0 00 9 00 (270 00) 2,800 00 0 00 9 00 (252 00) 2,600 00 0 00 9 00 (234 00) 2,400 00 0 00 9 00 (216 00) 2,200 00 0 00 9 00 (198 00) 2,000 00 0 00 9 00 (180 00) 1,800 00 0 00 9 00 (162 00) 1,600 00 0 00 9 00 144 00 1,400 00 0 00 9 00 (126 00) 1,200 00 0 00 9 00 (108 00) 192 0 00 4,500 00 4,000 00 0 00 0 00 0 00 0 00 10 00 0 00 0 00 0 00 10 00 0 00 0 00 0 00 10 00 0 00 0 00 10 00 0 00 5,000 00 10 00 (500 00) 4,750 00 0 00 10 00 (475 00) 4,250 00 0 00 10 00 (425 00) 3,750 00 0 00 10 00 (375 00) 3,500 00 0 00 10 00 (350 00) 3,250 00 0 00 10 00 325 00 3,000 00 0 00 10 00 (300 00) 2,750 00 0 00 10 00 (275 00) 195 10 00 (450 00) 0 00 10 00 (400 00) 0 00 Term loan 3 Assumes drawdowns happen at the start of the year, repayment at the end of the year Opening balance 0 00 Term loan 3 drawdown (start of year) 0 00 Term loan 3 interest 11 00 Interest expense 0 00 0 00 0 00 11 00 0 00 0 00 0 00 11 00 0 00 0 00 0 00 11 00 0 00 0 00 11 00 0 00 0 00 0 00 11 00 0 00 0 00 0 00 11 00 0 00 0 00 0 00 11 00 0 00 0 00 0 00 11 00 0 00 0 00 0 00 11 00 0 00 6,000 00 11 00 (412 50) 5,400 00 0 00 11 00 (385 00) 4,800 00 0 00 11 00 528 00 4,200 00 0 00 11 00 (330 00) 3,600 00 0 00 11 00 (302 50) 0 00 Revolver facility Assume calculation based on opening balance Opening balance Revolver facility Interest expense 0 00 13 00 0 00 0 00 13 00 0 00 0 00 13 00 0 00 0 00 13 00 0 00 0 00 13 00 0 00 0 00 13 00 0 00 0 00 13 00 0 00 0 00 13 00 0 00 0 00 13 00 0 00 0 00 13 00 0 00 0 00 13 00 0 00 0 00 13 00 0 00 0 00 13 00 0 00 0 00 13 00 0 00 0 00 13 00 0 00 209 210 211 Interest revenue (expense) (360 00) 54 46 (39 00) (121 46) (175 43) (706 57) (542 21) (596 99) (576 48) (535 67) (903 94) (684 40) 997 00 (701 52) (619 47) U V W E Jun 15 Jun 16 Jun 22 M Jun 23 A B 1 The Widget Company Inc (USD $m) 2 Calculations 211 212 Tax Jun 17 2 Jun 18 Jun 19 34 Jun 20 5 Jun 21 6 Jun 24 9 0 Jun 25 10 Jun 26 11 Q Jun 27 12 R Jun 28 13 Jun 29 14 Jun 30 15 213 30 00 Company tax rate ( ) Note this model does not deal with tax losses 214 215 216 217 218 P L tax Profit (Loss) before tax 3,875 70 3,951 48 3,476 56 3,507 26 3,572 30 1,673 73 1,969 623,405 08 3,663 54 3,953 36 2,114 382,604 526,060 56 4,656 08 5,044 62 219 Income tax benefit (expense) (1,162 71) (1,185 44) (1,042 97) (1,052 18) (1,071 69) (502 12) (590 89) (1,021 52) (1,099 06) (1,186 01) (634 31) (781 36) (1,818 17) (1,396 82) (1,513 38) 220 221 222 223 224 225 Adjustments to arrive at taxable profit Adjustment for timing differences in depreciation Accounting depreciation Tax depreciation Timing difference (1,352 00) (1,892 80) 540 80 (1,444 71) (1,644 03) 199 32 (1,541 13) (1,450 21) (90 92) (1,640 43) (1,299 20) (341 23) (1,742 72) (1,182 56) (560 16) (3,340 64) (3,183 14) (157 50) (3,449 16) (2,698 44) (750 72) (2,208 94) (2,315 23) 106 30 (2,231 35) (2,013 36) (217 99) (2,253 52) (1,776 70) (476 81) (4,006 64) (4,014 76) 8 13 (4,030 15) (3,387 93) (642 22) (2,561 80) (2,891 75) 329 95 (2,586 75) (2,500 25) (86 50) (2,612 44) (2,192 66) (419 79) 226 227 228 Adjusted profit before tax Tax payable 4,416 50 (1,324 95) 4 150 80 (1,245 24) 3,385 64 (1,015 69) 3,166 02 (949 81) 3,012,14 (903 64) 1,516 23 (454 87) 1,218 89 (365 67) 3,511 37 (1,053 41) 3,445 55 (1,033 66) 3,476 55 (1,042 96) 2,122 51 (636 75) 1,962 31 (588 69) 6,390,51 (1,917 15) 4,569 58 (1,370 87) 4,624 83 (1,387 45) 230 231 232 233 234 Depreciation timing differences Opening balance Timing difference Closing balance 0 00 540 80 540 80 540 80 199 32 740 12 7 40 12 (90 92) 649 21 649 21 (341 23) 307 97 307 97 (560 16) (252 19) (252 19) (157 50) (409 69) (409 69) (750 72) (1,160 41) (1,160 41) 106 30 (1,054 12) (1,054 12) (217 99) (1,272 11) (1,272 11) (476 81) (1,748 92) (1,748 92) 8 13 (1,740 79) (1,740 79) (642 22) (2,383 01) (2,383 01) 329 95 (2,053 06) (2,053 06) (86 50) (2,139 56) (2,139 56) (419 79) (2,559 35) 0 00 235 236 (162 24) (222 04) (194 76) (92 39) 75 66 122 91 22 91 348 12 348 12 316 24 316 24 381 63 524 68 524 68 522 24 522 24 714 90 714 90 6 15 92 615 92 641 87 767 81 222 04 0 00 Deferred tax assetliability (depreciation timing differences) 237 238 Deferred tax asset (depreciation timing differences) 239 Deferred tax liability (depreciation timing differences) 240 241 Sheet End 242 162 24 0 00 0 00 194 76 0 00 92 39 0 00 0 00 75 66 0 00 122 91 0 00 348 12 0 00 316 24 0 00 381 63 0 00 524 68 0 00 522 24 0 00 714 90 0 00 615 92 641 87 0 00 767 81 U V W sunny leon Jun 16 Jun 15 H Jun 18 elementele pe lumen Jun 17 Jun 19 dengan M Jun 23 Jun 20 tempat Jun 21 N Jun 24 bermain Jun 22 AB 1 The Widget Company Inc (USD $m) 2 Outputs 3 4 Profit and Loss luas Jun 25 dan luar negeringen Jun 26 Jun 28 som heter Jun 29 Jun 27 12 dengan Jun 30 bunga Sales Cost of Sales Gross Margin 51,000 00 (33,637 50) 17,362 50 53,570 40 (35,332 83) 18,237 57 56,270 35 (37,113 60) 19,156 74 58,538 04 (38,609 28) 19,928 76 60,897 13 (40,165 24) 20,731 89 63,351 28 (41,783 90) 21,567 38 65,904 34 (43,467 79) 22,436 55 68,560 28 (45,219 54) 23,340 74 71,323 26 (47,041 89) 24,281 37 74,197 59 (48,937 67) 25,259 91 77,187 75 (50,909 86) 26,277 89 80,298 42 (52,961 53) 27,336 89 83,534 44 (55,095 88) 28,438 56 86,900 88 (57,316 24) 29,584 64 90,402 99 (59,626 09) 30,776 90 Overhead Expenses Sales Marketing Other Total overhead expenses (8,444 80) (2,550 00) (780 00) (11,774 80) (8,870 42) (3,214 22) (811 20) (12,895 84) (9,317 49) (3,938 92) (843 65) (14,100 06) (9,692 98) (4,097 66) (868 96) (14,659 60) (10,083 61) (4 262 80) (895 03) (15,241 43) (10,489 98) (4,434 59) (921 88) (15,846 44) (10,912 72) (4,613 30) (949 53) (16,475 56) (11,352 51) (4,799 22) (978 02) (17,129 75) (11,810 01) (4,992 63) (1,007 36) (17,810 00) (12,285 96) (5,193 83) (1,037 58) (18,517 37) (12,781 08) (5,403 14) (1,068 71) (19,252 93) (13,296 16) (5,620 89) (1,100 77) (20,017 82) (13,831 99) (5,847 41) (1,133 79) (20,813 20) (14,389 42) (6,083 06) (1,167 81) (21,640 29) (14,969 32) (6,328 21) (1,202 84) (22,500 37) EBITDA 5,587 70 5,341 73 5,056 68 5 ,269 16 5,490 46 5,720 94 5,960 99 6,211 00 6,471 37 6,742 55 7,024 96 7,319 07 7,625 37 7,944 35 8 276 53 Depreciation Interest revenue (expense) Profit (Loss) before tax (1,352 00) (360 00) 3,875 70 (1,444 71) 54 4

Question

M N O R S U V W X AB 1 The Widget Company Inc (USD $m) 2 Inputs E Jun 15 0 Jun 16 Jun 18 Jun 19 Jun 22 Jun 21 6 Jun 17 2 Jun 23 Jun 20 lun 20 5 P Jun 26 11 10 Jun 27 12 Jun 28 13 Jun 29 14 Jun 30 15 14 Keyotes All real values are real as at June 2015 so should be subject to inflation from 2016 Input Headings The Widget Company Inc Currency and units First year of forecast period (USD $m) Jun 16 Jun 15 Jun 20 Jun 21 Timeline Year Counter Jun 16 2016 1 00 Jun 17 2017 2 00 Jun 18 2018 3 00 Jun 19 2019 4 00 2020 2021 Jun 22 2022 7 00 Jun 23 2023 8 00 Jun 24 2024 9 00 Jun 25 2025 10 00 Jun 26 2026 11 00 Jun 27 2027 12 00 Jun 28 2028 13 00 Jun 29 2029 14 00 Jun 30 2030 15 00 0 00 5 00 6 00 Economic Assumptions CPI Applied to sales and admin costs only   pa 4 00  4 00  4 00  3 00  3 00  3 00  3 00  3 00  3 00  3 00  3 00  3 00  3 00  3 00  3 00  Taxation Company tax rate ( ) Note   this model does not deal with tax losses L 30 00  Revenue Volume Assumptions Sales (m units) Widget A Widget B Widget C Widget D Widget E Widget F Widget G Widget H units ms units ms units ms units ms units ms units ms units ms units ms 100 00 50 00 25 00 60 00 200 00 30 00 75 00 40 00 101 00 50 50 25 25 60 60 202 00 30 30 75 75 40 40 102 01 51 01 25 50 61 21 204 02 30 60 76 51 40 80 103 03 51 52 25 76 61 82 206 06 30 91 77 27 41 21 104 06 52 03 26 02 62 44 208 12 31 22 78 05 41 62 105 10 52 55 26 28 63 06 210 20 31 53 78 83 42 04 106 15 53 08 26 54 63 69 212 30 31 85 79 61 42 46 107 21 53 61 26 80 64 33 214 43 32 16 80 41 42 89 108 29 54 14 27 07 64 97 216 57 32 49 109 37 54 68 27 34 65 62 218 74 32 81 82 03 43 75 110 46 55 23 27 62 66 28 220 92 33 14 82 85 44 18 111 57 55 78 27 89 66 94 223 13 33 47 83 68 44 63 112 68 56 34 28 17 67 61 225 37 33 80 84 51 45 07 113 81 56 90 28 45 68 29 227 62 34 14 85 36 45 52 114 95 57 47 28 74 68 97 229 89 34 48 86 21 45 98 81 21 43 31 Revenue Pricing Assumptions Widget categories Widget A Widget B Widget C Widget D Widget E Widget F Widget G Widget H Category Low value Mid value High value Mid value Low value High value Mid value Mid value Pricing scenario used Base Pricing (nominal)   Base Scenario D D Inputs Calculations Outputs   U V W Jun 15 Jun 22 M Jun 23 Jun 21 Jun 28 Jun 16 Jun 17 12 Jun 29 Jun 18 3 Jun 30 AB 1 The Widget Company Inc (USD $m) 2 Inputs Pricing scenario used Jun 24 Jun 19 4 Jun 20 5 Jun 25 10 Jun 26 11 Jun 27 12 9 13 15 Base Pricing (nominal)   Base Scenario High value Mid value Low value $   unit $   unit $   unit 150 00 110 00 60 00 156 00 114 40 62 40 162 24 118 98 64 90 167 11 122 55   66 84 172 12 126 22 68 85 177 28 130 01 7 0 91 182 60 133 91 73 04 188 08  137 93 75 23 193 72 142 06  77 49 199 53   146 33 79 81 205 52 150 72 82 21 211 69 155 24 84 67 218 04 159 89 87 21 224 58 164 69 89 83 231 32 169 63 92 53 Pricing (nominal)   Growth Scenario High value Mid value Low value $   unit $   unit $   unit 180 00 140 00 90 00 187 20 145 60 93 60 194 69 151 42 97 34 200 53 155 97 100 26 206 54 160 65 103 27 212 74 165 47   106 37 219 12 170 43 109 56 225 70 175 54 112 85 232 47   180 81 116 23 239 44 186 23 119 72 246 62 191 82 123 31 254 02 197 57 127 01 261 64 203 50 130 82 269 49 209 61 134 75 277 58 215 89 138 79 Cost Of Sales ( ) High value Mid value Low value 60 00  65 00  70 00  60 00  65 00  70 00  60 00  65 00  70 00  60 00  65 00  70 00  60 00  65 00  70 00  60 00  65 00  70 00  60 00  65 00  70 00  60 00  65 00  70 00  60 00  65 00  70 00  60 00  65 00  70 00  60 00  65 00  70 00  60 00  65 00  70 00  60 00  65 00  70 00  60 00  65 00  70 00  60 00  65 00  70 00  14 00 Overheads Direct sales costs Marketing costs Other (per year) $ per unit   of sales $m 5 00  5 00  7 00  7 00  7 00  7 00  7 00  7 00  7 00  7 00  7 00  7 00  7 00  7 00  7 00  750 00 Working Capital Days receivable days Inventory days days Days payable days All working capital calculated on a (days  actual) basis 33 00 42 00 37 00 33 00 42 00 37 00 33 00 42 00 37 00 33 00 42 00 37 00 33 00 42 00 37 00 33 00 42 00 37 00 33 00 42 00 37 00 33 00 42 00 37 00 33 00 42 00 37 00 33 00 42 00 37 00 33 00 42 00 37 00 33 00 42 00 37 00 33 00 42 00 37 00 33 00 42 00 37 00 33 00 42 00 37 00 Capital Expenditure One off capex Regular capex $m Sm 8 840 00 624 00 0 00 648 96 0 00 674 92 0 00 695 17 0 00 716 02 10 447 94 737 50 0 00 759 63 0 00 782 42 0 00 805 89 0 00 830 06  12 112 02 854 97 0 00 880 62 0 00 907 03 0 00 934 24 0 00 962 27 Depreciation Rates PP E   accounting (straight line) PP E   tax (diminishing value) years   pa 7 00 20 00  1 Financing Interest rates Cash on hand Term loan 1 interest Term loan 2 interest Term loan 3 interest Revolver facility 4 00  9 00  10 00  11 00  13 00  4 00  9 00  10 00  11 00  13 00  4 00  9 00  10 00  11 00  13 00  4 00  9 00  10 00  11 00  13 00  4 00  9 00  10 00  11 00  13 00  4 00  9 00  10 00  11 00  13 00  4 00  9 00  10 00  11 00  13 00  4 00  9 00  10 00  11 00  13 00  4 00  9 00  10 00  11 00  13 00  4 00  9 00  10 00  11 00  13 00  4 00  9 00  10 00  11 00  13 00  4 00  9 00  10 00  11 00  13 00  4 00  9 00  10 00  11 00  13 00  4 00  9 00  10 00  11 00  13 00  4 00  9 00  10 00  11 00  13 00  Other Term loan 1 drawdown (start of year) Term loan 1 repayment (end of year) Term loan 2 drawdown (start of year) Term loan 2 repayment (end of year) Term loan 3 drawdown (start of year) Term loan 3 repayment (end of year) Equity contribution Equity distributions (  of cash available) 4 000 00 (200 00) 0 00 0 00 0 00 0 00 2 500 00 40 00  0 00 (200 00 0 00 0 00 0 00 0 00 0 00 50 00  0 00 (200 00) 0 00 0 00 0 00 0 00 0 00 60 00  0 00 200 00) 0 00 0 00 0 00 0 00 0 00 70 00  0 00 (200 00) 0 00 0 00 0 00 0 00 0 00 80 00  0 00 (200 00) 5 000 00 (250 00) 0 00 0 00 0 00 80 00  0 00 (200 00 0 00 (250 00 0 00 0 00 0 00 80 00  0 00 (200 00) 0 00 (250 00) 0 00 0 00 0 00 80 00  0 00 (200 00) 0 00 (250 00) 0 00 0 00 0 00 80 00  0 00 (200 00) 0 00 (250 00) 0 00 0 00 0 00 80 00  0 00 (200 00) 0 00 (250 00) 6 000 00 (600 00) 0 00 80 00  0 00 (200 00) 0 00 (250 00) 0 00 (600 00) 0 00 80 00  0 00 (200 00) 0 00 (250 00) 0 00 (600 00) 0 00 80 00  0 00 (200 00) 0 00 (250 00) 0 00 (600 00) 0 00 80 00  80 00  0 00 (200 00) 0 00 (250 00) 0 00 (600 00) 0 00 80 00  heet End N U V W 26 Jun 15 Jun 16 Jun 17 12 Jun 18 Jun 19 Jun 20 45 Jun 21 Jun 22 1 The Widget Company Inc (USD $m) 2 Calculations 3 4 Operations Jun 23 Jun 24 Jun 25 10 Jun 26 11 Jun 27 12 Jun 28 13 Jun 29 14 Jun 30 15 Volumes Sales (m units) Widget A Widget B Widget C Widget D Widget E Widget F Widget G Widget H Total volume Low value Mid value High value Mid value Low value High value Mid value Mid value units ms 100 00 50 00 25 00 60 00 200 00 30 00 75 00 40 00 580 00 101 00 50 50 25 25 60 60 202 00 30 30 75 75 40 40 585 80 102 01 51 01 25 50 61 21 204 02 30 60 76 51 40 80 591 66 103 03 51 52 25 76 61 82 206 06 30 91 77 27 41 21 597 57 104 06 52 03 26 02 62 44 208 12 31 22 78 05 41 62 603 55 105 10 52 55 26 28 63 06 210 20 31 53 78 83 42 04 609 59 106 15 53 08 26 54 63 69 212 30 31 85 79 61 42 46 615 68 107 21 53 61 26 80 64 33 214 43 32 16 80 41 42 89 621 84 108 29 54 14 27 07 64 97 216 57 32 49 81 21 43 31 628 06 109 37 54 68 27 34 65 62 218 74 32 81 82 03 43 75 634 34 110 46 55 23 27 62 66 28 220 92 33 14 82 85 44 18 640 68 111 57 55 78 27 89 66 94 223 13 33 47 83 68 44 63 647 09 112 68 56 34 28 17 67 61 225 37 33 80 84 51 45 07 653 56 113 81 56 90 28 45 68 29 227 62 34 14 85 36 45 52 660 09 114 95 57 47 28 74 68 97 229 89 34 48 86 21 45 98 666 70 High value Mid value Low value units ms units ms units ms 55 00 225 00 300 00 300 00 55 55 227 25 303 00 303 00 56 11 229 52 306 03 306 03 56 67 231 82 309 09 309 09 57 23 234 14 312 18 312 18 57 81 236 48 315 30 315 30 58 38 238 84 318 46 318 46 58 97 241 23 321 64 321 64 59 56 243 64 324 86 324 86 60 15 246 08 328 11 328 11 60 75 248 54 331 39 331 39 61 36 251 03 334 70 334 70 61 98 253 54 338 05 338 05 62 60 256 07 341 43 341 43 63 22 258 63 344 84 344 84 4,829 07 Revenues Pricing scenario used Base Pricing (nominal)   Growth Scenario High value Mid value Low value $ unit $ unit $ unit 150 00 110 00 60 00 156 00 114 40 62 40 162 24 118 98 64 90 167 11 122 55 66 84 172 12 126 22 68 85 177 28 130 01 70 91 182 60 133 91 73 04 188 08 137 93 75 23 193 72 142 06 77 49 199 53 146 33 79 81 205 52 150 72 82 21 211 69 155 24 84 67 218 04 159 89 87 21 224 58 164 69 89 83 231 32 169 63 92 53 Sm $m High value Mid value Low value Total revenue 8,250 00 24,750 00 18,000 00 51,000 00 8,665 80 25,997 40 18,907 20 53,570 40 9,102 56 27,307 67 19,860 12 56,270 35 9,469 39 28,408 17 20,660 49 58,538 04 9,851 01 29,553 02 21,493 10 60,897 13 10,248 00 30,744 00 22,359 28 63,351 28 10,661 00 31,982 99 23,260 35 65,904 34 11,090 63 3 3,271 90 24,197 75 68,560 28 11,537 59' 34,612 76' 25,172 92 71,323 26 12,002 55 36,007 65 26,187 38 74,197 59 12,486 25 37,458 76 27,242 74 77,187 75 12,989 45 38,968 35 28,340 62 80,298 42 13,512 92 40,538 77 29,482 74 8 3,534 44 14,057 50 42,172 49 30,670 90 86,900 88 14,624 01 43,872 04 31,906 94 90,402 99 $m Cost of Sales High value Mid value Low value 60 00  65 00  70 00  60 00  65 00  70 00  60 00  65 00  70 00  60 00  65 00  70 00  60 00  65 00  70 00  60 00  65 00  70 00  60 00  65 00  70 00  60 00  65 00  70 00  60 00  65 00  70 00  60 00  65 00  70 00  60 00  65 00  70 00  60 00  65 00  70 00  60 00  65 00  70 00  60 00  65 00  70 00  60 00  65 00  70 00  7 8 High value Mid value Low value Total cost of sales 4,950 00 16,087 50 12,600 00 33,637 50 5,199 48 16,898 31 13,235 04 35,332 83 5,461 53 17,749 98 13,902 09 37,113 60 5,681 63 18,465 31 14,462 34 38,609 28 5,910 60 19,209 46 15,045 17 40,165 24 6,148 80 19,983 60 15,651 49 41,783 90 6,396 60 20,788 94 16,282 25 43,467 79 6,654 38 21,626 74 16,938 42 45,219 54 6,922 55 7,201 53 22,498 29 23,404 97 17,621 04 18,331 17 47,041 8948,937 67 7,491 75 24,348 20 19,069 92 50,909 86 ,793 67 25,329 43 19,838 43 52,961 53 ,107 75 26,350 20 20,637 92 55,095 88 8 ,434 50 27,412 12 21,469 63 57,316 24 8,774 41 28,516 82 22,334 86 59,626 09 Total Sales Sales Cost of sales Gross profit Sm Sm Sm 51,000 00 (33,637 50) 17,362 50 53,570 40 (35,332 83) 18,237 57 56,270 35 (37,113 60) 19 156 74 58,538 04 (38,609 28) 19,928 76 60,897 13 (40,165 24) 20,731 89 63,351 28 (41,783 90) 21,567 38 65,904 34 (43,467 79) 22 436 55 68,560 28 (45,219 54) 23,340 74 71,323 26 (47,041 89) 24,281 37 74,197 59 (48,937 67) 25,259 91 77,187 75 (50,909 86) 26,277 89 80,298 42 83,534 44 (52,961 53) (55,095 88) 27,336 89 28,438 56 86,900 88 (57,316 24) 29,584 64 90,402 99 (59,626 09) 30,776 90 Inputs Calculations Outputs    Jun 16 1 G Jun 17 2 H Jun 18 3 Jun 19 4 Jun 20 5 Jun 21 6 Jun 22 7 Jun 23 8 Jun 24 9 Jun 25 10 Jun 26 11 Jun 27 12 Jun 28 13 Jun 29 14 Jun 30 15 4A B 1 The Widget Company Inc (USD $m) 2 Calculations 51 Expense Schedule 53 Sales 54 Overhead expense per unit Sales expense 52 $ per unit 14 00 14 56 8,444 80 $m 15 14 8,870 42 15 75 9,317 49 16 22 ,692 98 16 71 10,083 61 17 21 10,489 98 17 72 10,912 72 18 26 11,352 51 18 80 11,810 01 19 37 1937 12,285 96 19 95 12,781 08 21 16 13,831 99 9 21 80 14,389 42 22 45 14,969 32 13,296 16   of sales Marketing Marketing costs Sales Marketing expense 5 00  51,000 00 2,550 00 6 00  53,570 40 3,214 22 7 00  56,270 35 3,938 92 7 00  58,538 04 4,097 66 7 00  60,897 13 4,262 80 7 00  63,351 28 4,434 59 7 00  65,904 34 4,613 30 7 00  68,560 28 4,799 22 7 00  71,323 26 4,992 63 7 00  74,197 59 5,193 83 7 00  77,187 75 5,403 14 7 00  80,298 42 5,620 89 7 00  83,534 44 5,847 41 7 00  7 00  86,900 88 90,402 99 6,083 066,328 21 Other Other costs Sm 750 00 780 00 811 20 843 65 8 68 96 895 03 921 88 949 53 978 02 1,007 36 1,037 58 1,068 71 1,100 77 1,133 79 1,167 81 1,202 84 64 Total expenses 11 774 80 12 895 84 14,100 06 14 659 60 15,241 43 15 846 44 16,475 56 17,129 75 17,810 00 18,517 37 19 252 93 20 017 82 20 813 20 21 640 29 22 500 37 66 67 Working capital 68 Days in year 366 00 365 00 365 00 365 00 366 00 3 65 00 365 00 365 00 366 00 365 00 365 00 365 00 366 00 365 00 365 00 Current Assets Sales Days receivable Accounts receivable $m days Sm 51,000 00 33 00 4,598 36 53,570 40 33 00 4,843 35 56,270 35 33 00 5,087 46 58,538 04 33 00 5,292 48 60,897 13 33 00 5,490 72 63,351 28 33 00 5,727 65 65,904 3468,560 28 33 00 33 00 5,958 476,198 60 71,323 26 33 00 6,430 79 74,197 59 33 00 6,708 28 77,187 75 33 00 6,978 62 80,298 42 33 00 7,259 86 83,534 44 33 00 7,531 79 86,900 88 33 00 7,856 79 90,402 99 33 00 8,173 42 Cost of sales Inventory days Inventory $m days Sm 33,637 50 42 00 3,860 04 35,332 83 42 00 4,065 70 37,113 60 42 00 4,270 61 38,609 28 42 00 4,442 71 40,165 24 42 00 4,609 13 41,783 90 42 00 4,808 01 43,467 79 45,219 54 47,041 89 42 00 42 00 42 00 5,001 775,203 345,398 25 48,937 67 42 00 5,631 18 50,909 86 52,961 53 55,095 88 42 00 42 00 42 00 5,858 126,094 206,322 48 57,316 24 59,626 09 42 00 42 00 6,595 296,861 08 Current Liabilities Cost of sales Total expenses Days payable Accounts payable $m $m days 33,637 50 11,774 80 37 00 4,590 86 83 84 35,332 83 12,895 84 37 00 4,888 93 37,113 60 14,100 06 37 00 5,191 52 38,609 28 14,659 60 37 00 5,399 86 40,165 24 15,241 43 37 00 41,783 90 15,846 44 37 00 5,841 98 43,467 7945,219 54 16,475 56 1 7,129 75 37 00 37 00 6,076 45 6 ,320 34 47,041 8948,937 67 17,810 00 18,517 37 37 00 37 00 6,556 07 6,837 91 50,909 86 19,252 93 37 00 7,112 39 52,961 53 20,017 82 37 00 7,397 91 55,095 88 20,813 20 37 00 7,673 87 57,316 24 21,640 29 37 00 8,003 81 59,626 09 22,500 37 37 00 8 ,325 15 Sm 85 86 87 Net Working Capital Networking capital Change in net working capital 0 00 88 3,867 54 (3,867 54) 4,020 11 (152 57) 4,166 54 (146 43) 4,335 33 (168 79) 4,498 63 (163 30) 4,693 68 (195 05) 4,883 80 (190 12) 5,081 61 (197 81) 5,272 97 (191 36) 5,501 55 (228 58) 5,724 35 (222 80) 5,956 156 (231 81) ,180 406 (224 25) ,448 27 (267 87) 6,709 36 (261 08) 89 U V W A B 1 The Widget Company Inc (USD $m) 2 Calculations 90 PP E Jun 16 1 Jun 17 2 Jun 18 3 Jun 19 4 Jun 20 5 Jun 21 6 Jun 22 7 M Jun 23 8 N Jun 24 9 O Jun 25 10 P Jun 26 11 Q Jun 27 12 R Jun 28 13 Jun 29 14 Jun 30 15 91 93 Accounting Depreciation Opening accounting net book value 0 00 8,112 00 7,316 25 5,504 78 4,478 07 12,322 87 9,633 33 8,206 81 6,781 355,357 90 14,318 25 11,168 72 9,513 95 7,861 44 0 00 96 One off capex Regular capex Capital expenditure incurred (nominal) $m $m $m 8,840 00 624 00 9,464 00 0 00 648 96 648 96 674 92 674 92 0 00 695 17 695 17 0 00 716 02 716 02 10,447 94 7 37 50 11,185 44 0 00 759 63 759 63 0 00 782 42 782 42 0 00 805 89 805 89 0 00 830 06 830 06 12,112 02 854 97 12,966 99 0 00 880 62 880 62 0 00 907 03 907 03 0 00 934 24 934 24 0 00 962 27 962 27 98 PP E   accounting (straight line) years Capital expenditure assumed to be made on the first day of the period  Year 100 101 102 103 104 105 106 107 0 00 92 71 96 42 99 31 102 29 1,597 92 108 52 111 77 108 109 110 1,352 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 1,352 00 1,352 00 92 71 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 1,444 71 1,352 00 92 71 96 42 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 1,541 13 1,352 00 92 71 96 42 99 31 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 1,640 43 1,352 00 92 71 96 42 99 31 102 29 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 1,742 72 1,352 00 92 71 96 42 99 31 102 29 1,597 92 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 3,340 64 1,352 00 92 71 96 42 99 31 102 29 1,597 92 108 52 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 3,449 16 0 00 0 00 96 42 99 31 102 29 1,597 92 108 52 111 77 115 13 0 00 0 00 0 00 0 00 0 00 0 00 2,231 35 0 00 0 00 0 00 99 31 102 29 1,597 92 108 52 111 77 115 13 118 58 0 00 0 00 0 00 0 00 0 00 2,253 52 0 00 0 00 0 00 0 00 102 29 1,597 92 108 52 111 77 115 13 118 58 1,852 43 0 00 0 00 0 00 0 00 4,006 64 0 00 0 00 0 00 0 00 0 00 1,597 92 108 52 111 77 115 13 118 58 1,852 43 125 80 0 00 0 00 0 00 4,030 15 0 00 0 00 0 00 0 00 0 00 0 00 108 52 111 77 115 13 118 58 1,852 43 125 80 129 58 0 00 0 00 2,561 80 0 00 0 00 0 00 0 00 0 00 0 00 0 00 111 77 115 13 118 58 1,852 43 125 80 129 58 133 46 0 00 2,586 75 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 115 13 118 58 1,852 43 125 80 129 58 133 46 137 47 2,612 44 111 0 00 112 113 114 115 0 00 0 00 0 00 0 00 0 00 0 00 2,208 94 0 00 8 112 00 7 316 25 6 450 04 5 504 78 4 478 07 12,322 87 9 633 33 8 206 81 6,781 35 5 ,357 90 14,318 25 11,168 72 9 513 95 7  861 44 6 211 27 9 7 0 00 9,464 00 1,892 80 7,571 20 116 117 118 Accounting depreciation 119 120 Closing accounting net book value 121 122 Taxation Depreciation 123 124 Opening tax net book value 125 Capital expenditure incurred 126 Tax depreciation 127 Closing tax net book value 1281 129 Financing 130 131 Term loans 132 133 Term loan 1 134 Opening balance 135 Term loan 1 drawdown (start of year) 136 Term loan 1 repayment (end of year) 137 Ending balance 7,571 20 648 96 1,644 03 6,576 13 6,576 13 674 92 1,450 21 5,800 84 5,800 84 695 17 ,299 20 5,196 80 5,196 80 716 02 1,182 56 4 730 26 4,730 26 11,185 44 3,183 14 12,732 56 12,732 56 759 63 2,698 44 10,793 75 10,793 75 7 82 42 2,315 23 9 260 93 ,260 93 8 05 89 2,013 36 8,053 46 8,053 46 830 06 1,776 70 7 106 82 ,106 82 12,966 99 4,014 76 16,059 05 16,059 05 880 62 3,387 93 13,551 73 13,551 73 907 03 2,891 75 11,567 01 11,567 01 934 24 2,500 25 10 001 00 10,001 00 962 27 ,192 66 34,443 04 8,770 62 1 2 0 00 0 00 4,000 00 (200 00) 3,800 00 3,800 00 0 00 (200 00) 3,600 00 3,600 00 0 00 (200 00) 3,400 00 3,400 00 0 00 (200 00) 3,200 00 3,200 00 0 00 (200 00) 3,000 00 3,000 00 0 00 (200 00) 2,800 00 2,800 00 0 00 (200 00) 2,600 00 2,600 00 0 00 (200 00) 2,400 00 2,400 00 0 00 (200 00) 2,200 00 2,200 00 0 00 (200 00) 2,000 00 2,000 00 0 00 (200 00) 1,800 00 1,800 00 0 00 (200 00) 1,600 00 1,600 00 0 00 (200 00) 1,400 00 1,400 00 0 00 (200 00) 1,200 00 1,200 00 0 00 (200 00) 1,000 00 0 00 K U V W Jun 15 Jun 16 1 Jun 17 2 Jun 18 3 Jun 19 4 Jun 20 5 Jun 21 6 Jun 22 7 M Jun 23 8 N Jun 24 9 O Jun 25 10 P Jun 26 11 Q Jun 27 12 R Jun 28 13 S Jun 29 14 Jun 30 15 LA B 1 The Widget Company Inc (USD $m) 2 Calculations 129 Financing 130 131 Term loans 132 Term loan 1 Opening balance 135 Term loan 1 drawdown (start of year) 136 Term loan 1 repayment (end of year) Ending balance 133 0 00 4,000 00 (200 00) 3,800 00 3,800 00 0 00 (200 00) 3,600 00 3,600 00 0 00 (200 00) 3,400 00 3,400 00 0 00 (200 00) 3,200 00 3,200 00 0 00 (200 00) 3,000 00 3,000 00 0 00 (200 00) 2,800 00 2,800 00 0 00 (200 00) 2,600 00 2,600 00 0 00 (200 00) 2,400 00 2,400 00 0 00 (200 00) 2,200 00 2,200 00 0 00 (200 00) 2,000 00 2,000 00 0 00 (200 00) 1,800 00 1,800 00 0 00 (200 00) 1,600 00 1,600 00 0 00 (200 00) 1,400 00 1,400 00 0 00 (200 00) 1,200 00 1,200 00 0 00 (200 00) 1,000 00 0 00 Term loan 2 Opening balance Term loan 2 drawdown (start of year) Term loan 2 repayment (end of year) Ending balance 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 5,000 00 (250 00) 4,750 00 4,750 00 0 00 (250 00) 4,500 00 4,500 00 0 00 (250 00) 4,250 00 4,250 00 0 00 (250 00) 4,000 00 4,000 00 0 00 (250 00) 3,750 00 3,750 00 0 00 (250 00) 3,500 00 3,500 00 0 00 (250 00) 3,250 00 3,250 00 0 00 (250 00) 3,000 00 3,000 00 0 00 (250 00) 2,750 00 2,750 00 0 00 (250 00) 2,500 00 3,600 00 147 Term loan 3 Opening balance Term loan 3 drawdown (start of year) Term loan 3 repayment (end of year) Ending balance 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 6,000 00 (600 00) 5,400 00 5,400 00 0 00 (600 00) 4,800 00 4,800 00 0 00 (600 00) 4 200 00 0 00 4,200 00 0 00 (600 00) 3,600 00 149 (600 00) 3,000 00 Free Cash Flows Opening cash Cash Flows from Operating Activities Cash Flows from Investing Activities Free Cash Flow Available (Required) 156 0 00 9,911 53 7,124 97 4,613 382,814 171,585 834,619 77 755 213,889 453,933 574,272 02 4,598 955 ,777 595 ,947 41 9,464 00 648 96 674 92 695 17 716 02 11,185 44 759 63 10 219 21 14 449 94 11,733 45 9 580 57 8 129 14 18,548 86 11,326 81 2,175 36 5,556 76 782 42 ,514 54 1,612 91 5,822 83 8 05 89 8 241 63 1,558 33 6,006 67 830 06 8,395 06 1,589 01 7,069 35 12,966 99 21,625 35 5,315 07 7 182 97 880 62 13,378 65 2,465 73 4,486 96 907 03 7 859 73 1,361 95 1,650 66 7,007 137,247 47 9 34 24 962 27 9,303 32 9 860 41 8 159 0 00 Financing Cash Flows Equity contribution Term loan drawdown repayment Scheduled financing cash flows 2,500 00 3,800 00 6,300 00 0 00 (200 00) (200 00) 0 00 (200 00) (200 00) (200 00) (200 00) 0 00 (200 00) (200 00) 0 00 4,550 00 4,550 00 0 00 (450 00) (450 00) 0 00 (450 00) (450 00) 0 00 (450 00) (450 00) 0 00 (450 00) (450,00) 0 00 4,950 00 4,950 00 0 00 (1,050 00) (1,050 00) 0 00 (1,050 00) (1,050 00) 0 00 (1,050 00) (1 050 00) 0 00 (1,050 00) (1 050 00) 162 163 164 Cash Flow Available  (Required) 16,519 21 14,249 94 11,533 45 9,380 57 7 ,929 14 23,098 86 10,876 81 8 ,064 54 7,791 63 7,945 0626,575 35 12,328 65 6,809 73 8,253 32 8,810 41 0 00 Revolver Opening balance Borrowings Payments Ending balance 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 168 169 170 171 172 173 174 Equity Distribution to shareholders 6,607,68 7,124 97 6,920 076 ,566 40 6,343 31 18,479 09 8 ,701 45 6 ,451 63 6,233 30 6,356 05 21,260 28 9,862 92 5,447 78 6,602 66 7,048 33 U V W Jun 15 G Jun 17 2 H Jun 18 3 I Jun 19 4 J Jun 20 5 K Jun 21 6 L Jun 22 7 M Jun 23 8 N Jun 24 9 O Jun 25 10 P Jun 26 11 Q Jun 27 12 R Jun 28 13 S Jun 29 14   Jun 30 15 179 180 181 9,911 53 4 00  396 46 7,124 97 4 00  285 00 4,613 38 4 00  184 54 2,814 171,585 834,619 77 4 00  4 00  4 00  112 57 63 43 184 79 2,175 36 4 00  87 01 1,612 91 4 00  64 52 1,558 33 4 00  62 33 1,589 01 4 00  63 56 5,315 07 4 00  212 60 2,465 73 4 00  98 63 1 361 95 4 00  54 48 1 650 66 4 00  66 03 F 1 The Widget Company Inc (USD $m) Jun 16 2 Calculations 1 174 175 Interest 176 177 Cash on hand 178 Assume calculation based on opening balance Opening cash 0 00 Cash on hand 4 00  Interest revenue 0 00 182 Term loan 1 184 Assumes drawdowns happen at the start of the year, repayment at the end of the year 185 Opening balance 0 00 186 Term loan 1 drawdown (start of year) 4,000 00 187 Term loan 1 interest 9 00  188 Interest expense (360 00) 189 190 Term loan 2 191 Assumes drawdowns happen at the start of the year, repayment at the end of the year Opening balance 0 00 193 Term loan 2 drawdown (start of year) 0 00 194 Term loan 2 interest 10 00  Interest expense 0 00 183 3,800 00 0 00 9 00  (342 00) 3,600 00 0 00 9 00  (324 00) 3,400 00 0 00 9 00  (306 00) 3,200 00 0 00 9 00  (288 00) 3,000 00 0 00 9 00  (270 00) 2,800 00 0 00 9 00  (252 00) 2,600 00 0 00 9 00  (234 00) 2,400 00 0 00 9 00  (216 00) 2,200 00 0 00 9 00  (198 00) 2,000 00 0 00 9 00  (180 00) 1,800 00 0 00 9 00  (162 00) 1,600 00 0 00 9 00  144 00 1,400 00 0 00 9 00  (126 00) 1,200 00 0 00 9 00  (108 00) 192 0 00 4,500 00 4,000 00 0 00 0 00 0 00 0 00 10 00  0 00 0 00 0 00 10 00  0 00 0 00 0 00 10 00  0 00 0 00 10 00  0 00 5,000 00 10 00  (500 00) 4,750 00 0 00 10 00  (475 00) 4,250 00 0 00 10 00  (425 00) 3,750 00 0 00 10 00  (375 00) 3,500 00 0 00 10 00  (350 00) 3,250 00 0 00 10 00  325 00 3,000 00 0 00 10 00  (300 00) 2,750 00 0 00 10 00  (275 00) 195 10 00  (450 00) 0 00 10 00  (400 00) 0 00 Term loan 3 Assumes drawdowns happen at the start of the year, repayment at the end of the year Opening balance 0 00 Term loan 3 drawdown (start of year) 0 00 Term loan 3 interest 11 00  Interest expense 0 00 0 00 0 00 11 00  0 00 0 00 0 00 11 00  0 00 0 00 0 00 11 00  0 00 0 00 11 00  0 00 0 00 0 00 11 00  0 00 0 00 0 00 11 00  0 00 0 00 0 00 11 00  0 00 0 00 0 00 11 00  0 00 0 00 0 00 11 00  0 00 6,000 00 11 00  (412 50) 5,400 00 0 00 11 00  (385 00) 4,800 00 0 00 11 00  528 00 4,200 00 0 00 11 00  (330 00) 3,600 00 0 00 11 00  (302 50) 0 00 Revolver facility Assume calculation based on opening balance Opening balance Revolver facility Interest expense 0 00 13 00  0 00 0 00 13 00  0 00 0 00 13 00  0 00 0 00 13 00  0 00 0 00 13 00  0 00 0 00 13 00  0 00 0 00 13 00  0 00 0 00 13 00  0 00 0 00 13 00  0 00 0 00 13 00  0 00 0 00 13 00  0 00 0 00 13 00  0 00 0 00 13 00  0 00 0 00 13 00  0 00 0 00 13 00  0 00 209 210 211 Interest revenue (expense) (360 00) 54 46 (39 00) (121 46) (175 43) (706 57) (542 21) (596 99) (576 48) (535 67) (903 94) (684 40) 997 00 (701 52) (619 47) U V W E Jun 15 Jun 16 Jun 22 M Jun 23 A B 1 The Widget Company Inc (USD $m) 2 Calculations 211 212 Tax Jun 17 2 Jun 18 Jun 19 34 Jun 20 5 Jun 21 6 Jun 24 9 0 Jun 25 10 Jun 26 11 Q Jun 27 12 R Jun 28 13 Jun 29 14 Jun 30 15 213   30 00  Company tax rate ( ) Note   this model does not deal with tax losses 214 215 216 217 218 P L tax Profit (Loss) before tax 3,875 70 3,951 48 3,476 56 3,507 26 3,572 30 1,673 73 1,969 623,405 08 3,663 54 3,953 36 2,114 382,604 526,060 56 4,656 08 5,044 62 219 Income tax benefit (expense) (1,162 71) (1,185 44) (1,042 97) (1,052 18) (1,071 69) (502  12) (590 89) (1,021 52) (1,099 06) (1,186 01) (634 31) (781 36) (1,818 17) (1,396 82) (1,513 38) 220 221 222 223 224 225 Adjustments to arrive at taxable profit Adjustment for timing differences in depreciation  Accounting depreciation Tax depreciation Timing difference (1,352 00) (1,892 80) 540 80 (1,444 71) (1,644 03) 199 32 (1,541 13) (1,450 21) (90 92) (1,640 43) (1,299 20) (341 23) (1,742 72) (1,182 56) (560 16) (3,340 64) (3,183 14) (157 50) (3,449 16) (2,698 44) (750 72) (2,208 94) (2,315 23) 106 30 (2,231 35) (2,013 36) (217 99) (2,253 52) (1,776 70) (476 81) (4,006 64) (4,014 76) 8 13 (4,030 15) (3,387 93) (642 22) (2,561 80) (2,891 75) 329 95 (2,586 75) (2,500 25) (86 50) (2,612 44) (2,192 66) (419 79) 226 227 228 Adjusted profit before tax Tax payable 4,416 50 (1,324 95) 4 150 80 (1,245 24) 3,385 64 (1,015 69) 3,166 02 (949 81) 3,012,14 (903 64) 1,516 23 (454 87) 1,218 89 (365 67) 3,511 37 (1,053 41) 3,445 55 (1,033 66) 3,476 55 (1,042 96) 2,122 51 (636 75) 1,962 31 (588 69) 6,390,51 (1,917 15) 4,569 58 (1,370 87) 4,624 83 (1,387 45) 230 231 232 233 234 Depreciation timing differences Opening balance Timing difference Closing balance 0 00 540 80 540 80 540 80 199 32 740 12 7 40 12 (90 92) 649 21 649 21 (341 23) 307 97 307 97 (560 16) (252 19) (252 19) (157 50) (409 69) (409 69) (750 72) (1,160 41) (1,160 41) 106 30 (1,054 12) (1,054 12) (217 99) (1,272 11) (1,272 11) (476 81) (1,748 92) (1,748 92) 8 13 (1,740 79) (1,740 79) (642 22) (2,383 01) (2,383 01) 329 95 (2,053 06) (2,053 06) (86 50) (2,139 56) (2,139 56) (419 79) (2,559 35) 0 00 235 236 (162 24) (222 04) (194 76) (92 39) 75 66 122 91 22 91 348 12 348 12 316 24 316 24 381 63 524 68 524 68 522 24 522 24 714 90 714 90 6 15 92 615 92 641 87 767 81 222 04 0 00 Deferred tax assetliability (depreciation timing differences) 237 238 Deferred tax asset (depreciation timing differences) 239 Deferred tax liability (depreciation timing differences) 240 241 Sheet End 242 162 24 0 00 0 00 194 76 0 00 92 39 0 00 0 00 75 66 0 00 122 91 0 00 348 12 0 00 316 24 0 00 381 63 0 00 524 68 0 00 522 24 0 00 714 90 0 00 615 92 641 87 0 00 767 81 U V W sunny leon Jun 16 Jun 15 H Jun 18 elementele pe lumen Jun 17 Jun 19 dengan M Jun 23 Jun 20 tempat Jun 21 N Jun 24 bermain Jun 22 AB 1 The Widget Company Inc (USD $m) 2 Outputs 3 4 Profit and Loss luas Jun 25 dan luar negeringen Jun 26 Jun 28 som heter Jun 29 Jun 27 12 dengan Jun 30 bunga Sales Cost of Sales Gross Margin 51,000 00 (33,637 50) 17,362 50 53,570 40 (35,332 83) 18,237 57 56,270 35 (37,113 60) 19,156 74 58,538 04 (38,609 28) 19,928 76 60,897 13 (40,165 24) 20,731 89 63,351 28 (41,783 90) 21,567 38 65,904 34 (43,467 79) 22,436 55 68,560 28 (45,219 54) 23,340 74 71,323 26 (47,041 89) 24,281 37 74,197 59 (48,937 67) 25,259 91 77,187 75 (50,909 86) 26,277 89 80,298 42 (52,961 53) 27,336 89 83,534 44 (55,095 88) 28,438 56 86,900 88 (57,316 24) 29,584 64 90,402 99 (59,626 09) 30,776 90 Overhead Expenses Sales Marketing Other Total overhead expenses (8,444 80) (2,550 00) (780 00) (11,774 80) (8,870 42) (3,214 22) (811 20) (12,895 84) (9,317 49) (3,938 92) (843 65) (14,100 06) (9,692 98) (4,097 66) (868 96) (14,659 60) (10,083 61) (4 262 80) (895 03) (15,241 43) (10,489 98) (4,434 59) (921 88) (15,846 44) (10,912 72) (4,613 30) (949 53) (16,475 56) (11,352 51) (4,799 22) (978 02) (17,129 75) (11,810 01) (4,992 63) (1,007 36) (17,810 00) (12,285 96) (5,193 83) (1,037 58) (18,517 37) (12,781 08) (5,403 14) (1,068 71) (19,252 93) (13,296 16) (5,620 89) (1,100 77) (20,017 82) (13,831 99) (5,847 41) (1,133 79) (20,813 20) (14,389 42) (6,083 06) (1,167 81) (21,640 29) (14,969 32) (6,328 21) (1,202 84) (22,500 37) EBITDA 5,587 70 5,341 73 5,056 68 5 ,269 16 5,490 46 5,720 94 5,960 99 6,211 00 6,471 37 6,742 55 7,024 96 7,319 07 7,625 37 7,944 35 8 276 53 Depreciation Interest revenue (expense) Profit (Loss) before tax (1,352 00) (360 00) 3,875 70 (1,444 71) 54 46 3,951 48 (1,541 13) (39 00) 3,476 56 (1,640 43) (121 46) 3,507 26 (1,742 72) (175 43) 3,572 30 (3,340 64) (706 57) 1,673 73 (3,449 16) (542 21) 1,969 62 (2 208 94) (596 99) 3,405 08 (2,231 35) (576 48) 3,663 54 (2 253 52) (535 67) 3,953 36 (4 006 64) (903 94) 2,114 38 (4,030 15) (684 40) 2,604 52 (2,561 80) 997 00 6,060 56 (2,586 75) (701 52) 4,656 08 (2,612 44) (619 47) 5,044 62 Income tax benefit (expense) Net Profit (Loss) after tax (1,162 71) 2 712 99 (1,185 44) 2 766 04 (1,042 97) 2 433 59 (1,052 18) 2 455 08 (1,071 69) 2 500 61 (502 12) 1,171 61 (590 89) ,378 73 (1,021 52) 2 383 55 (1,099 06) 2 564 48 (1,186 01) 2 767 35 (634 31) 1,480 07 (781 36) 1 823 17 (1,818 17) 4 242 39 (1,396 82) 3 259 25 (1,513 38) 3,531 23 1 Balance Sheet 0 00 Current Assets Cash at Bank Accounts receivable Inventory Total Current Assets 5 9,911 53 4,598 36 3,860 04 18,369 93 6,938 07 4,843 35 4,065 70 15,847 12 4,349 86 5,087 46 4,270 61 13,707 92 2,568 40 5,292 48 4,442 71 12,303 60 1,416 18 5,490 72 4,609 13 11,516 03 4,461 93 ,727 65 4,808 01 14,997 59 1,813 80 5,958 47 5,001 77 12,774 05 1,207 54 1 160 71 1 214 73 6,198 606,430 796,708 28 5,203 34 5,398 25 5,631 18 1 2,609 48 12,989 74 13,554 19 4 968 14 1,930 84 1,254 97 6,978 62 7,259 86 7,531 79 5,858 126,094 206 ,322 48 1 7,804 88 15,284 90 15,109 24 1,215 99 1 311 54 7,856 79 8 ,173 42 6,595 296,861 08 15,668 08 16,346 05 4 Non Current Assets Plant and equipment Deferred tax asset (depreciation timing differences) Total Non Current Assets 8,112 00 162 24 8,274 24 7,316 25 222 04 7,538 296 6,450 04 194 76 ,644 81 5,504 78 92 39 5,597 17 ,478 07 0 00 4,478 07 12,322 87 9,633 33 8,206 81 0 00 0 00 0 00 12,322 879,633 338,206 81 6,781 35 0 00 6,781 355 5,357 9014,318 25 0 00 0 00 ,357 90 14,318 25 11,168 72 9 0 00 11,168 729 ,513 95 0 00 ,513 95 7,861 44 0 00 7,861 44 6,211 27 0 00 6,211 27 Total Assets 34 918 41 30,923 69 26,997 54 23,497 93 20,472 18 39 643 33 32,040 72 29 023 11 26,552 44 24 269 98 46,441 38 37,622 34 34 137 13 31,390 96 28,768 59 Current Liabilities Accounts payable Revolver borrowings Total Current Liabilities 4,590 86 0 00 4,590 86 4,888 93 0 00 4,888 93 5,191 525,399 865,601 22 0 00 0 00 0 00 5,191 52 5,399 86 5,601 225 5,841 98 0 00 ,841 98 6,076 456,320 34 0 00 0 00 6,076 45 6 320 34 6,556 07 0 00 6 556 07 6,837 917,112 397,397 91 7,673 87 0 00 0 00 0 00 0 00 6 837 91 7 112 397 397 917 673 87 8,003 818 ,325 15 0 00 0 00 8 003 818 325 15 0 Non Current Liabilities Term loans Deferred tax liability (depreciation timing differences) Total Non Current Liabilities 3,800 00 0 00 3,800 00 3,600 00 0 00 3,600 00 3,400 00 0 00 3,400 00 3,200 00 0 00 3,200 00 3,000 00 75 66 3,075 66 7,550 00 122 91 7,672 91 7,100 00 348 12 7,448 12 6,650 00 316 24 6,966 24 6,200 00 381 63 6,581 636 5,750 00 5 24 68 ,274 68 ,700 00 522 24 11,222 24 9,650 00 714 90 10,364 90 8,600 00 6 15 92 9,215 92 7,550 00 641 87 8,191 87 6,500 00 767 81 7,267 81 Total Liabilities 8,390 86 8,488 93 8,591 52 8,599 86 8,676 88 13,514 89 13,524 57 13,286 57 13,137 70 13,112 58 18,334 63 17,762 81 16,889 79 16,195 68 15,592 95 Net Assets 26,527 55 22,434 76 18,406 01 14,898 07 11,795 30 26,128 45 18,516 14 15,736 54 13,414 74 11,157 40 28,106 75 19,859 53 17,247 35 15,195 28 13,175 64 Shareholders Equity Equity Opening balance Equity contribution Closing balance 0 00 2,500 00 2,500 00 2,500 00 0 00 2,500 00 2,500 00 0 00 2,500 00 2,500 00 0 00 2,500 00 2,500 00 0 00 2,500 00 2,500 00 0 00 2,500 00 2,500 00 0 00 2,500 00 2,500 00 0 00 2,500 00 2,500 00 0 00 2,500 00 2,500 00 0 00 2,500 00 2,500 00 0 00 2,500 00 2,500 00 0 00 2,500 00 2,500 00 0 00 2,500 00 2,500 00 0 00 2,500 00 2,500 00 0 00 2,500 00 0 00 Retained Earnings Opening retained earnings Net profit (loss) after tax Distributions to shareholders Closing retained earnings 0 00 2 712 99 (6,607 68)  (3,894 69) (3,894 69) (7,865 45) (11,702 55) (14,941 18) 2,967 31 2,687 692,754 312,825 01 (6,938 07) (6,524 79) (5,992 94) (5,664 73) (7,865 45) (11,702 55) (14,941 18) (17,780 90) (17,780 90) 1,505 21 (17,847 74) (34,123 43) (34,123 43) 2,267 18 (7,255 20) (39,111 46) (39,111 46) (40,661 37) (41,887 48) (43,185 50) (60,845 48) (65,492 40) (66,269 88) (66,761 93) 3,280 24 3,416 71 3,560 92 2,212 58 3,076 454,242 394,371 92 4,510 16 (4,830 16) (4,642 82) (4,858 94) (19,872 56) (7,723 37) (5,019 87) (4,863 97) (5,246 18) (40,661 37) (41,887 48) (43,185 50) (60,845 48) (65,492 40) (66,269 88) (66,761 93) (67,497 95) 0 00 Total Equity (1,394 69) (5,365 45) (9,202 55) (12,441 18) (15,280 90) (31,623 43) (36,611 46) (38,161 37) (39,387 48) (40,685 50) (58,345 48) (62,992 40) (63,769 88) (64 261 93) (64 997 95) Question 10 Analyst comment   The balance sheet doesn't balance   Error(s) in Outputs 27 67  What is the total net as sets ($m) in the June 2030 year under the Base scenario  a  8,085 44 b  8,085 55 c  d  8,085 66 8,085 77 e  8,085 88 f  8,085 99 M N O R S U V W X AB 1 The Widget Company Inc (USD $m) 2 Inputs E Jun 15 0 Jun 16 Jun 18 Jun 19 Jun 22 Jun 21 6 Jun 17 2 Jun 23 Jun 20 lun 20 5 P Jun 26 11 10 Jun 27 12 Jun 28 13 Jun 29 14 Jun 30 15 14 Keyotes All real values are real as at June 2015 so should be subject to inflation from 2016 Input Headings The Widget Company Inc Currency and units First year of forecast period (USD $m) Jun 16 Jun 15 Jun 20 Jun 21 Timeline Year Counter Jun 16 2016 1 00 Jun 17 2017 2 00 Jun 18 2018 3 00 Jun 19 2019 4 00 2020 2021 Jun 22 2022 7 00 Jun 23 2023 8 00 Jun 24 2024 9 00 Jun 25 2025 10 00 Jun 26 2026 11 00 Jun 27 2027 12 00 Jun 28 2028 13 00 Jun 29 2029 14 00 Jun 30 2030 15 00 0 00 5 00 6 00 Economic Assumptions CPI Applied to sales and admin costs only   pa 4 00  4 00  4 00  3 00  3 00  3 00  3 00  3 00  3 00  3 00  3 00  3 00  3 00  3 00  3 00  Taxation Company tax rate ( ) Note   this model does not deal with tax losses L 30 00  Revenue Volume Assumptions Sales (m units) Widget A Widget B Widget C Widget D Widget E Widget F Widget G Widget H units ms units ms units ms units ms units ms units ms units ms units ms 100 00 50 00 25 00 60 00 200 00 30 00 75 00 40 00 101 00 50 50 25 25 60 60 202 00 30 30 75 75 40 40 102 01 51 01 25 50 61 21 204 02 30 60 76 51 40 80 103 03 51 52 25 76 61 82 206 06 30 91 77 27 41 21 104 06 52 03 26 02 62 44 208 12 31 22 78 05 41 62 105 10 52 55 26 28 63 06 210 20 31 53 78 83 42 04 106 15 53 08 26 54 63 69 212 30 31 85 79 61 42 46 107 21 53 61 26 80 64 33 214 43 32 16 80 41 42 89 108 29 54 14 27 07 64 97 216 57 32 49 109 37 54 68 27 34 65 62 218 74 32 81 82 03 43 75 110 46 55 23 27 62 66 28 220 92 33 14 82 85 44 18 111 57 55 78 27 89 66 94 223 13 33 47 83 68 44 63 112 68 56 34 28 17 67 61 225 37 33 80 84 51 45 07 113 81 56 90 28 45 68 29 227 62 34 14 85 36 45 52 114 95 57 47 28 74 68 97 229 89 34 48 86 21 45 98 81 21 43 31 Revenue Pricing Assumptions Widget categories Widget A Widget B Widget C Widget D Widget E Widget F Widget G Widget H Category Low value Mid value High value Mid value Low value High value Mid value Mid value Pricing scenario used Base Pricing (nominal)   Base Scenario D D Inputs Calculations Outputs   U V W Jun 15 Jun 22 M Jun 23 Jun 21 Jun 28 Jun 16 Jun 17 12 Jun 29 Jun 18 3 Jun 30 AB 1 The Widget Company Inc (USD $m) 2 Inputs Pricing scenario used Jun 24 Jun 19 4 Jun 20 5 Jun 25 10 Jun 26 11 Jun 27 12 9 13 15 Base Pricing (nominal)   Base Scenario High value Mid value Low value $   unit $   unit $   unit 150 00 110 00 60 00 156 00 114 40 62 40 162 24 118 98 64 90 167 11 122 55   66 84 172 12 126 22 68 85 177 28 130 01 7 0 91 182 60 133 91 73 04 188 08  137 93 75 23 193 72 142 06  77 49 199 53   146 33 79 81 205 52 150 72 82 21 211 69 155 24 84 67 218 04 159 89 87 21 224 58 164 69 89 83 231 32 169 63 92 53 Pricing (nominal)   Growth Scenario High value Mid value Low value $   unit $   unit $   unit 180 00 140 00 90 00 187 20 145 60 93 60 194 69 151 42 97 34 200 53 155 97 100 26 206 54 160 65 103 27 212 74 165 47   106 37 219 12 170 43 109 56 225 70 175 54 112 85 232 47   180 81 116 23 239 44 186 23 119 72 246 62 191 82 123 31 254 02 197 57 127 01 261 64 203 50 130 82 269 49 209 61 134 75 277 58 215 89 138 79 Cost Of Sales ( ) High value Mid value Low value 60 00  65 00  70 00  60 00  65 00  70 00  60 00  65 00  70 00  60 00  65 00  70 00  60 00  65 00  70 00  60 00  65 00  70 00  60 00  65 00  70 00  60 00  65 00  70 00  60 00  65 00  70 00  60 00  65 00  70 00  60 00  65 00  70 00  60 00  65 00  70 00  60 00  65 00  70 00  60 00  65 00  70 00  60 00  65 00  70 00  14 00 Overheads Direct sales costs Marketing costs Other (per year) $ per unit   of sales $m 5 00  5 00  7 00  7 00  7 00  7 00  7 00  7 00  7 00  7 00  7 00  7 00  7 00  7 00  7 00  750 00 Working Capital Days receivable days Inventory days days Days payable days All working capital calculated on a (days  actual) basis 33 00 42 00 37 00 33 00 42 00 37 00 33 00 42 00 37 00 33 00 42 00 37 00 33 00 42 00 37 00 33 00 42 00 37 00 33 00 42 00 37 00 33 00 42 00 37 00 33 00 42 00 37 00 33 00 42 00 37 00 33 00 42 00 37 00 33 00 42 00 37 00 33 00 42 00 37 00 33 00 42 00 37 00 33 00 42 00 37 00 Capital Expenditure One off capex Regular capex $m Sm 8 840 00 624 00 0 00 648 96 0 00 674 92 0 00 695 17 0 00 716 02 10 447 94 737 50 0 00 759 63 0 00 782 42 0 00 805 89 0 00 830 06  12 112 02 854 97 0 00 880 62 0 00 907 03 0 00 934 24 0 00 962 27 Depreciation Rates PP E   accounting (straight line) PP E   tax (diminishing value) years   pa 7 00 20 00  1 Financing Interest rates Cash on hand Term loan 1 interest Term loan 2 interest Term loan 3 interest Revolver facility 4 00  9 00  10 00  11 00  13 00  4 00  9 00  10 00  11 00  13 00  4 00  9 00  10 00  11 00  13 00  4 00  9 00  10 00  11 00  13 00  4 00  9 00  10 00  11 00  13 00  4 00  9 00  10 00  11 00  13 00  4 00  9 00  10 00  11 00  13 00  4 00  9 00  10 00  11 00  13 00  4 00  9 00  10 00  11 00  13 00  4 00  9 00  10 00  11 00  13 00  4 00  9 00  10 00  11 00  13 00  4 00  9 00  10 00  11 00  13 00  4 00  9 00  10 00  11 00  13 00  4 00  9 00  10 00  11 00  13 00  4 00  9 00  10 00  11 00  13 00  Other Term loan 1 drawdown (start of year) Term loan 1 repayment (end of year) Term loan 2 drawdown (start of year) Term loan 2 repayment (end of year) Term loan 3 drawdown (start of year) Term loan 3 repayment (end of year) Equity contribution Equity distributions (  of cash available) 4 000 00 (200 00) 0 00 0 00 0 00 0 00 2 500 00 40 00  0 00 (200 00 0 00 0 00 0 00 0 00 0 00 50 00  0 00 (200 00) 0 00 0 00 0 00 0 00 0 00 60 00  0 00 200 00) 0 00 0 00 0 00 0 00 0 00 70 00  0 00 (200 00) 0 00 0 00 0 00 0 00 0 00 80 00  0 00 (200 00) 5 000 00 (250 00) 0 00 0 00 0 00 80 00  0 00 (200 00 0 00 (250 00 0 00 0 00 0 00 80 00  0 00 (200 00) 0 00 (250 00) 0 00 0 00 0 00 80 00  0 00 (200 00) 0 00 (250 00) 0 00 0 00 0 00 80 00  0 00 (200 00) 0 00 (250 00) 0 00 0 00 0 00 80 00  0 00 (200 00) 0 00 (250 00) 6 000 00 (600 00) 0 00 80 00  0 00 (200 00) 0 00 (250 00) 0 00 (600 00) 0 00 80 00  0 00 (200 00) 0 00 (250 00) 0 00 (600 00) 0 00 80 00  0 00 (200 00) 0 00 (250 00) 0 00 (600 00) 0 00 80 00  80 00  0 00 (200 00) 0 00 (250 00) 0 00 (600 00) 0 00 80 00  heet End N U V W 26 Jun 15 Jun 16 Jun 17 12 Jun 18 Jun 19 Jun 20 45 Jun 21 Jun 22 1 The Widget Company Inc (USD $m) 2 Calculations 3 4 Operations Jun 23 Jun 24 Jun 25 10 Jun 26 11 Jun 27 12 Jun 28 13 Jun 29 14 Jun 30 15 Volumes Sales (m units) Widget A Widget B Widget C Widget D Widget E Widget F Widget G Widget H Total volume Low value Mid value High value Mid value Low value High value Mid value Mid value units ms 100 00 50 00 25 00 60 00 200 00 30 00 75 00 40 00 580 00 101 00 50 50 25 25 60 60 202 00 30 30 75 75 40 40 585 80 102 01 51 01 25 50 61 21 204 02 30 60 76 51 40 80 591 66 103 03 51 52 25 76 61 82 206 06 30 91 77 27 41 21 597 57 104 06 52 03 26 02 62 44 208 12 31 22 78 05 41 62 603 55 105 10 52 55 26 28 63 06 210 20 31 53 78 83 42 04 609 59 106 15 53 08 26 54 63 69 212 30 31 85 79 61 42 46 615 68 107 21 53 61 26 80 64 33 214 43 32 16 80 41 42 89 621 84 108 29 54 14 27 07 64 97 216 57 32 49 81 21 43 31 628 06 109 37 54 68 27 34 65 62 218 74 32 81 82 03 43 75 634 34 110 46 55 23 27 62 66 28 220 92 33 14 82 85 44 18 640 68 111 57 55 78 27 89 66 94 223 13 33 47 83 68 44 63 647 09 112 68 56 34 28 17 67 61 225 37 33 80 84 51 45 07 653 56 113 81 56 90 28 45 68 29 227 62 34 14 85 36 45 52 660 09 114 95 57 47 28 74 68 97 229 89 34 48 86 21 45 98 666 70 High value Mid value Low value units ms units ms units ms 55 00 225 00 300 00 300 00 55 55 227 25 303 00 303 00 56 11 229 52 306 03 306 03 56 67 231 82 309 09 309 09 57 23 234 14 312 18 312 18 57 81 236 48 315 30 315 30 58 38 238 84 318 46 318 46 58 97 241 23 321 64 321 64 59 56 243 64 324 86 324 86 60 15 246 08 328 11 328 11 60 75 248 54 331 39 331 39 61 36 251 03 334 70 334 70 61 98 253 54 338 05 338 05 62 60 256 07 341 43 341 43 63 22 258 63 344 84 344 84 4,829 07 Revenues Pricing scenario used Base Pricing (nominal)   Growth Scenario High value Mid value Low value $ unit $ unit $ unit 150 00 110 00 60 00 156 00 114 40 62 40 162 24 118 98 64 90 167 11 122 55 66 84 172 12 126 22 68 85 177 28 130 01 70 91 182 60 133 91 73 04 188 08 137 93 75 23 193 72 142 06 77 49 199 53 146 33 79 81 205 52 150 72 82 21 211 69 155 24 84 67 218 04 159 89 87 21 224 58 164 69 89 83 231 32 169 63 92 53 Sm $m High value Mid value Low value Total revenue 8,250 00 24,750 00 18,000 00 51,000 00 8,665 80 25,997 40 18,907 20 53,570 40 9,102 56 27,307 67 19,860 12 56,270 35 9,469 39 28,408 17 20,660 49 58,538 04 9,851 01 29,553 02 21,493 10 60,897 13 10,248 00 30,744 00 22,359 28 63,351 28 10,661 00 31,982 99 23,260 35 65,904 34 11,090 63 3 3,271 90 24,197 75 68,560 28 11,537 59' 34,612 76' 25,172 92 71,323 26 12,002 55 36,007 65 26,187 38 74,197 59 12,486 25 37,458 76 27,242 74 77,187 75 12,989 45 38,968 35 28,340 62 80,298 42 13,512 92 40,538 77 29,482 74 8 3,534 44 14,057 50 42,172 49 30,670 90 86,900 88 14,624 01 43,872 04 31,906 94 90,402 99 $m Cost of Sales High value Mid value Low value 60 00  65 00  70 00  60 00  65 00  70 00  60 00  65 00  70 00  60 00  65 00  70 00  60 00  65 00  70 00  60 00  65 00  70 00  60 00  65 00  70 00  60 00  65 00  70 00  60 00  65 00  70 00  60 00  65 00  70 00  60 00  65 00  70 00  60 00  65 00  70 00  60 00  65 00  70 00  60 00  65 00  70 00  60 00  65 00  70 00  7 8 High value Mid value Low value Total cost of sales 4,950 00 16,087 50 12,600 00 33,637 50 5,199 48 16,898 31 13,235 04 35,332 83 5,461 53 17,749 98 13,902 09 37,113 60 5,681 63 18,465 31 14,462 34 38,609 28 5,910 60 19,209 46 15,045 17 40,165 24 6,148 80 19,983 60 15,651 49 41,783 90 6,396 60 20,788 94 16,282 25 43,467 79 6,654 38 21,626 74 16,938 42 45,219 54 6,922 55 7,201 53 22,498 29 23,404 97 17,621 04 18,331 17 47,041 8948,937 67 7,491 75 24,348 20 19,069 92 50,909 86 ,793 67 25,329 43 19,838 43 52,961 53 ,107 75 26,350 20 20,637 92 55,095 88 8 ,434 50 27,412 12 21,469 63 57,316 24 8,774 41 28,516 82 22,334 86 59,626 09 Total Sales Sales Cost of sales Gross profit Sm Sm Sm 51,000 00 (33,637 50) 17,362 50 53,570 40 (35,332 83) 18,237 57 56,270 35 (37,113 60) 19 156 74 58,538 04 (38,609 28) 19,928 76 60,897 13 (40,165 24) 20,731 89 63,351 28 (41,783 90) 21,567 38 65,904 34 (43,467 79) 22 436 55 68,560 28 (45,219 54) 23,340 74 71,323 26 (47,041 89) 24,281 37 74,197 59 (48,937 67) 25,259 91 77,187 75 (50,909 86) 26,277 89 80,298 42 83,534 44 (52,961 53) (55,095 88) 27,336 89 28,438 56 86,900 88 (57,316 24) 29,584 64 90,402 99 (59,626 09) 30,776 90 Inputs Calculations Outputs    Jun 16 1 G Jun 17 2 H Jun 18 3 Jun 19 4 Jun 20 5 Jun 21 6 Jun 22 7 Jun 23 8 Jun 24 9 Jun 25 10 Jun 26 11 Jun 27 12 Jun 28 13 Jun 29 14 Jun 30 15 4A B 1 The Widget Company Inc (USD $m) 2 Calculations 51 Expense Schedule 53 Sales 54 Overhead expense per unit Sales expense 52 $ per unit 14 00 14 56 8,444 80 $m 15 14 8,870 42 15 75 9,317 49 16 22 ,692 98 16 71 10,083 61 17 21 10,489 98 17 72 10,912 72 18 26 11,352 51 18 80 11,810 01 19 37 1937 12,285 96 19 95 12,781 08 21 16 13,831 99 9 21 80 14,389 42 22 45 14,969 32 13,296 16   of sales Marketing Marketing costs Sales Marketing expense 5 00  51,000 00 2,550 00 6 00  53,570 40 3,214 22 7 00  56,270 35 3,938 92 7 00  58,538 04 4,097 66 7 00  60,897 13 4,262 80 7 00  63,351 28 4,434 59 7 00  65,904 34 4,613 30 7 00  68,560 28 4,799 22 7 00  71,323 26 4,992 63 7 00  74,197 59 5,193 83 7 00  77,187 75 5,403 14 7 00  80,298 42 5,620 89 7 00  83,534 44 5,847 41 7 00  7 00  86,900 88 90,402 99 6,083 066,328 21 Other Other costs Sm 750 00 780 00 811 20 843 65 8 68 96 895 03 921 88 949 53 978 02 1,007 36 1,037 58 1,068 71 1,100 77 1,133 79 1,167 81 1,202 84 64 Total expenses 11 774 80 12 895 84 14,100 06 14 659 60 15,241 43 15 846 44 16,475 56 17,129 75 17,810 00 18,517 37 19 252 93 20 017 82 20 813 20 21 640 29 22 500 37 66 67 Working capital 68 Days in year 366 00 365 00 365 00 365 00 366 00 3 65 00 365 00 365 00 366 00 365 00 365 00 365 00 366 00 365 00 365 00 Current Assets Sales Days receivable Accounts receivable $m days Sm 51,000 00 33 00 4,598 36 53,570 40 33 00 4,843 35 56,270 35 33 00 5,087 46 58,538 04 33 00 5,292 48 60,897 13 33 00 5,490 72 63,351 28 33 00 5,727 65 65,904 3468,560 28 33 00 33 00 5,958 476,198 60 71,323 26 33 00 6,430 79 74,197 59 33 00 6,708 28 77,187 75 33 00 6,978 62 80,298 42 33 00 7,259 86 83,534 44 33 00 7,531 79 86,900 88 33 00 7,856 79 90,402 99 33 00 8,173 42 Cost of sales Inventory days Inventory $m days Sm 33,637 50 42 00 3,860 04 35,332 83 42 00 4,065 70 37,113 60 42 00 4,270 61 38,609 28 42 00 4,442 71 40,165 24 42 00 4,609 13 41,783 90 42 00 4,808 01 43,467 79 45,219 54 47,041 89 42 00 42 00 42 00 5,001 775,203 345,398 25 48,937 67 42 00 5,631 18 50,909 86 52,961 53 55,095 88 42 00 42 00 42 00 5,858 126,094 206,322 48 57,316 24 59,626 09 42 00 42 00 6,595 296,861 08 Current Liabilities Cost of sales Total expenses Days payable Accounts payable $m $m days 33,637 50 11,774 80 37 00 4,590 86 83 84 35,332 83 12,895 84 37 00 4,888 93 37,113 60 14,100 06 37 00 5,191 52 38,609 28 14,659 60 37 00 5,399 86 40,165 24 15,241 43 37 00 41,783 90 15,846 44 37 00 5,841 98 43,467 7945,219 54 16,475 56 1 7,129 75 37 00 37 00 6,076 45 6 ,320 34 47,041 8948,937 67 17,810 00 18,517 37 37 00 37 00 6,556 07 6,837 91 50,909 86 19,252 93 37 00 7,112 39 52,961 53 20,017 82 37 00 7,397 91 55,095 88 20,813 20 37 00 7,673 87 57,316 24 21,640 29 37 00 8,003 81 59,626 09 22,500 37 37 00 8 ,325 15 Sm 85 86 87 Net Working Capital Networking capital Change in net working capital 0 00 88 3,867 54 (3,867 54) 4,020 11 (152 57) 4,166 54 (146 43) 4,335 33 (168 79) 4,498 63 (163 30) 4,693 68 (195 05) 4,883 80 (190 12) 5,081 61 (197 81) 5,272 97 (191 36) 5,501 55 (228 58) 5,724 35 (222 80) 5,956 156 (231 81) ,180 406 (224 25) ,448 27 (267 87) 6,709 36 (261 08) 89 U V W A B 1 The Widget Company Inc (USD $m) 2 Calculations 90 PP E Jun 16 1 Jun 17 2 Jun 18 3 Jun 19 4 Jun 20 5 Jun 21 6 Jun 22 7 M Jun 23 8 N Jun 24 9 O Jun 25 10 P Jun 26 11 Q Jun 27 12 R Jun 28 13 Jun 29 14 Jun 30 15 91 93 Accounting Depreciation Opening accounting net book value 0 00 8,112 00 7,316 25 5,504 78 4,478 07 12,322 87 9,633 33 8,206 81 6,781 355,357 90 14,318 25 11,168 72 9,513 95 7,861 44 0 00 96 One off capex Regular capex Capital expenditure incurred (nominal) $m $m $m 8,840 00 624 00 9,464 00 0 00 648 96 648 96 674 92 674 92 0 00 695 17 695 17 0 00 716 02 716 02 10,447 94 7 37 50 11,185 44 0 00 759 63 759 63 0 00 782 42 782 42 0 00 805 89 805 89 0 00 830 06 830 06 12,112 02 854 97 12,966 99 0 00 880 62 880 62 0 00 907 03 907 03 0 00 934 24 934 24 0 00 962 27 962 27 98 PP E   accounting (straight line) years Capital expenditure assumed to be made on the first day of the period  Year 100 101 102 103 104 105 106 107 0 00 92 71 96 42 99 31 102 29 1,597 92 108 52 111 77 108 109 110 1,352 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 1,352 00 1,352 00 92 71 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 1,444 71 1,352 00 92 71 96 42 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 1,541 13 1,352 00 92 71 96 42 99 31 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 1,640 43 1,352 00 92 71 96 42 99 31 102 29 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 1,742 72 1,352 00 92 71 96 42 99 31 102 29 1,597 92 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 3,340 64 1,352 00 92 71 96 42 99 31 102 29 1,597 92 108 52 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 3,449 16 0 00 0 00 96 42 99 31 102 29 1,597 92 108 52 111 77 115 13 0 00 0 00 0 00 0 00 0 00 0 00 2,231 35 0 00 0 00 0 00 99 31 102 29 1,597 92 108 52 111 77 115 13 118 58 0 00 0 00 0 00 0 00 0 00 2,253 52 0 00 0 00 0 00 0 00 102 29 1,597 92 108 52 111 77 115 13 118 58 1,852 43 0 00 0 00 0 00 0 00 4,006 64 0 00 0 00 0 00 0 00 0 00 1,597 92 108 52 111 77 115 13 118 58 1,852 43 125 80 0 00 0 00 0 00 4,030 15 0 00 0 00 0 00 0 00 0 00 0 00 108 52 111 77 115 13 118 58 1,852 43 125 80 129 58 0 00 0 00 2,561 80 0 00 0 00 0 00 0 00 0 00 0 00 0 00 111 77 115 13 118 58 1,852 43 125 80 129 58 133 46 0 00 2,586 75 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 115 13 118 58 1,852 43 125 80 129 58 133 46 137 47 2,612 44 111 0 00 112 113 114 115 0 00 0 00 0 00 0 00 0 00 0 00 2,208 94 0 00 8 112 00 7 316 25 6 450 04 5 504 78 4 478 07 12,322 87 9 633 33 8 206 81 6,781 35 5 ,357 90 14,318 25 11,168 72 9 513 95 7  861 44 6 211 27 9 7 0 00 9,464 00 1,892 80 7,571 20 116 117 118 Accounting depreciation 119 120 Closing accounting net book value 121 122 Taxation Depreciation 123 124 Opening tax net book value 125 Capital expenditure incurred 126 Tax depreciation 127 Closing tax net book value 1281 129 Financing 130 131 Term loans 132 133 Term loan 1 134 Opening balance 135 Term loan 1 drawdown (start of year) 136 Term loan 1 repayment (end of year) 137 Ending balance 7,571 20 648 96 1,644 03 6,576 13 6,576 13 674 92 1,450 21 5,800 84 5,800 84 695 17 ,299 20 5,196 80 5,196 80 716 02 1,182 56 4 730 26 4,730 26 11,185 44 3,183 14 12,732 56 12,732 56 759 63 2,698 44 10,793 75 10,793 75 7 82 42 2,315 23 9 260 93 ,260 93 8 05 89 2,013 36 8,053 46 8,053 46 830 06 1,776 70 7 106 82 ,106 82 12,966 99 4,014 76 16,059 05 16,059 05 880 62 3,387 93 13,551 73 13,551 73 907 03 2,891 75 11,567 01 11,567 01 934 24 2,500 25 10 001 00 10,001 00 962 27 ,192 66 34,443 04 8,770 62 1 2 0 00 0 00 4,000 00 (200 00) 3,800 00 3,800 00 0 00 (200 00) 3,600 00 3,600 00 0 00 (200 00) 3,400 00 3,400 00 0 00 (200 00) 3,200 00 3,200 00 0 00 (200 00) 3,000 00 3,000 00 0 00 (200 00) 2,800 00 2,800 00 0 00 (200 00) 2,600 00 2,600 00 0 00 (200 00) 2,400 00 2,400 00 0 00 (200 00) 2,200 00 2,200 00 0 00 (200 00) 2,000 00 2,000 00 0 00 (200 00) 1,800 00 1,800 00 0 00 (200 00) 1,600 00 1,600 00 0 00 (200 00) 1,400 00 1,400 00 0 00 (200 00) 1,200 00 1,200 00 0 00 (200 00) 1,000 00 0 00 K U V W Jun 15 Jun 16 1 Jun 17 2 Jun 18 3 Jun 19 4 Jun 20 5 Jun 21 6 Jun 22 7 M Jun 23 8 N Jun 24 9 O Jun 25 10 P Jun 26 11 Q Jun 27 12 R Jun 28 13 S Jun 29 14 Jun 30 15 LA B 1 The Widget Company Inc (USD $m) 2 Calculations 129 Financing 130 131 Term loans 132 Term loan 1 Opening balance 135 Term loan 1 drawdown (start of year) 136 Term loan 1 repayment (end of year) Ending balance 133 0 00 4,000 00 (200 00) 3,800 00 3,800 00 0 00 (200 00) 3,600 00 3,600 00 0 00 (200 00) 3,400 00 3,400 00 0 00 (200 00) 3,200 00 3,200 00 0 00 (200 00) 3,000 00 3,000 00 0 00 (200 00) 2,800 00 2,800 00 0 00 (200 00) 2,600 00 2,600 00 0 00 (200 00) 2,400 00 2,400 00 0 00 (200 00) 2,200 00 2,200 00 0 00 (200 00) 2,000 00 2,000 00 0 00 (200 00) 1,800 00 1,800 00 0 00 (200 00) 1,600 00 1,600 00 0 00 (200 00) 1,400 00 1,400 00 0 00 (200 00) 1,200 00 1,200 00 0 00 (200 00) 1,000 00 0 00 Term loan 2 Opening balance Term loan 2 drawdown (start of year) Term loan 2 repayment (end of year) Ending balance 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 5,000 00 (250 00) 4,750 00 4,750 00 0 00 (250 00) 4,500 00 4,500 00 0 00 (250 00) 4,250 00 4,250 00 0 00 (250 00) 4,000 00 4,000 00 0 00 (250 00) 3,750 00 3,750 00 0 00 (250 00) 3,500 00 3,500 00 0 00 (250 00) 3,250 00 3,250 00 0 00 (250 00) 3,000 00 3,000 00 0 00 (250 00) 2,750 00 2,750 00 0 00 (250 00) 2,500 00 3,600 00 147 Term loan 3 Opening balance Term loan 3 drawdown (start of year) Term loan 3 repayment (end of year) Ending balance 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 6,000 00 (600 00) 5,400 00 5,400 00 0 00 (600 00) 4,800 00 4,800 00 0 00 (600 00) 4 200 00 0 00 4,200 00 0 00 (600 00) 3,600 00 149 (600 00) 3,000 00 Free Cash Flows Opening cash Cash Flows from Operating Activities Cash Flows from Investing Activities Free Cash Flow Available (Required) 156 0 00 9,911 53 7,124 97 4,613 382,814 171,585 834,619 77 755 213,889 453,933 574,272 02 4,598 955 ,777 595 ,947 41 9,464 00 648 96 674 92 695 17 716 02 11,185 44 759 63 10 219 21 14 449 94 11,733 45 9 580 57 8 129 14 18,548 86 11,326 81 2,175 36 5,556 76 782 42 ,514 54 1,612 91 5,822 83 8 05 89 8 241 63 1,558 33 6,006 67 830 06 8,395 06 1,589 01 7,069 35 12,966 99 21,625 35 5,315 07 7 182 97 880 62 13,378 65 2,465 73 4,486 96 907 03 7 859 73 1,361 95 1,650 66 7,007 137,247 47 9 34 24 962 27 9,303 32 9 860 41 8 159 0 00 Financing Cash Flows Equity contribution Term loan drawdown repayment Scheduled financing cash flows 2,500 00 3,800 00 6,300 00 0 00 (200 00) (200 00) 0 00 (200 00) (200 00) (200 00) (200 00) 0 00 (200 00) (200 00) 0 00 4,550 00 4,550 00 0 00 (450 00) (450 00) 0 00 (450 00) (450 00) 0 00 (450 00) (450 00) 0 00 (450 00) (450,00) 0 00 4,950 00 4,950 00 0 00 (1,050 00) (1,050 00) 0 00 (1,050 00) (1,050 00) 0 00 (1,050 00) (1 050 00) 0 00 (1,050 00) (1 050 00) 162 163 164 Cash Flow Available  (Required) 16,519 21 14,249 94 11,533 45 9,380 57 7 ,929 14 23,098 86 10,876 81 8 ,064 54 7,791 63 7,945 0626,575 35 12,328 65 6,809 73 8,253 32 8,810 41 0 00 Revolver Opening balance Borrowings Payments Ending balance 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 168 169 170 171 172 173 174 Equity Distribution to shareholders 6,607,68 7,124 97 6,920 076 ,566 40 6,343 31 18,479 09 8 ,701 45 6 ,451 63 6,233 30 6,356 05 21,260 28 9,862 92 5,447 78 6,602 66 7,048 33 U V W Jun 15 G Jun 17 2 H Jun 18 3 I Jun 19 4 J Jun 20 5 K Jun 21 6 L Jun 22 7 M Jun 23 8 N Jun 24 9 O Jun 25 10 P Jun 26 11 Q Jun 27 12 R Jun 28 13 S Jun 29 14   Jun 30 15 179 180 181 9,911 53 4 00  396 46 7,124 97 4 00  285 00 4,613 38 4 00  184 54 2,814 171,585 834,619 77 4 00  4 00  4 00  112 57 63 43 184 79 2,175 36 4 00  87 01 1,612 91 4 00  64 52 1,558 33 4 00  62 33 1,589 01 4 00  63 56 5,315 07 4 00  212 60 2,465 73 4 00  98 63 1 361 95 4 00  54 48 1 650 66 4 00  66 03 F 1 The Widget Company Inc (USD $m) Jun 16 2 Calculations 1 174 175 Interest 176 177 Cash on hand 178 Assume calculation based on opening balance Opening cash 0 00 Cash on hand 4 00  Interest revenue 0 00 182 Term loan 1 184 Assumes drawdowns happen at the start of the year, repayment at the end of the year 185 Opening balance 0 00 186 Term loan 1 drawdown (start of year) 4,000 00 187 Term loan 1 interest 9 00  188 Interest expense (360 00) 189 190 Term loan 2 191 Assumes drawdowns happen at the start of the year, repayment at the end of the year Opening balance 0 00 193 Term loan 2 drawdown (start of year) 0 00 194 Term loan 2 interest 10 00  Interest expense 0 00 183 3,800 00 0 00 9 00  (342 00) 3,600 00 0 00 9 00  (324 00) 3,400 00 0 00 9 00  (306 00) 3,200 00 0 00 9 00  (288 00) 3,000 00 0 00 9 00  (270 00) 2,800 00 0 00 9 00  (252 00) 2,600 00 0 00 9 00  (234 00) 2,400 00 0 00 9 00  (216 00) 2,200 00 0 00 9 00  (198 00) 2,000 00 0 00 9 00  (180 00) 1,800 00 0 00 9 00  (162 00) 1,600 00 0 00 9 00  144 00 1,400 00 0 00 9 00  (126 00) 1,200 00 0 00 9 00  (108 00) 192 0 00 4,500 00 4,000 00 0 00 0 00 0 00 0 00 10 00  0 00 0 00 0 00 10 00  0 00 0 00 0 00 10 00  0 00 0 00 10 00  0 00 5,000 00 10 00  (500 00) 4,750 00 0 00 10 00  (475 00) 4,250 00 0 00 10 00  (425 00) 3,750 00 0 00 10 00  (375 00) 3,500 00 0 00 10 00  (350 00) 3,250 00 0 00 10 00  325 00 3,000 00 0 00 10 00  (300 00) 2,750 00 0 00 10 00  (275 00) 195 10 00  (450 00) 0 00 10 00  (400 00) 0 00 Term loan 3 Assumes drawdowns happen at the start of the year, repayment at the end of the year Opening balance 0 00 Term loan 3 drawdown (start of year) 0 00 Term loan 3 interest 11 00  Interest expense 0 00 0 00 0 00 11 00  0 00 0 00 0 00 11 00  0 00 0 00 0 00 11 00  0 00 0 00 11 00  0 00 0 00 0 00 11 00  0 00 0 00 0 00 11 00  0 00 0 00 0 00 11 00  0 00 0 00 0 00 11 00  0 00 0 00 0 00 11 00  0 00 6,000 00 11 00  (412 50) 5,400 00 0 00 11 00  (385 00) 4,800 00 0 00 11 00  528 00 4,200 00 0 00 11 00  (330 00) 3,600 00 0 00 11 00  (302 50) 0 00 Revolver facility Assume calculation based on opening balance Opening balance Revolver facility Interest expense 0 00 13 00  0 00 0 00 13 00  0 00 0 00 13 00  0 00 0 00 13 00  0 00 0 00 13 00  0 00 0 00 13 00  0 00 0 00 13 00  0 00 0 00 13 00  0 00 0 00 13 00  0 00 0 00 13 00  0 00 0 00 13 00  0 00 0 00 13 00  0 00 0 00 13 00  0 00 0 00 13 00  0 00 0 00 13 00  0 00 209 210 211 Interest revenue (expense) (360 00) 54 46 (39 00) (121 46) (175 43) (706 57) (542 21) (596 99) (576 48) (535 67) (903 94) (684 40) 997 00 (701 52) (619 47) U V W E Jun 15 Jun 16 Jun 22 M Jun 23 A B 1 The Widget Company Inc (USD $m) 2 Calculations 211 212 Tax Jun 17 2 Jun 18 Jun 19 34 Jun 20 5 Jun 21 6 Jun 24 9 0 Jun 25 10 Jun 26 11 Q Jun 27 12 R Jun 28 13 Jun 29 14 Jun 30 15 213   30 00  Company tax rate ( ) Note   this model does not deal with tax losses 214 215 216 217 218 P L tax Profit (Loss) before tax 3,875 70 3,951 48 3,476 56 3,507 26 3,572 30 1,673 73 1,969 623,405 08 3,663 54 3,953 36 2,114 382,604 526,060 56 4,656 08 5,044 62 219 Income tax benefit (expense) (1,162 71) (1,185 44) (1,042 97) (1,052 18) (1,071 69) (502  12) (590 89) (1,021 52) (1,099 06) (1,186 01) (634 31) (781 36) (1,818 17) (1,396 82) (1,513 38) 220 221 222 223 224 225 Adjustments to arrive at taxable profit Adjustment for timing differences in depreciation  Accounting depreciation Tax depreciation Timing difference (1,352 00) (1,892 80) 540 80 (1,444 71) (1,644 03) 199 32 (1,541 13) (1,450 21) (90 92) (1,640 43) (1,299 20) (341 23) (1,742 72) (1,182 56) (560 16) (3,340 64) (3,183 14) (157 50) (3,449 16) (2,698 44) (750 72) (2,208 94) (2,315 23) 106 30 (2,231 35) (2,013 36) (217 99) (2,253 52) (1,776 70) (476 81) (4,006 64) (4,014 76) 8 13 (4,030 15) (3,387 93) (642 22) (2,561 80) (2,891 75) 329 95 (2,586 75) (2,500 25) (86 50) (2,612 44) (2,192 66) (419 79) 226 227 228 Adjusted profit before tax Tax payable 4,416 50 (1,324 95) 4 150 80 (1,245 24) 3,385 64 (1,015 69) 3,166 02 (949 81) 3,012,14 (903 64) 1,516 23 (454 87) 1,218 89 (365 67) 3,511 37 (1,053 41) 3,445 55 (1,033 66) 3,476 55 (1,042 96) 2,122 51 (636 75) 1,962 31 (588 69) 6,390,51 (1,917 15) 4,569 58 (1,370 87) 4,624 83 (1,387 45) 230 231 232 233 234 Depreciation timing differences Opening balance Timing difference Closing balance 0 00 540 80 540 80 540 80 199 32 740 12 7 40 12 (90 92) 649 21 649 21 (341 23) 307 97 307 97 (560 16) (252 19) (252 19) (157 50) (409 69) (409 69) (750 72) (1,160 41) (1,160 41) 106 30 (1,054 12) (1,054 12) (217 99) (1,272 11) (1,272 11) (476 81) (1,748 92) (1,748 92) 8 13 (1,740 79) (1,740 79) (642 22) (2,383 01) (2,383 01) 329 95 (2,053 06) (2,053 06) (86 50) (2,139 56) (2,139 56) (419 79) (2,559 35) 0 00 235 236 (162 24) (222 04) (194 76) (92 39) 75 66 122 91 22 91 348 12 348 12 316 24 316 24 381 63 524 68 524 68 522 24 522 24 714 90 714 90 6 15 92 615 92 641 87 767 81 222 04 0 00 Deferred tax assetliability (depreciation timing differences) 237 238 Deferred tax asset (depreciation timing differences) 239 Deferred tax liability (depreciation timing differences) 240 241 Sheet End 242 162 24 0 00 0 00 194 76 0 00 92 39 0 00 0 00 75 66 0 00 122 91 0 00 348 12 0 00 316 24 0 00 381 63 0 00 524 68 0 00 522 24 0 00 714 90 0 00 615 92 641 87 0 00 767 81 U V W sunny leon Jun 16 Jun 15 H Jun 18 elementele pe lumen Jun 17 Jun 19 dengan M Jun 23 Jun 20 tempat Jun 21 N Jun 24 bermain Jun 22 AB 1 The Widget Company Inc (USD $m) 2 Outputs 3 4 Profit and Loss luas Jun 25 dan luar negeringen Jun 26 Jun 28 som heter Jun 29 Jun 27 12 dengan Jun 30 bunga Sales Cost of Sales Gross Margin 51,000 00 (33,637 50) 17,362 50 53,570 40 (35,332 83) 18,237 57 56,270 35 (37,113 60) 19,156 74 58,538 04 (38,609 28) 19,928 76 60,897 13 (40,165 24) 20,731 89 63,351 28 (41,783 90) 21,567 38 65,904 34 (43,467 79) 22,436 55 68,560 28 (45,219 54) 23,340 74 71,323 26 (47,041 89) 24,281 37 74,197 59 (48,937 67) 25,259 91 77,187 75 (50,909 86) 26,277 89 80,298 42 (52,961 53) 27,336 89 83,534 44 (55,095 88) 28,438 56 86,900 88 (57,316 24) 29,584 64 90,402 99 (59,626 09) 30,776 90 Overhead Expenses Sales Marketing Other Total overhead expenses (8,444 80) (2,550 00) (780 00) (11,774 80) (8,870 42) (3,214 22) (811 20) (12,895 84) (9,317 49) (3,938 92) (843 65) (14,100 06) (9,692 98) (4,097 66) (868 96) (14,659 60) (10,083 61) (4 262 80) (895 03) (15,241 43) (10,489 98) (4,434 59) (921 88) (15,846 44) (10,912 72) (4,613 30) (949 53) (16,475 56) (11,352 51) (4,799 22) (978 02) (17,129 75) (11,810 01) (4,992 63) (1,007 36) (17,810 00) (12,285 96) (5,193 83) (1,037 58) (18,517 37) (12,781 08) (5,403 14) (1,068 71) (19,252 93) (13,296 16) (5,620 89) (1,100 77) (20,017 82) (13,831 99) (5,847 41) (1,133 79) (20,813 20) (14,389 42) (6,083 06) (1,167 81) (21,640 29) (14,969 32) (6,328 21) (1,202 84) (22,500 37) EBITDA 5,587 70 5,341 73 5,056 68 5 ,269 16 5,490 46 5,720 94 5,960 99 6,211 00 6,471 37 6,742 55 7,024 96 7,319 07 7,625 37 7,944 35 8 276 53 Depreciation Interest revenue (expense) Profit (Loss) before tax (1,352 00) (360 00) 3,875 70 (1,444 71) 54 4

Accepted Answer

The Answer is in the image, click to view ...

Question

M N O R S U V W X AB 1 The Widget Company Inc (USD $m) 2 Inputs E Jun 15 0 Jun 16

Step by Step Solution

Step: 1

Get Instant Access to Expert-Tailored Solutions

Step: 2

Step: 3

Ace Your Homework with AI

Recommended Textbook for

Handbook Of Blockchain Digital Finance And Inclusion

Students also viewed these Finance questions

Question

Question

Question

Question

Question

Question

Question

Question

Question

Question

Question

Question

Question

Question