Assignment:
Build “live” (i.e. change of inputs should result in change of result) financial model of an asset from scratch on a separate tab. Model should be in nominal terms (i.e. including inflation). Make it as detailed as possible given provided information and at the same time make it as convenient to use and review as possible. Outputs should contain pro-forma Profit & Loss and Cash Flow statements. Keep it simple, but informative, please.
Description of an asset: zinc mine with one pit, one crusher and one milling and floatation plant. Final product – zinc concentrate. There is a stockpile of ore before crushing stage – asume weighted average grade of feed to crusher per period. There are no stockpiles at other stages.
Data inputs
Items Spec Units Jan-15 Feb-15 Mar-15 Apr-15 May-15 Jun-15 Jul-15 Aug-15 Sep-15 Oct-15 Nov-15 Dec-15 2015 Jan-16 Feb-16 Mar-16 Apr-16 May-16 Jun-16 Jul-16 Aug-16 Sep-16 Oct-16 Nov-16 Dec-16 2016 Jan-17 Feb-17 Mar-17 Apr-17 May-17 Jun-17 Jul-17 Aug-17 Sep-17 Oct-17 Nov-17 Dec-17 2017 Jan-18 Feb-18 Mar-18 Apr-18 May-18 Jun-18 Jul-18 Aug-18 Sep-18 Oct-18 Nov-18 Dec-18 2018 Jan-19 Feb-19 Mar-19 Apr-19 May-19 Jun-19 Jul-19 Aug-19 Sep-19 Oct-19 Nov-19 Dec-19 2019 2020 2021 2022
Ore mined Nominal BCM 259,510 215,249 297,521 184,807 280,961 254,249 258,786 422,262 255,979 329,289 282,985 387,183 3,428,781 288,660 297,609 418,103 454,966 670,321 359,293 333,957 273,874 368,171 299,297 353,749 252,407 4,370,407 251,030 259,938 260,063 264,708 266,250 324,984 377,355 330,469 383,594 206,612 286,353 335,156 3,546,512 378,300 343,027 375,476 376,867 365,001 339,903 354,751 345,522 374,749 401,441 380,824 372,910 4,408,771 352,565 322,398 314,834 320,204 373,838 323,233 304,828 260,356 327,748 303,980 319,459 319,522 3,842,965 3,856,432 4,131,055 418,773
Ore density Nominal DMT/BCM 2.7 2.7 2.7 2.7 2.7 2.7 2.7 2.7 2.8 2.3 1.8 2.3 2.6 2.5 2.6 2.6 2.8 2.8 2.8 2.8 2.8 2.8 2.8 2.8 2.8 2.7 2.8 2.8 2.8 2.7 2.7 2.8 2.6 2.7 2.6 2.8 2.8 2.7 2.7 2.6 2.7 2.6 2.6 2.6 2.8 2.6 2.7 2.6 2.5 2.6 2.6 2.6 2.6 2.6 2.7 2.7 2.8 2.8 2.8 2.8 2.8 2.8 2.8 2.8 2.7 2.8 2.8 2.8
Zinc contained mined Nominal t 7,552 6,356 8,393 5,555 8,806 8,336 10,420 16,108 8,555 7,542 4,884 13,256 105,761 11,018 11,733 12,539 12,165 25,982 11,163 14,576 8,735 14,519 12,070 13,846 9,385 157,729 6,476 8,384 5,537 9,575 6,619 11,310 13,030 10,807 9,007 6,183 7,655 15,944 110,526 11,461 8,772 7,695 9,642 8,990 10,942 8,059 10,168 8,896 9,715 10,800 11,723 116,863 9,585 12,159 9,938 10,114 9,345 9,608 11,951 11,584 7,873 8,207 10,780 8,566 119,711 121,992 141,137 9,913
Waste mined total, including Nominal BCM 1,229,979 1,204,282 1,423,499 1,251,156 1,393,489 1,418,028 1,348,698 1,029,561 1,039,030 1,091,567 673,063 907,293 14,009,644 760,253 628,344 630,420 382,035 194,580 300,708 193,043 253,126 141,828 227,703 156,250 274,593 4,142,883 583,688 569,961 570,039 565,324 733,751 674,992 822,661 869,531 1,116,406 1,293,540 1,213,796 1,164,846 10,178,535 1,121,641 1,155,002 1,126,118 1,122,493 1,134,974 1,159,686 1,146,299 1,153,709 1,125,646 1,090,862 1,120,726 1,134,429 13,591,585 1,145,639 1,176,998 1,185,264 1,177,252 1,111,667 1,006,250 956,046 1,059,453 805,174 775,116 773,352 810,494 11,982,705 3,835,384 1,344,359 218,750
Waste mined operational Nominal BCM 1,229,979 1,204,282 1,423,499 1,251,156 1,393,489 1,418,028 1,348,698 882,686 1,037,467 792,348 562,516 689,992 13,234,139 687,377 584,924 561,804 382,035 194,580 296,020 193,043 250,001 141,828 227,703 156,250 274,593 3,950,158 392,829 499,709 284,184 358,793 203,712 437,869 409,776 290,407 255,756 1,293,540 1,213,796 1,164,846 10,178,535 386,189 463,345 373,434 523,072 525,577 659,111 482,724 562,214 571,469 525,462 607,266 637,376 6,317,239 488,182 550,530 512,601 486,685 468,575 449,762 749,079 709,013 590,983 643,271 773,352 692,846 7,179,106 3,835,384 1,344,359 218,750
Waste mined capitalised Nominal BCM 0 0 0 0 0 0 0 146,875 1,563 299,219 110,547 217,301 775,505 72,876 43,420 68,616 0 0 4,688 0 3,125 0 0 0 0 192,725 190,859 70,252 285,855 206,531 530,039 237,123 412,885 579,124 860,650 0 0 0 0 735,452 691,657 752,684 599,421 609,397 500,575 663,575 591,495 554,177 565,400 513,460 497,053 7,274,346 657,457 626,468 672,663 690,567 643,092 556,488 206,967 350,440 214,191 131,845 0 117,648 4,803,599 0 0 0
Waste density Nominal DMT/BCM 2.33 2.29 2.20 2.25 2.38 2.28 2.49 2.58 2.76 2.42 2.56 2.46 2.40 2.64 2.66 2.63 2.76 2.76 2.74 2.77 2.75 2.77 2.76 2.77 2.76 2.70 2.34 1.92 1.95 1.84 1.66 1.69 1.55 1.57 1.63 2.63 2.75 1.73 1.98 1.65 1.69 1.99 2.01 2.00 2.23 2.47 2.47 2.51 2.52 2.60 2.64 2.23 2.62 2.63 2.69 2.71 2.75 2.48 2.75 2.77 2.77 2.77 2.77 2.76 2.70 2.76 2.76 2.76
Inflation rate Nominal % 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 2.20% 2.20% 2.20% 2.20% 2.20% 2.20% 2.20% 2.20% 2.20% 2.20% 2.20% 2.20% 2.20% 2.20% 2.20% 2.20% 2.20% 2.20% 2.20% 2.20% 2.20% 2.20% 2.20% 2.20% 2.20% 2.20% 2.40% 2.40% 2.40% 2.40% 2.40% 2.40% 2.40% 2.40% 2.40% 2.40% 2.40% 2.40% 2.40% 1.50% 1.50% 1.50% 1.50% 1.50% 1.50% 1.50% 1.50% 1.50% 1.50% 1.50% 1.50% 1.50% 1.80% 1.80% 1.80%
Mining recovery (% of in-situ ore mined and delivered to crusher) Nominal % 90% 90% 90% 90% 90% 90% 90% 90% 90% 90% 90% 90% 90% 90% 90% 90% 90% 90% 90% 90% 90% 90% 90% 90% 90% 90% 90% 90% 90% 90% 90% 90% 90% 90% 90% 90% 90% 90% 90% 90% 90% 90% 90% 90% 90% 90% 90% 90% 90% 90% 90% 90% 90% 90% 90% 90% 90% 90% 90% 90% 90% 90% 90% 90% 90% 90% 90% 90%
Mining dilution (% of waste material blended into the ore mined and delivered to crusher) Nominal % 15% 15% 15% 15% 15% 15% 15% 15% 15% 15% 15% 15% 15% 15% 15% 15% 15% 15% 15% 15% 15% 15% 15% 15% 15% 15% 15% 15% 15% 15% 15% 15% 15% 15% 15% 15% 15% 15% 15% 15% 15% 15% 15% 15% 15% 15% 15% 15% 15% 15% 15% 15% 15% 15% 15% 15% 15% 15% 15% 15% 15% 15% 15% 15% 15% 15% 15% 15%
Benchmark price (benchmark) scenario 1 Real $/t 5,816 5,702 5,926 6,028 6,301 5,834 5,457 5,089 5,208 5,320 5,320 4,800 5,567 4,463 4,595 4,948 4,570 4,570 4,570 4,330 4,330 4,330 4,600 4,600 4,600 4,542 4,892 4,892 4,892 4,892 4,892 4,892 4,892 4,892 4,892 4,892 4,892 4,892 4,892 5,494 5,494 5,494 5,494 5,494 5,494 5,494 5,494 5,494 5,494 5,494 5,494 5,494 6,025 6,025 6,025 6,025 6,025 6,025 6,025 6,025 6,025 6,025 6,025 6,025 6,025 6,473 6,473 6,473
Benchmark price (benchmark) scenario 2 Real $/t 5,306 5,202 5,406 5,500 5,748 5,322 4,979 4,643 4,752 4,854 4,854 4,379 5,079 4,072 4,192 4,514 4,169 4,169 4,169 3,950 3,950 3,950 4,197 4,197 4,197 4,144 4,950 4,950 4,950 4,950 4,950 4,950 4,950 4,950 4,950 4,950 4,950 4,950 4,950 5,007 5,007 5,007 5,007 5,007 5,007 5,007 5,007 5,007 5,007 5,007 5,007 5,007 5,187 5,187 5,187 5,187 5,187 5,187 5,187 5,187 5,187 5,187 5,187 5,187 5,187 5,136 6,110 7,040
Mining cost variable Real $/BCM ore 7.86 7.86 7.86 7.86 7.86 7.86 7.86 7.86 7.86 7.86 7.86 7.86 7.86 7.86 7.86 7.86 7.86 7.86 7.86 7.86 7.86 7.86 7.86 7.86 7.86 7.86 7.86 7.86 7.86 7.86 7.86 7.86 7.86 7.86 7.86 7.86 7.86 7.86 7.86 7.86 7.86 7.86 7.86 7.86 7.86 7.86 7.86 7.86 7.86 7.86 7.86 7.86 7.86 7.86 7.86 7.86 7.86 7.86 7.86 7.86 7.86 7.86 7.86 7.86 7.86 7.86 7.86 7.86
Mining cost variable Real $/BCM waste 7.86 7.86 7.86 7.86 7.86 7.86 7.86 7.86 7.86 7.86 7.86 7.86 7.86 7.86 7.86 7.86 7.86 7.86 7.86 7.86 7.86 7.86 7.86 7.86 7.86 7.86 7.86 7.86 7.86 7.86 7.86 7.86 7.86 7.86 7.86 7.86 7.86 7.86 7.86 7.86 7.86 7.86 7.86 7.86 7.86 7.86 7.86 7.86 7.86 7.86 7.86 7.86 7.86 7.86 7.86 7.86 7.86 7.86 7.86 7.86 7.86 7.86 7.86 7.86 7.86 7.86 7.86 7.86
Mining cost fixed Real $/period 1,220,000 1,220,000 1,220,000 1,220,000 1,220,000 1,220,000 1,220,000 1,220,000 1,220,000 1,220,000 1,220,000 1,220,000 14,640,000 1,220,000 1,220,000 1,220,000 1,220,000 1,220,000 1,220,000 1,220,000 1,220,000 1,220,000 1,220,000 1,220,000 1,220,000 14,640,000 1,220,000 1,220,000 1,220,000 1,220,000 1,220,000 1,220,000 1,220,000 1,220,000 1,220,000 1,220,000 1,220,000 1,220,000 14,640,000 1,220,000 1,220,000 1,220,000 1,220,000 1,220,000 1,220,000 1,220,000 1,220,000 1,220,000 1,220,000 1,220,000 1,220,000 14,640,000 1,220,000 1,220,000 1,220,000 1,220,000 1,220,000 1,220,000 1,220,000 1,220,000 1,220,000 1,220,000 1,220,000 1,220,000 14,640,000 14,640,000 14,640,000 14,640,000
Processing cost variable Real $/DMT feed 3.17 5.33 4.87 5.40 3.82 2.78 3.10 3.24 3.58 3.27 3.33 3.32 3.56 3.93 3.67 3.60 3.75 3.73 3.75 3.73 3.73 3.75 3.73 3.75 3.73 3.74 3.32 3.34 3.32 3.33 3.32 3.33 3.32 3.32 3.33 3.32 3.33 3.32 3.32 3.32 3.34 3.32 3.33 3.32 3.33 3.32 3.32 3.33 3.32 3.33 3.32 3.32 3.32 3.34 3.32 3.33 3.32 3.33 3.32 3.32 3.33 3.32 3.33 3.32 3.32 3.32 3.32 3.32
Processing cost fixed Real $/period 1,239,808 1,239,808 1,239,808 1,239,808 1,239,808 1,239,808 1,239,808 1,239,808 1,239,808 1,239,808 1,239,808 1,239,808 14,877,696 1,239,808 1,239,808 1,239,808 1,239,808 1,239,808 1,239,808 1,239,808 1,239,808 1,239,808 1,239,808 1,239,808 1,239,808 14,877,696 1,239,808 1,239,808 1,239,808 1,239,808 1,239,808 1,239,808 1,239,808 1,239,808 1,239,808 1,239,808 1,239,808 1,239,808 14,877,696 1,239,808 1,239,808 1,239,808 1,239,808 1,239,808 1,239,808 1,239,808 1,239,808 1,239,808 1,239,808 1,239,808 1,239,808 14,877,696 1,239,808 1,239,808 1,239,808 1,239,808 1,239,808 1,239,808 1,239,808 1,239,808 1,239,808 1,239,808 1,239,808 1,239,808 14,877,696 14,877,696 14,877,696 14,877,696
Concentrate moisture Nominal % 10% 10% 10% 10% 10% 10% 10% 10% 10% 10% 10% 10% 10% 10% 10% 10% 10% 10% 10% 10% 10% 10% 10% 10% 10% 10% 10% 10% 10% 10% 10% 10% 10% 10% 10% 10% 10% 10% 10% 10% 10% 10% 10% 10% 10% 10% 10% 10% 10% 10% 10% 10% 10% 10% 10% 10% 10% 10% 10% 10% 10% 10% 10% 10% 10% 10% 10% 10%
Crusher feed rate Nominal DMT/h 1200 1200 1200 1200 1200 1200 1200 1200 1200 1200 1200 1200 1200 1200 1200 1200 1200 1200 1200 1200 1200 1200 1200 1200 1200 1200 1200 1200 1200 1200 1200 1200 1200 1200 1200 1200 1200 1200 1200 1200 1200 1200 1200 1200 1200 1200 1200 1200 1200 1200 1200 1200 1200 1200 1200 1200 1200 1200 1200 1200 1200 1200 1200 1200 1200 1200 1200 1200
Crusher utilisation Nominal % 90% 90% 90% 90% 90% 90% 90% 90% 90% 90% 90% 90% 90% 90% 90% 90% 90% 90% 90% 90% 90% 90% 90% 90% 90% 90% 90% 90% 90% 90% 90% 90% 90% 90% 90% 90% 90% 90% 90% 90% 90% 90% 90% 90% 90% 90% 90% 90% 90% 90% 90% 90% 90% 90% 90% 90% 90% 90% 90% 90% 90% 90% 90% 90% 90% 90% 90% 90%
Corporate income tax rate Nominal % 20% 20% 20% 20% 20% 20% 20% 20% 20% 20% 20% 20% 20% 20% 20% 20% 20% 20% 20% 20% 20% 20% 20% 20% 20% 20% 20% 20% 20% 20% 20% 20% 20% 20% 20% 20% 20% 20% 20% 20% 20% 20% 20% 20% 20% 20% 20% 20% 20% 20% 20% 20% 20% 20% 20% 20% 20% 20% 20% 20% 20% 20% 20% 20% 20% 20% 20% 20%
Realisation price payable Nominal % 93% 93% 93% 93% 93% 93% 93% 93% 93% 93% 93% 93% 93% 93% 93% 93% 93% 93% 93% 93% 93% 93% 93% 93% 93% 93% 93% 93% 93% 93% 93% 93% 93% 93% 93% 93% 93% 93% 93% 93% 93% 93% 93% 93% 93% 93% 93% 93% 93% 93% 93% 93% 93% 93% 93% 93% 93% 93% 93% 93% 93% 93% 93% 93% 93% 93% 93% 93%
Export duties Real $/WMT conc 16.00 16.00 16.00 16.00 16.00 16.00 16.00 16.00 16.00 16.00 16.00 16.00 16.00 16.00 16.00 16.00 16.00 16.00 16.00 16.00 16.00 16.00 16.00 16.00 16.00 16.00 16.00 16.00 16.00 16.00 16.00 16.00 16.00 16.00 16.00 16.00 16.00 16.00 16.00 16.00 16.00 16.00 16.00 16.00 16.00 16.00 16.00 16.00 16.00 16.00 16.00 16.00 16.00 16.00 16.00 16.00 16.00 16.00 16.00 16.00 16.00 16.00 16.00 16.00 16.00 16.00 16.00 16.00
Other sales taxes Real $/WMT conc 37.00 37.00 37.00 37.00 37.00 37.00 37.00 37.00 37.00 37.00 37.00 37.00 37.00 37.00 37.00 37.00 37.00 37.00 37.00 37.00 37.00 37.00 37.00 37.00 37.00 37.00 37.00 37.00 37.00 37.00 37.00 37.00 37.00 37.00 37.00 37.00 37.00 37.00 37.00 37.00 37.00 37.00 37.00 37.00 37.00 37.00 37.00 37.00 37.00 37.00 37.00 37.00 37.00 37.00 37.00 37.00 37.00 37.00 37.00 37.00 37.00 37.00 37.00 37.00 37.00 37.00 37.00 37.00
G&A fixed Real $/period 1,675,000 1,675,000 1,675,000 1,675,000 1,675,000 1,675,000 1,675,000 1,675,000 1,675,000 1,675,000 1,675,000 1,675,000 20,100,000 1,675,000 1,675,000 1,675,000 1,675,000 1,675,000 1,675,000 1,675,000 1,675,000 1,675,000 1,675,000 1,675,000 1,675,000 20,100,000 1,675,000 1,675,000 1,675,000 1,675,000 1,675,000 1,675,000 1,675,000 1,675,000 1,675,000 1,675,000 1,675,000 1,675,000 20,100,000 1,675,000 1,675,000 1,675,000 1,675,000 1,675,000 1,675,000 1,675,000 1,675,000 1,675,000 1,675,000 1,675,000 1,675,000 20,100,000 1,675,000 1,675,000 1,675,000 1,675,000 1,675,000 1,675,000 1,675,000 1,675,000 1,675,000 1,675,000 1,675,000 1,675,000 20,100,000 20,100,000 20,100,000 20,100,000
Working capital assumptions
Accounts receivable turnover Nominal days 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10
Accounts payable turnover Nominal days 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10
Inventory turnover Nominal days 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30
Capex sustaining for property plant and equipment (PPE) Nominal $ 1,172,809 1,527,248 1,777,265 654,999 1,806,534 4,198,330 4,383,846 4,396,282 6,108,852 3,361,796 3,118,058 4,342,855 36,848,874 1,572,986 1,001,725 1,865,042 7,660,106 8,139,401 1,694,274 2,893,272 1,205,812 1,341,064 959,402 468,763 665,534 29,467,381 2,201,954 2,201,954 2,201,954 2,201,954 2,201,954 2,201,954 2,201,954 2,201,954 2,201,954 2,201,954 2,201,954 2,201,954 26,423,451 2,235,179 2,235,179 2,235,179 2,235,179 2,235,179 2,235,179 2,235,179 2,235,179 2,235,179 2,235,179 2,235,179 2,235,179 26,822,145 2,846,830 2,846,830 2,846,830 2,846,830 2,846,830 2,846,830 2,846,830 2,846,830 2,846,830 2,846,830 2,846,830 2,846,830 34,161,962 29,709,770 25,282,812 25,167,180
Capitalised waste mining costs Nominal $ to be calculated using assumptions above
Depreciation assumptions
Starting PPE balance Nominal $mln 300
PPE average depreciation period Nominal years 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
Capitalised mining cost depreciation method Nominal pro-rata on the basis of subsequent ore mining volumes
Dividends Nominal % of net profit 5% 5% 5% 5% 5% 5% 5% 5% 5% 5% 5% 5% 5% 5% 5% 5% 5% 5% 5% 5% 5% 5% 5% 5% 5% 5% 5% 5% 5% 5% 5% 5% 5% 5% 5% 5% 5% 5% 5% 5% 5% 5% 5% 5% 5% 5% 5% 5% 5% 5% 5% 5% 5% 5% 5% 5% 5% 5% 5% 5% 5% 5% 5% 5% 5% 5% 5% 5%