acc excel
Irenee1
CompanySpreadsheet2019.xlsFinancial Statements
| Documentation | ||||||||
| [Insert Company Name] | This is an area where we can put documentation. | |||||||
| Statements of Movements in Equity | ||||||||
| Years ended [insert balance date, e.g. 30 June] | ||||||||
| 2018 | 2017 | 2016 | 2015 | |||||
| $'000 | $'000 | $'000 | $'000 | |||||
| Notes Area | ||||||||
| [Insert Company Name] | 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 [insert balance date, e.g. 30 June] | ||||||||
| 2018 | 2017 | 2016 | 2015 | |||||
| $'000 | $'000 | $'000 | $'000 | |||||
| [Insert Company Name] | ||||||||
| Income Statements | ||||||||
| Years ended [insert balance date, e.g. 30 June] | ||||||||
| 2018 | 2017 | 2016 | 2015 | |||||
| $'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. |
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 | |||||||||||||||
| [Insert Company Name] | This is an area where we can put documentation. | ||||||||||||||
| Restated Statements of Movements in Equity | |||||||||||||||
| Years ended [insert balance date, e.g. 30 June] | |||||||||||||||
| 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. | |||||||||||||||
| [Insert Company Name] | |||||||||||||||
| Restated Statements of Financial Position | |||||||||||||||
| as at [insert balance date, e.g. 30 June] | |||||||||||||||
| 2004 | 2005 | 2006 | 2007 | 2008 | 2009 | 2018 | 2017 | 2016 | 2015 | ||||||
| $'000 | $'000 | $'000 | $'000 | $'000 | $'000 | $'000 | $'000 | $'000 | $'000 | ||||||
| [Insert Company Name] | |||||||||||||||
| Restated Statements of Financial Performance | |||||||||||||||
| Years ended [insert balance date, e.g. 30 June] | |||||||||||||||
| 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 | |||||||||
| [Insert Company Name] | This is an area where we can put documentation. | ||||||||
| RATIOS | |||||||||
| Years ended [insert balance date, e.g. 30 June] | |||||||||
| 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 |
