acc excel
Irenee1
CompanySpreadsheet2019.xlsFinancial Statements
| Documentation | ||||||||
| Rotork | This is an area where we can put documentation. | |||||||
| Statements of Movements in Equity | ||||||||
| Years ended 31 Dec | ||||||||
| 2018 | 2017 | 2016 | 2015 | |||||
| £'000 | £'000 | £'000 | £'000 | |||||
| Opening Balance | 457,200 | 434,086 | 407,802 | 376,795 | ||||
| Profit for the year | 91,744 | 55,613 | 67,173 | 74,857 | ||||
| Other comprehensive income | ||||||||
| Foreign exchange translation differences | 3,164 | (376) | 36,854 | (6,511) | ||||
| Effective portion of changes in fair value of cash flow hedges | (24) | 7,546 | (7,822) | (1,790) | ||||
| Actuarial gain on defined benefit pension plans | 9,501 | 5,849 | (37,923) | 9,704 | ||||
| Tax on other comprehensive income | (1,428) | (3,498) | 8,599 | (1,313) | ||||
| Total other comprehensive income | 11,213 | 9,521 | (292) | 90 | ||||
| Total comprehensive income | 102,957 | 65,134 | 66,881 | 74,947 | ||||
| Transactions with owners, recorded directly in equity | ||||||||
| Equity settled share-based payment transactions | 2,457 | 1,089 | 1,557 | (1,447) | ||||
| Tax on equity settled share-based payment transactions | 98 | 252 | 74 | (799) | ||||
| Share options exercised by employees | 1,837 | 713 | 465 | 599 | ||||
| Own ordinary shares acquired | (4,850) | (1,157) | (1,019) | (2,785) | ||||
| Own ordinary shares awarded under share schemes | 2,217 | 2,301 | 2,202 | 4,257 | ||||
| Dividends | (48,288) | (45,218) | (43,876) | (43,765) | ||||
| Closing Balance | 513,628 | 457,200 | 434,086 | 407,802 | ||||
| Notes Area | ||||||||
| Rotork | This area is designed as a space to write notes to yourself. Hopefully this will save you the hassle of attempting to find the pen and paper that is somewhere in the bottom of your bag. | |||||||
| Balance Sheets | ||||||||
| as at 31 Dec | ||||||||
| 2018 | 2017 | 2016 | 2015 | |||||
| £'000 | £'000 | £'000 | £'000 | |||||
| Non-current assets | ||||||||
| Goodwill | 230,157 | 228,028 | 251,407 | 222,086 | ||||
| Intangible assets | 61,517 | 81,456 | 109,019 | 118,555 | ||||
| Property, plant and equipment | 79,338 | 81,725 | 83,766 | 72,008 | ||||
| Deferred tax assets | 17,337 | 21,218 | 25,259 | 13,698 | ||||
| Other receivables | 352 | 142 | 146 | 2,234 | ||||
| Total non-current assets | 388,701 | 412,569 | 469,597 | 428,581 | ||||
| Current assets | ||||||||
| Inventories | 94,739 | 91,908 | 85,772 | 87,210 | ||||
| Trade receivables | 145,509 | 145,529 | 131,891 | 118,801 | ||||
| Current tax | 1,429 | 2,726 | 4,349 | 4,458 | ||||
| Derivative financial instruments | 308 | 3,468 | 0 | 25 | ||||
| Other receivables | 23,161 | 19,202 | 22,341 | 13,225 | ||||
| Cash and cash equivalents | 104,489 | 63,192 | 61,423 | 48,968 | ||||
| Total current assets | 369,635 | 326,025 | 305,776 | 272,687 | ||||
| Total assets | 758,336 | 738,594 | 775,373 | 701,268 | ||||
| Equity | ||||||||
| Issued equity capital | 4,358 | 4,352 | 4,350 | 4,349 | ||||
| Share premium | 13,024 | 11,193 | 10,482 | 10,018 | ||||
| Reserves | 35,421 | 32,263 | 26,451 | (3,989) | ||||
| Retained earnings | 460,825 | 409,392 | 392,803 | 397,424 | ||||
| Total equity | 513,628 | 457,200 | 434,086 | 407,802 | ||||
| Non-current liabilities | ||||||||
| Interest bearing loans and borrowings | 30,871 | 45,879 | 51,303 | 69,756 | ||||
| Employee benefits | 31,274 | 52,293 | 62,593 | 26,320 | ||||
| Deferred tax liabilities | 15,722 | 19,379 | 24,848 | 28,973 | ||||
| Derivative financial instruments | 0 | 245 | 2,483 | 431 | ||||
| Provisions | 2,149 | 1,929 | 11,947 | 11,990 | ||||
| Total non-current liabilities | 80,016 | 119,725 | 153,174 | 137,470 | ||||
| Current liabilities | ||||||||
| Interest bearing loans and borrowings | 30,010 | 29,928 | 65,108 | 50,352 | ||||
| Trade payables | 47,332 | 49,183 | 39,652 | 36,724 | ||||
| Employee benefits | 26,489 | 21,464 | 14,256 | 11,118 | ||||
| Current tax | 11,792 | 13,093 | 13,352 | 14,276 | ||||
| Derivative financial instruments | 2,682 | 1,521 | 8,143 | 3,601 | ||||
| Other payables | 40,150 | 42,165 | 41,999 | 34,612 | ||||
| Provisions | 6,237 | 4,315 | 5,603 | 5,313 | ||||
| Total current liabilities | 164,692 | 161,669 | 188,113 | 155,996 | ||||
| Total liabilities | 244,708 | 281,394 | 341,287 | 293,466 | ||||
| Total equity and liabilities | 758,336 | 738,594 | 775,373 | 701,268 | ||||
| Rotork | ||||||||
| Income Statements | ||||||||
| Years ended 31 Dec | ||||||||
| 2018 | 2017 | 2016 | 2015 | |||||
| £'000 | £'000 | £'000 | £'000 | |||||
| Revenue | 695,713 | 642,229 | 590,078 | 546,459 | ||||
| Cost of sales | (384,253) | (358,090) | (328,410) | (296,944) | ||||
| Gross profit | 311,460 | 284,139 | 261,668 | 249,515 | ||||
| Other income | 8,990 | 10,651 | 629 | 427 | ||||
| Distribution costs | (7,260) | (6,271) | (5,138) | (4,613) | ||||
| Administrative expenses | (189,474) | (202,233) | (163,165) | (140,877) | ||||
| Other expenses | (789) | (314) | (217) | (66) | ||||
| Adjusted operating profit | 146,015 | 130,162 | 120,588 | 125,272 | ||||
| Adjustments | ||||||||
| – Amortisation of acquired intangible assets | (20,284) | (27,183) | (26,811) | (20,886) | ||||
| – Other adjustments | (2,813) | (17,007) | 0 | 0 | ||||
| Operating profit | 122,918 | 85,972 | 93,777 | 104,386 | ||||
| Finance income | 2,278 | 1,381 | 1,744 | 1,740 | ||||
| Finance expense | (4,448) | (6,767) | (4,451) | (4,257) | ||||
| Profit before tax | 120,748 | 80,586 | 91,070 | 101,869 | ||||
| Income tax expense | (29,004) | (24,973) | (23,897) | (27,012) | ||||
| Profit for the year | 91,744 | 55,613 | 67,173 | 74,857 | ||||
| Other comprehensive income | ||||||||
| Items that may be subsequently reclassified to the income statement: | ||||||||
| Foreign exchange translation differences | 3,164 | (376) | 36,854 | (6,511) | ||||
| Effective portion of changes in fair value of cash flow hedges net of tax | (6) | 6,188 | (6,414) | (1,448) | ||||
| 3,158 | 5,812 | 30,440 | (7,959) | |||||
| Items that are not subsequently reclassified to the income statement: | ||||||||
| Actuarial gain in pension scheme net of tax | 8,055 | 3,709 | (30,732) | 8,049 | ||||
| Income and expenses recognised directly in equity | 11,213 | 9,521 | (292) | 90 | ||||
| Total comprehensive income for the year | 102,957 | 65,134 | 66,881 | 74,947 | ||||
| Working Area | ||||||||
| This is a place where you can copy and paste formulas, play with calculations and generally have fun whilst being able to see the data you are currently working on. |
Martin: Insert currency used in your firm's financial statements and it will change the whole spreadsheet for you.
Martin:
You may need to adjust the years
Martin:
You may need to adjust the years
Martin:
You may need to adjust the years
Martin:
You may need to adjust the years
Restated Financial Statements
| Documentation | |||||||||||||||
| Rotork | This is an area where we can put documentation. | ||||||||||||||
| Restated Statements of Movements in Equity | |||||||||||||||
| Years ended 31 Dec | |||||||||||||||
| 2004 | 2005 | 2006 | 2007 | 2008 | 2009 | 2018 | 2017 | 2016 | 2015 | ||||||
| $'000 | $'000 | $'000 | $'000 | $'000 | $'000 | £'000 | £'000 | £'000 | £'000 | ||||||
| Notes Area | |||||||||||||||
| This area is designed as a space to write notes to yourself. Hopefully this will save you the hassle of attempting to find the pen and paper that is somewhere in the bottom of your bag. | |||||||||||||||
| Rotork | |||||||||||||||
| Restated Statements of Financial Position | |||||||||||||||
| as at 31 Dec | |||||||||||||||
| 2004 | 2005 | 2006 | 2007 | 2008 | 2009 | 2018 | 2017 | 2016 | 2015 | ||||||
| $'000 | $'000 | $'000 | $'000 | $'000 | $'000 | £'000 | £'000 | £'000 | £'000 | ||||||
| Rotork | |||||||||||||||
| Restated Statements of Financial Performance | |||||||||||||||
| Years ended 31 Dec | |||||||||||||||
| 2004 | 2005 | 2006 | 2007 | 2008 | 2009 | 2018 | 2017 | 2016 | 2015 | ||||||
| $'000 | $'000 | $'000 | $'000 | $'000 | $'000 | £'000 | £'000 | £'000 | £'000 | ||||||
| Working Area | |||||||||||||||
| This is a place where you can copy and paste formulas, play with calculations and generally have fun whilst being able to see the data you are currently working on. |
Ratios
| Documentation | |||||||||
| Rotork | This is an area where we can put documentation. | ||||||||
| RATIOS | |||||||||
| Years ended 31 Dec | |||||||||
| 2018 | 2017 | 2016 | 2015 | ||||||
| Profitability Ratios | |||||||||
| Gross Profit Margin | Gross profit/sales | 19.26% | |||||||
| Net Profit Margin | Net profit after tax/sales | ||||||||
| Return on Assets | Net profit after tax/total assets | ||||||||
| Efficiency (or Asset Management) Ratios | |||||||||
| Inventory Turnover Ratio | Cost of goods sold/ending inventory | 189.20% | |||||||
| Days of Inventory | Inventory/av.daily cost of goods sold | ||||||||
| Total Asset Turnover Ratio | Sales/total assets | ||||||||
| Current Asset Turnover Ratio | Sales/current assets | ||||||||
| Average Collection Period (Days Receivable) | Accounts receivable/av.daily credit sales | ||||||||
| Liquidity Ratios | |||||||||
| Current Ratio | Current assets/current liabilities | Notes Area | |||||||
| Quick Ratio 1 | (Current assets - inventory - prepayments)/current liabilities | 0.90% | This area is designed as a space to write notes to yourself. Hopefully this will save you the hassle of attempting to find the pen and paper that is somewhere in the bottom of your bag. | ||||||
| Quick Ratio 2 | (Current assets - inventory - prepayments - receivables)/current liabilities | ||||||||
| Financial Structure Ratios | |||||||||
| Debt/Equity Ratio | Debt/equity | ||||||||
| Equity Ratio | Equity/total assets | ||||||||
| Times Interest Earned | Earnings before interest & tax/interest | ||||||||
| Market Ratios | |||||||||
| Earnings per Share (EPS) | Net profit after tax/nos of issued ordinary shares | ||||||||
| Dividends per Share (DPS) | Dividends/number of issued ordinary shares | ||||||||
| Dividend Yield Ratio | Dividends per share/market price per share | ||||||||
| Price Earnings Ratio | Market price per share/earnings per share | ||||||||
| Net Asset Backing per Share Ratio | Net assets (owners equity)/number of shares issued | ||||||||
| Market/Book Ratio | Market price per share/net asset backing per share | 5.20% | |||||||
| Ratios Based on Reformulated Financial Statements | |||||||||
| Dividend Payout Ratio | Dividends/comprehensive income | ||||||||
| Return on Equity (ROE) | Comprehensive Income/shareholders' equity | ||||||||
| Return on Net Operating Assets (RNOA) | Operating income after tax (OI)/net operating assets (NOA) | ||||||||
| Net Borrowing Cost (NBC) | Net fin. expenses after tax/net financial obligations | ||||||||
| Profit Margin (PM) | Operating income after tax (OI)/sales | ||||||||
| Operating Liability Leverage (OLLEV) | Av.operating liabilities/av.net operating assets | ||||||||
| Financial Leverage (FLEV) | Av.net financial obligations/av.shareholders' equity | ||||||||
| Return on Operating Assets (ROOA) | (OI after tax + implicit interest after tax)/av.operating assets | ||||||||
| Operating Liability Leverage Spread (OLSPREAD) | ROOA - short-term borrowing rate (after tax) | ||||||||
| Asset Turnover (ATO) | Sales/net operating assets (NOA) | ||||||||
| Growth in Sales | Change in sales/prior period's sales | ||||||||
| Growth in Operating Income | Change in OI after tax/prior period's OI after tax | ||||||||
| Growth in Net Operating Assets | Change in NOA/opening NOA | ||||||||
| Growth in Shareholders' Equity | Change in shareholders' equity/opening shareholders' equity | ||||||||
| Economic profit | (RNOA - cost of capital) x net operating assets (NOA) | ||||||||
| Working Area | |||||||||
| This is a place where you can copy and paste formulas, play with calculations and generally have fun whilst being able to see the data you are currently working on. |
Working
| Working Area | Notes Area | |||||||||||||
| This is a place where you can copy and paste forumlas, play with calculations and generally have fun without messing up your other worksheets. | This area is designed as a space to write notes to yourself. Hopefully this will save you the hassle of attempting to find the pen and paper that is somewhere in the bottom of your bag. |
NPV & IRR
| EXAMPLE FOR WESFARMERS | |||||||||||
| ALL FIGURES ARE EXPRESSED IN MILLIONS AUD | |||||||||||
| OPTION 1: BUNNINGS | |||||||||||
| Time period | 0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 |
| Cashflow | -77 | -4 | 5 | 15 | 20 | 20 | 20 | 25 | 25 | 10 | 13 |
| NPV | $5.88 | ||||||||||
| IRR | 11.4% | ||||||||||
| Time period | 0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 |
| Cashflow | -77 | -4 | 5 | 15 | 20 | 20 | 20 | 25 | 25 | 10 | 13 |
| Cumulative Cashflow | -77 | -81 | -76 | -61 | -41 | -21 | -1 | 24 | 49 | 59 | 72 |
| Payback Period | 6 years | 14.6 | days | ||||||||
| 0.48 | months | ||||||||||
| 0.04 | years | ||||||||||
| OPTION 2: COLES | |||||||||||
| Time period | 0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 |
| Cashflow | -135 | -5 | -1 | 10 | 15 | 15 | 20 | 20 | 20 | 25 | 13.5 |
| NPV | -$66.61 | ||||||||||
| IRR | -0.3% | ||||||||||
| Time period | 0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 |
| Cashflow | -135 | -5 | -1 | 10 | 15 | 15 | 20 | 20 | 20 | 25 | 13.5 |
| Cumulative Cashflow | -135 | -140 | -141 | -131 | -116 | -101 | -81 | -61 | -41 | -16 | -2.5 |
| Payback Period | Investment is never paid back within 10 years |
