Need Blue boxes solved

Year index -3 0 Year (replace with your firm's year end dates) 29-Jun-18 29-Jun-19 29-Jun-20 29-Jun-21 29-Jun-22 29-Jun-23 29-Jun-24 29-Jun-25 29-Jun-26 Revenue growth rate (For past: calculate based on past reve 0.1478158 0.0540208 0.2276635 0.1131212 0.0348482 -0.0304298 0.0283371 0.0383831 Revenue (For past: retrieve from P&L. For forec 2,792,523 3,205,302 3,378,455 4,147,606 4,616,788 4,777,675 4,632,291 4,763,557 4,946,397 Net Income (For past: retrieve from P&L. For for 375,378 402,317 480,541 841,414 936,596 969,234 939,741 966,370 1,003,463 Depreciation & amortisation expense (calculate based on growth rates, or estimate k 431,821 480,669 497,420 482,283 495,950 514,986 Tangible and intangible capital assets, carrying amount (For past: retrieve from balar 1,321,531 1,471,024 1,522,287 1,475,964 1,517,788 1,576,046 CapEx (calculate based on above net capital assets and other items) 630,162 548,682 435,960 537,774 573,243 Operating net working capital (For past: retrieve from balance sheet. For forecast: cal 3,893,019 4,333,402 4,484,413 4,347,953 4,471,162 4,642,779 DeltaNWC (calculate based on above operating working capital and other items) 440,383 151,011 -136,460 123,208 171,617 Interest expense (calculate based on growth rates, or estimate based on forecast P&1 9,272 10,321 10,681 10,356 10,649 11,058 tc, corporate tax rate (feel free to change this if you wish) 0.3 0.3 0.3 0.3 0.3 0.3 OFCF (calculate based on above items) 353,944 774,437 1,129,772 808,792 781,329 rf (retrieve from online source (e.g., Reserve Bank of Australia Website) to get 10 yea betaE (retrieve from reputable online source such as Reuters) MRP (retrieve from reputable online source such as Fernandez, Pablo and bauls, sot rE (calculate based on CAPM) 0.018 0.87 0.064 0.07368 Debt (book value. Retrieve from balance sheet) 1,738,634 rD draft estimate based on calculation of InterestExpenseOverYearo / BookDebtOfYe 0.0033354 rD draft estimate using alternative method such as traded corporate bond yields ava 0.016 rD final estimate, used as an input into WACC after tax 0.0096677 Equity (traded market value, retrieve from stock exchange) 6,143,000 6.143B Equity (book value, retrieve from balance sheet) 3,864,829 D/V final estimate. This debt-to-assets ratio will be used as input into WACC after t 0.2205931 WACC after tax. Calculate using corporate tax rate, rd final estimate, rE, D/V ratio, ar 0.0589195 Do not use this WACC after tax in any calculations. Please 'copy, paste special, value' the WACC after tax in the above cell to this questic 0.0589195 Use this WACC after tax in all below calculations. PV of OFCF for each year 1 to 4. Calculate using above data, ensure WACC after tax from Q3i is used. Terminal value as at year 4 based on perpetuity of year 5 OFCF growing at year 5 revenue growth rate forever. Calculate using above data. PV of the Terminal Value based on perpetuity. Calculate using above data Assets (DCF model estimated value. Calculate using above data) Equity (DCF model estimated value. Calculate using above data) Units of all above cash flows. Retrieve from financial statements. For example, if in m 1,000 Number of shares. Retrieve from online source or financial statements. Ensure consis 1,246,045 Share price in dollars per one share (DCF model estimated share price. Calculate base Share price in dollars per one share (traded market share price. Retrieve from online 4.93 NPV in dollars of buying one share assuming DCF model is correct and market price is not correct. Calculate based on above data 0 Year index -3 0 Year (replace with your firm's year end dates) 29-Jun-18 29-Jun-19 29-Jun-20 29-Jun-21 29-Jun-22 29-Jun-23 29-Jun-24 29-Jun-25 29-Jun-26 Revenue growth rate (For past: calculate based on past reve 0.1478158 0.0540208 0.2276635 0.1131212 0.0348482 -0.0304298 0.0283371 0.0383831 Revenue (For past: retrieve from P&L. For forec 2,792,523 3,205,302 3,378,455 4,147,606 4,616,788 4,777,675 4,632,291 4,763,557 4,946,397 Net Income (For past: retrieve from P&L. For for 375,378 402,317 480,541 841,414 936,596 969,234 939,741 966,370 1,003,463 Depreciation & amortisation expense (calculate based on growth rates, or estimate k 431,821 480,669 497,420 482,283 495,950 514,986 Tangible and intangible capital assets, carrying amount (For past: retrieve from balar 1,321,531 1,471,024 1,522,287 1,475,964 1,517,788 1,576,046 CapEx (calculate based on above net capital assets and other items) 630,162 548,682 435,960 537,774 573,243 Operating net working capital (For past: retrieve from balance sheet. For forecast: cal 3,893,019 4,333,402 4,484,413 4,347,953 4,471,162 4,642,779 DeltaNWC (calculate based on above operating working capital and other items) 440,383 151,011 -136,460 123,208 171,617 Interest expense (calculate based on growth rates, or estimate based on forecast P&1 9,272 10,321 10,681 10,356 10,649 11,058 tc, corporate tax rate (feel free to change this if you wish) 0.3 0.3 0.3 0.3 0.3 0.3 OFCF (calculate based on above items) 353,944 774,437 1,129,772 808,792 781,329 rf (retrieve from online source (e.g., Reserve Bank of Australia Website) to get 10 yea betaE (retrieve from reputable online source such as Reuters) MRP (retrieve from reputable online source such as Fernandez, Pablo and bauls, sot rE (calculate based on CAPM) 0.018 0.87 0.064 0.07368 Debt (book value. Retrieve from balance sheet) 1,738,634 rD draft estimate based on calculation of InterestExpenseOverYearo / BookDebtOfYe 0.0033354 rD draft estimate using alternative method such as traded corporate bond yields ava 0.016 rD final estimate, used as an input into WACC after tax 0.0096677 Equity (traded market value, retrieve from stock exchange) 6,143,000 6.143B Equity (book value, retrieve from balance sheet) 3,864,829 D/V final estimate. This debt-to-assets ratio will be used as input into WACC after t 0.2205931 WACC after tax. Calculate using corporate tax rate, rd final estimate, rE, D/V ratio, ar 0.0589195 Do not use this WACC after tax in any calculations. Please 'copy, paste special, value' the WACC after tax in the above cell to this questic 0.0589195 Use this WACC after tax in all below calculations. PV of OFCF for each year 1 to 4. Calculate using above data, ensure WACC after tax from Q3i is used. Terminal value as at year 4 based on perpetuity of year 5 OFCF growing at year 5 revenue growth rate forever. Calculate using above data. PV of the Terminal Value based on perpetuity. Calculate using above data Assets (DCF model estimated value. Calculate using above data) Equity (DCF model estimated value. Calculate using above data) Units of all above cash flows. Retrieve from financial statements. For example, if in m 1,000 Number of shares. Retrieve from online source or financial statements. Ensure consis 1,246,045 Share price in dollars per one share (DCF model estimated share price. Calculate base Share price in dollars per one share (traded market share price. Retrieve from online 4.93 NPV in dollars of buying one share assuming DCF model is correct and market price is not correct. Calculate based on above data 0