Question
Complete your answers in this excel workbook. Once you have finished your answers, upload your solutions to the Canvas to be marked. For each item
Complete your answers in this excel workbook. Once you have finished your answers, upload your solutions to the Canvas to be marked.
For each item in the Tab "Income Statement", apply the best method to forecast that item for the next year. Justify the method you used. Be aware of significant relationships between the sheets that may allow you to forecast items in other ways.
Tips: For each item, you should consider the growth rate over the 5 previous years. You should also apply the common size method. Then, you can compare the results to tell which one is more reasonable.
\begin{tabular}{|l|r|r|r|r|r|r|} \hline & 2018 & 2019 & 2020 & 2021 & 2022 \\ \hline Total Operating Revenue & 244147 & 266538 & 268460 & 245521 & 255882 \\ \hline & & & & & \\ \hline Contracted Services for Port Operations & -53634 & -48178 & -51807 & -52700 & -54985 \\ \hline Employee Benefit Expenses & -32927 & -41549 & -42662 & -32101 & -33958 \\ Direct Fuel and Power Expenses & -9951 & -12329 & -10032 & -6995 & -7175 \\ \hline Maintence of Property, Plant and Equipment & -9905 & -14605 & -15026 & -10021 & -8759 \\ \hline Other Expenses & -14631 & -17605 & -16441 & -13961 & -12615 \\ \hline Operating Expenses & -121048 & -134266 & -135968 & -115778 & -117492 \\ \hline EBITDA & 123099 & 132272 & 132492 & 129743 & 138390 \\ \hline Depreciation and Amoritisation & -18558 & -22549 & -24954 & -23752 & -24267 \\ \hline Impairment of PPE & 0 & 0 & 0 & 0 & 0 \\ & -18558 & -22549 & -24954 & -23752 & -24267 \\ \hline EBIT & 104541 & 109723 & 107538 & 105991 & 114123 \\ \hline Finance Income & 2123 & 1124 & 2259 & 666 & 434 \\ \hline Finance Expense & -17987 & -15406 & -17092 & -17006 & -17205 \\ Net Finance Costs & -15864 & -14282 & -14833 & -16340 & -16771 \\ \hline Gain on Sale of Associate & 38214 & & & & \\ \hline Share of Profit from Associates & 10360 & 9370 & 12686 & 13437 & 13995 \\ \hline Earnings before Tax & 137251 & 104811 & 105391 & 103088 & 111347 \\ \hline Income Tax & -25128 & -26559 & -26243 & -25774 & -27906 \\ Net Profit & 112123 & 78252 & 79148 & 77314 & 83441 \\ \hline & & & & & \\ \hline Dividends paid & -63035 & -63035 & -69419 & -72142 & -108893 \\ Adjusted dividends paid & -63035 & -63035 & -69419 & -72142 & -74893 \\ \hline Retained Earnings & 49088 & 15217 & 9729 & 5172 & 8548 \\ \hline \end{tabular} \begin{tabular}{|l|r|r|r|r|r|} \hline & 2018 & 2019 & 2020 & 2021 & 2022 \\ \hline Current Assets & & & & & \\ \hline Cash and cash equivalents & 37218 & 1560 & 17918 & 11580 & 5184 \\ \hline Recievables, Prepayments and Other & 35882 & 39629 & 41370 & 41685 & 44555 \\ \hline Total Current & 73100 & 41189 & 59288 & 53265 & 49739 \\ \hline Non Current Assets & & & & & \\ \hline Property, Plant and Equipment & 946929 & 998742 & 1097401 & 1127366 & 1227223 \\ \hline Intangible Assets & 42637 & 43873 & 21357 & 18426 & 18019 \\ \hline Investments in Associates & 49915 & 71079 & 118972 & 123290 & 127583 \\ \hline Total Non-Current & 1039481 & 1113694 & 1237730 & 1269082 & 1372861 \\ \hline Total Assets & 1112581 & 1154883 & 1297018 & 1322347 & 1422600 \\ \hline & & & & & \\ \hline Liabilities & & & & & \\ \hline Trade and Other Payables & 21561 & 19601 & 20742 & 30107 & 31343 \\ \hline Loans and Borrowings & 226079 & 256331 & 305362 & 320200 & 380363 \\ \hline Derivative Financial Instruments & 9504 & 4549 & 9361 & 18501 & 9900 \\ \hline Deferred Tax Liabilities & 56604 & 58188 & 70276 & 63955 & 64829 \\ \hline Other & 4955 & 3795 & 3727 & 3920 & 4222 \\ \hline Total Liabilities & 318703 & 342464 & 409468 & 436683 & 490657 \\ \hline & & & & & \\ \hline Equity & & & & & \\ \hline Share Capital & 68381 & 68397 & 68267 & 68262 & 68276 \\ \hline Reserves & 591903 & 595211 & 660743 & 653710 & 724944 \\ \hline Retained Earnings & 133594 & 148811 & 158540 & 163712 & 138723 \\ \hline Total Equity & 793878 & 812419 & 887550 & 885684 & 931943 \\ \hline \end{tabular} CF' s from Financing Activities Change in Loans and Borrowings Change in Share Equity Dividends Paid Other Net Cash used in Financing Activities \begin{tabular}{|r|r|r|r|r|} \hline 29534 & 30155 & 50180 & 15157 & 60189 \\ -40 & 16 & -248 & -347 & 0 \\ \hline-63035 & -63035 & -69419 & -72142 & -108893 \\ \hline-33541 & -53364 & & 222 & -183 \\ \hline \end{tabular} Net (decrease)/Increase in cash held Cash at Beginning Ending Cash \begin{tabular}{|r|r|r|r|r|} \hline 7974 & -35658 & 16358 & -6338 & -6396 \\ \hline 9244 & 37218 & 1560 & 17918 & 11580 \\ \hline 37218 & 1560 & 17918 & 11580 & 5184 \\ \hline \end{tabular} \begin{tabular}{|l|r|r|r|r|r|r|} \hline & 2018 & 2019 & 2020 & 2021 & 2022 \\ \hline Total Operating Revenue & 244147 & 266538 & 268460 & 245521 & 255882 \\ \hline & & & & & \\ \hline Contracted Services for Port Operations & -53634 & -48178 & -51807 & -52700 & -54985 \\ \hline Employee Benefit Expenses & -32927 & -41549 & -42662 & -32101 & -33958 \\ Direct Fuel and Power Expenses & -9951 & -12329 & -10032 & -6995 & -7175 \\ \hline Maintence of Property, Plant and Equipment & -9905 & -14605 & -15026 & -10021 & -8759 \\ \hline Other Expenses & -14631 & -17605 & -16441 & -13961 & -12615 \\ \hline Operating Expenses & -121048 & -134266 & -135968 & -115778 & -117492 \\ \hline EBITDA & 123099 & 132272 & 132492 & 129743 & 138390 \\ \hline Depreciation and Amoritisation & -18558 & -22549 & -24954 & -23752 & -24267 \\ \hline Impairment of PPE & 0 & 0 & 0 & 0 & 0 \\ & -18558 & -22549 & -24954 & -23752 & -24267 \\ \hline EBIT & 104541 & 109723 & 107538 & 105991 & 114123 \\ \hline Finance Income & 2123 & 1124 & 2259 & 666 & 434 \\ \hline Finance Expense & -17987 & -15406 & -17092 & -17006 & -17205 \\ Net Finance Costs & -15864 & -14282 & -14833 & -16340 & -16771 \\ \hline Gain on Sale of Associate & 38214 & & & & \\ \hline Share of Profit from Associates & 10360 & 9370 & 12686 & 13437 & 13995 \\ \hline Earnings before Tax & 137251 & 104811 & 105391 & 103088 & 111347 \\ \hline Income Tax & -25128 & -26559 & -26243 & -25774 & -27906 \\ Net Profit & 112123 & 78252 & 79148 & 77314 & 83441 \\ \hline & & & & & \\ \hline Dividends paid & -63035 & -63035 & -69419 & -72142 & -108893 \\ Adjusted dividends paid & -63035 & -63035 & -69419 & -72142 & -74893 \\ \hline Retained Earnings & 49088 & 15217 & 9729 & 5172 & 8548 \\ \hline \end{tabular} \begin{tabular}{|l|r|r|r|r|r|} \hline & 2018 & 2019 & 2020 & 2021 & 2022 \\ \hline Current Assets & & & & & \\ \hline Cash and cash equivalents & 37218 & 1560 & 17918 & 11580 & 5184 \\ \hline Recievables, Prepayments and Other & 35882 & 39629 & 41370 & 41685 & 44555 \\ \hline Total Current & 73100 & 41189 & 59288 & 53265 & 49739 \\ \hline Non Current Assets & & & & & \\ \hline Property, Plant and Equipment & 946929 & 998742 & 1097401 & 1127366 & 1227223 \\ \hline Intangible Assets & 42637 & 43873 & 21357 & 18426 & 18019 \\ \hline Investments in Associates & 49915 & 71079 & 118972 & 123290 & 127583 \\ \hline Total Non-Current & 1039481 & 1113694 & 1237730 & 1269082 & 1372861 \\ \hline Total Assets & 1112581 & 1154883 & 1297018 & 1322347 & 1422600 \\ \hline & & & & & \\ \hline Liabilities & & & & & \\ \hline Trade and Other Payables & 21561 & 19601 & 20742 & 30107 & 31343 \\ \hline Loans and Borrowings & 226079 & 256331 & 305362 & 320200 & 380363 \\ \hline Derivative Financial Instruments & 9504 & 4549 & 9361 & 18501 & 9900 \\ \hline Deferred Tax Liabilities & 56604 & 58188 & 70276 & 63955 & 64829 \\ \hline Other & 4955 & 3795 & 3727 & 3920 & 4222 \\ \hline Total Liabilities & 318703 & 342464 & 409468 & 436683 & 490657 \\ \hline & & & & & \\ \hline Equity & & & & & \\ \hline Share Capital & 68381 & 68397 & 68267 & 68262 & 68276 \\ \hline Reserves & 591903 & 595211 & 660743 & 653710 & 724944 \\ \hline Retained Earnings & 133594 & 148811 & 158540 & 163712 & 138723 \\ \hline Total Equity & 793878 & 812419 & 887550 & 885684 & 931943 \\ \hline \end{tabular} CF' s from Financing Activities Change in Loans and Borrowings Change in Share Equity Dividends Paid Other Net Cash used in Financing Activities \begin{tabular}{|r|r|r|r|r|} \hline 29534 & 30155 & 50180 & 15157 & 60189 \\ -40 & 16 & -248 & -347 & 0 \\ \hline-63035 & -63035 & -69419 & -72142 & -108893 \\ \hline-33541 & -53364 & & 222 & -183 \\ \hline \end{tabular} Net (decrease)/Increase in cash held Cash at Beginning Ending Cash \begin{tabular}{|r|r|r|r|r|} \hline 7974 & -35658 & 16358 & -6338 & -6396 \\ \hline 9244 & 37218 & 1560 & 17918 & 11580 \\ \hline 37218 & 1560 & 17918 & 11580 & 5184 \\ \hline \end{tabular}Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started