Economics

profileStudy Hustler
Assignment1-Task2.xlsx
Home>Business & Finance homework help>Economics homework help>Economics

Content

Chapter 5. Examples
Table 1 Standardised balance sheets and averaged values Setting
Table 2 Standardised income statements Tax rate 30% If the tax rate is not equal to 30% update here
Table 3 Standardised cash flow statements
Table 4 Measuring overall profitability (Tables 5.1 - 5.5)
Table 5 Assessing operating management (Tables 5.6 - 5.8)
Table 6 Evaluating investment management (Tables 5.9 - 5.10)
Table 7 Evaluating financial management and assessing sustainable growth rate (Tables 5.11 - 5.13)
Table 8 Cash flow analysis (Table 5.14)
TWE Wines TWE Wines Enter the name of the company you are evaluating in the yellow highlighted area
2019 Enter the last year of data in the yellow highlighted area
Instructions for use:
Each of the following worksheets (Tables 1 - 8) are able to be updated for data found for your company.
Cells where calculations occur have been locked, however all other cells where data may be input
are open and are able to be updated/amended for your company's information
Please use this workbook to prepare the ratios necessary to evaluate your company's performance
You may find when entering data you would like an extra row for a new asset or liability or even expense item.
This is not possible, therefore you will have to make a professional judgement call on where to include the
value of interest within the existing construct of the spreadsheets.

Table 1

Standardised balance sheets
TWE Wines
As at 30 June 2019 2018 2017 2016 2015 2014
$000 $000 $000 $000 $000 $000
Assets
Cash and marketable securities 480 135 277 Manual entry
Trade receivables 661 593 607 Manual entry
Inventory 1,024 1,012 948 Manual entry
Other current assets 3 2 4 Manual entry
Total current assets 2,169 1,742 1,835 0 0 0
Non-current tangible assets 2,497 2,410 2,130 Manual entry
Non-current intangible assets 1,164 1,129 1,096 Manual entry
Deferred tax asset 152 155 208 Manual entry
Other non-current assets 19 11 10 Manual entry
Total non-current assets 3,832 3,704 3,444 0 0 0
Total assets 6,000 5,446 5,279 0 0 0
Liabilities
Current debt 141 100 112 Manual entry
Trade payables 725 703 663 Manual entry
Other current liabilities 19 7 4 Manual entry
Total current liabilities 885 809 779 0 0 0
Non-current debt 1,204 932 654 Manual entry
Deferred tax liability 194 191 234 Manual entry
Other non-current liabilities 11 18 4 Manual entry
Total non-current liabilities 1,409 1,140 891 0 0 0
Total liabilities 2,294 1,949 1,671 0 0 0
Equity
Minority interest 4 4 4 Manual entry
Shareholders' equity 3,702 3,492 3,604 Manual entry
Total shareholders' equity 3,706 3,496 3,609 0 0 0
Total liabilities and shareholders' equity 6,000 5,446 5,279 0 0 0
Error Assets do not equal Liab + Equity
Standardised balance sheets (averaged)
TWE Wines
As at 30 June 2019 2018 2017 2016 2015
$000 $000 $000 $000 $000
Assets
Cash and marketable securities 307.4 205.7 0.0 0.0 0.0
Trade receivables 627.1 599.8 0.0 0.0 0.0
Inventory 1,018.2 980.1 0.0 0.0 0.0
Other current assets 2.6 3.0 0.0 0.0 0.0
Total current assets 1,955.2 1,788.5 0.0 0.0 0.0
Non-current tangible assets 2,453.5 2,270.0 0.0 0.0 0.0
Non-current intangible assets 1,146.4 1,112.4 0.0 0.0 0.0
Deferred tax asset 153.4 181.3 0.0 0.0 0.0
Other non-current assets 14.6 10.4 0.0 0.0 0.0
Total non-current assets 3,767.8 3,574.0 0.0 0.0 0.0
Total assets 5,722.9 5,362.5 0.0 0.0 0.0
Liabilities 0.0 0.0 0.0
Current debt 120.6 106.2 0.0 0.0 0.0
Trade payables 713.8 682.7 0.0 0.0 0.0
Other current liabilities 12.9 5.5 0.0 0.0 0.0
Total current liabilities 847.2 794.4 0.0 0.0 0.0
Non-current debt 1,067.7 792.8 0.0 0.0 0.0
Deferred tax liability 192.5 212.4 0.0 0.0 0.0
Other non-current liabilities 14.4 10.7 0.0 0.0 0.0
Total non-current liabilities 1,274.6 1,015.8 0.0 0.0 0.0
Total liabilities 2,121.8 1,810.1 0.0 0.0 0.0
Equity 0.0 0.0 0.0
Minority interest 4.2 4.3 0.0 0.0 0.0
Shareholders' equity 3,597.0 3,548.2 0.0 0.0 0.0
Total shareholders' equity 3,601.2 3,552.4 0.0 0.0 0.0
Total liabilities and shareholders' equity 5,723.0 5,362.5 0.0 0.0 0.0

Table 2

Standardised income statements
TWE Wines
For year ended June 30 2019 2018 2017 2016 2015 2014
Name of accounts $000 $000 $000 $000 $000 $000
Sales 2,883 2,496 2,534 Manual entry
Cost of sales -1,661 -1,436 -1,568 Manual entry
Gross profit 1,222 1,061 966 0 0 0
SG&A expenses -565 -510 -516 Manual entry
Other operating income 0 0 0 Manual entry
Other operating expense 0 0 0 Manual entry
Operating profit 658 550 450 0 0 0
Investment income Manual entry
Other income 59 59 -46 Manual entry
Other expense -15 -42 -35 Manual entry
Interest income 47 28 20 Manual entry
Interest expense -99 -62 -47 Manual entry
Profit before tax 650 535 341 0 0 0
Tax expense -172 -115 -117 Manual entry
Profit for period 478 420 224 0 0 0

Table 3

Standardised cash flow statements
TWE Wines
For year ended June 30 2019 2018 2017 2016 2015 2014
Name of accounts $000 $000 $000 $000 $000 $000
Cash flows from operations 416 295 383 Manual entry
Net (investments in) or liquidation of operating working capital 1,362 992 1,133 Manual entry
Cash flow before working capital -947 -697 -750 0 0 0
After-tax net interest adjustment 46 28 23 Manual entry
Investing cash flows -58 -165 -130 Manual entry
Free cash flow available to debt and equity 403 158 276 0 0 0
After-tax net interest (outflow) inflow -46 -28 -23 0 0 0
Net debt issuance (net of repayments) 215 267 -3 Manual entry
Free cash flow available to equity 573 397 250 0 0 0
Dividends -245 -204 -185 Manual entry
Net issuance of shares (net of repurchases) -17 -343 -65 Manual entry
Net increase (decrease) in cash before FX 311 -150 -0 0 0 0
FX effects on cash balances 1 -2 -11 Manual entry
Net increase (decrease) in cash after FX 312 -151 -11 0 0 0

Table 4

Measuring overall profitability
Data items and ratio TWE Wines 2019 TWE Wines 2018 TWE Wines 2017 TWE Wines 2016 TWE Wines 2015 TWE Wines 2014
Net income ($'000) 478 420 224 0 0 0
Average shareholders' equity ($'000) 3,597 3,548 0 0 0 0
Return on equity 13.29% 11.83% 0.00% 0.00% 0.00% 0.00%
Modified DuPont decomposition of ROE
Values:
Sales 2,883 2,496 2,534 0 0 0
Interest expense -99 -62 -47 0 0 0
Interest income 47 28 20 0 0 0
Net interest expense (income) -52 -33 -27 0 0 0
Minority interest ($'000) 4 4 4 0 0 0
Average total assets ($'000) 5,723 5,363 0 0 0 0
Preferred dividends 245 204 185 Manual entry
Calculation:
Profit margin (PM) 17.99% 17.93% 9.74% 0.00% 0.00% 0.00%
Asset turnover (AT) 0.50 0.47 0.00 0.00 0.00 0.00
= Return on assets (ROA) 9.06% 8.34% 0.00% 0.00% 0.00% 0.00%
x Common earnings leverage (CEL) 0.450 0.483 0.158 0.000 0.000 0.000
x Capital structure leverage (CSL) 1.59 1.51 0.00 0.00 0.00 0.00
Adjusted financial leverage 0.72 0.73 0.00 0.00 0.00 0.00
= Return on equity (ROE) 6.49% 6.09% 0.00% 0.00% 0.00% 0.00%
Decomposing profitability: Alternative approach Item Definition
Values:
Net income 478.1 419.8 223.6 0.0 0.0 0.0 Net income Net operating income + Net unusual income
Net operating income 478.1 419.8 223.6 0.0 0.0 0.0 Net unusual income (NUI) Sum of non-operating items of income and expense on an after-tax basis. This includes items such as discontinued operatings, one-time items and extradinary profit and loss
Net unusual income (NUI) 0 0 0 Manual entry Net interest expense after tax (interest expense - Interest income) (1-Tax rate)
Net interest expense after tax 36.4 23.4 19.0 0.0 0.0 0.0 Net operating profit after tax (NOPAT) Net operating income +Net interest expense after tax
Net operating profit after tax (NOPAT) 514.5 443.2 242.6 0.0 0.0 0.0 Operating working capital (Current assets - Cash and marketable securities) - (Current liabilities - Short-term debt and current portion of long-term debt)
Net non-current assets Total non-current assets — Non-interest-bearing non-current liabilities
Current assets 2,168.5 1,741.8 1,835.2 0.0 0.0 0.0 Net debt Total interest-bearing liabilities —Cash and marketable securities
- Cash and marketable securities 480.1 134.6 276.8 0.0 0.0 0.0 Net assets Operating working capital + Net non-current assets
- (Current liabilities 885.0 809.4 779.3 0.0 0.0 0.0 Net capital Net debt + Shareholders' equity
- Short-term debt) 141.2 99.9 112.4 0.0 0.0 0.0
= Operating working capital 944.6 897.7 891.5 0.0 0.0 0.0
Total non-current assets 3,831.6 3,703.9 3,444.1 0.0 0.0 0.0
Deferred tax liability 194.1 190.8 233.9 0.0 0.0 0.0
Other non-current liabilities 11.3 17.5 3.8 0.0 0.0 0.0
Non-interest-bearing non-current liabilities 205.4 208.3 237.7 0.0 0.0 0.0
Net non-current assets 3,626.2 3,495.6 3,206.4 0.0 0.0 0.0
Net debt 864.8 897.0 489.4 0.0 0.0
Net assets 4,570.8 4,393.3 4,097.9 0.0 0.0
Shareholders' equity 3,702.0 3,492.0 3,604.3 0.0 0.0
Net capital 4,566.8 4,389.0 4,093.7 0.0 0.0
Calculation:
Net operating profit margin 17.85% 17.75% 9.57% 0.00% 0.00%
x Net operating asset turnover 0.63 0.57 0.62 0.00 0.00
= Operating ROA 11.26% 10.09% 5.92% 0.00% 0.00%
Spread 7.05% 7.48% 2.04% 0.00% 0.00%
x Net financial leverage 0.23 0.26 0.14 0.00 0.00
= Financial leverage gain 1.65% 1.92% 0.28% 0.00% 0.00%
Unexpected ROE (UROE) 0.00% 0.00% 0.00% 0.00% 0.00% Manual entry
ROE = Operating ROA + Financial leverage +Unexpected ROE 12.90% 12.01% 6.20% 0.00% 0.00%

Table 5

Assessing operating management
TWE Wines
2019 2018 2017 2016 2015 2014
$000 $000 $000 $000 $000 $000
Common-sized income statements (Ratios as a percentage of sales)
Values:
Sales 2,883 2,496 2,534 0 0 0
Cost of sales 1,661 1,436 1,568 0 0 0
SG&A expenses 565 510 516 0 0 0
Other 44 18 82 0 0 0
Interest 52 33 27 0 0 0
Tax expense 172 115 117 0 0 0
Net income 478 420 224 0 0 0
Common-sized income statements (Ratios as a percentage of sales)
Cost of sales 0.576 0.575 0.619 0.000 0.000 0.000
SG&A expenses 0.196 0.204 0.204 0.000 0.000 0.000
Other 0.015 0.007 0.032 0.000 0.000 0.000
Interest 0.018 0.013 0.011 0.000 0.000 0.000
Tax expense 0.059 0.046 0.046 0.000 0.000 0.000
Net income 0.166 0.168 0.088 0.000 0.000 0.000
Gross margin ratios
Gross profit margin 42.39% 42.49% 38.11% 0.00% 0.00% 0.00%
Mark-up 73.59% 73.89% 61.59% 0.00% 0.00% 0.00%
Decompositions of profit margin
EBIT 702 568 368 0 0 0
Margin (Sales-COGS) 1,222 1,061 966 0 0 0
Profit before leverage 515 443 243 0 0 0
EBIT coverage 73.33% 77.98% 65.92% 0.00% 0.00% 0.00%
EBIT margin 24.34% 22.76% 14.52% 0.00% 0.00% 0.00%
Operating efficiency 57.40% 53.57% 38.10% 0.00% 0.00% 0.00%
Production efficiency 42.39% 42.49% 38.11% 0.00% 0.00% 0.00%
Margins
Values:
EBIT 702 568 368 0 0 0
Depreciation expense 89 86 99 0 0 0 Manual entry
EBITDA 791 654 467 0 0 0
NOPAT 515 443 243 0 0 0
Margin ratios:
EBIT margin 24.34% 22.76% 14.52% 0.00% 0.00% 0.00%
EBITDA margin 27.43% 26.20% 18.44% 0.00% 0.00% 0.00%
NOPAT margin 17.85% 17.75% 9.57% 0.00% 0.00% 0.00%

Table 6

Evaluating Investment Management
TWE Wines
2019 2018 2017 2016 2015 2014
$000 $000 $000 $000 $000 $000
Asset turnover ratios
Values:
Sales 2,883 2,496 2,534 0 0 0
COGS 1,661 1,436 1,568 0 0 0
Average Cash 307 206 0 0 0 0
Average receivables 627 600 0 0 0 0
Average inventory 1,018 980 0 0 0 0
Average fixed assets 2,453 2,270 0 0 0 0
Average total assets 5,723 5,363 0 0 0 0
Net non-current assets 3,626 3,496 3,206 0 0 0
Turnover ratios:
Total assets turnover 0.50 0.47 0.00 0.00 0.00 0.00
Cash turnover 9.38 12.14 0.00 0.00 0.00 0.00
Receivables turnover 4.60 4.16 0.00 0.00 0.00 0.00
Inventory turnover 2.83 2.55 0.00 0.00 0.00 0.00
Fixed asset turnover 1.18 1.10 0.00 0.00 0.00 0.00
Asset management ratios
Operating working capital/sales 0.2787 0.3196 0.3074 0.0000 0.0000 0.0000
Net non-current assets/sales 1.2351 1.3423 0.6326 0.0000 0.0000 0.0000
PPE/Sales 0.8510 0.9093 0.0000 0.0000 0.0000 0.0000
Operating working capital turnover 3.59 3.13 3.25 0.00 0.00 0.00
Net non-current asset turnover 0.81 0.74 1.58 0.00 0.00 0.00
PPE turnover 1.18 1.10 0.00 0.00 0.00 0.00
Accounts receivable turnover 4.36 4.21 4.18 0.00 0.00 0.00
Inventory turnover 1.62 1.42 1.65 0.00 0.00 0.00
Accounts payable turnover 2.29 2.04 2.37 0.00 0.00 0.00
Days receivable 83.71 86.70 87.35 0.00 0.00 0.00
Days inventory 225.05 257.38 220.61 0.00 0.00 0.00
Days payable 159.27 178.71 154.19 0.00 0.00 0.00

Table 7

Evaluating financial management
TWE Wines
2019 2018 2017 2016 2015 2014
$000 $000 $000 $000 $000 $000
Liquidity ratios
Current ratio 2.45 2.15 2.35 0.00 0.00 0.00
Quick ratio 1.29 0.90 1.13 0.00 0.00 0.00
Cash ratio 0.54 0.17 0.36 0.00 0.00 0.00
Operating cash flow ratio 0.47 0.36 0.49 0.00 0.00 0.00
Liabilities to equity 0.620 0.558 0.464 0.000 0.000 0.000
Debt to equity 0.363 0.295 0.213 0.000 0.000 0.000
Net debt to equity 0.234 0.257 0.136 0.000 0.000 0.000
Debt to capital 0.266 0.228 0.175 0.000 0.000 0.000
Net debt to equity 0.189 0.204 0.120 0.000 0.000 0.000
Interest coverage (earnings based) 13.492 17.015 13.579 0.000 0.000 0.000
Interest coverage (cash flow based) 10.998 12.437 16.096 0.000 0.000 0.000
Sustainable growth rate
ROE 13.29% 11.83% 0.00% 0.00% 0.00% 0.00%
Dividend payout ratio 51.18% 48.52% 82.56% 0.00% 0.00% 0.00%
Sustainable growth rate 6.49% 6.09% 0.00% 0.00% 0.00% 0.00%
Interest expense 52 33 27 0 0 0
Interest paid 44 27 22 0 0 0 Manual entry
Income tax paid (received) 113 94 32 0 0 0 Manual entry
Net cash flows from operations 416 295 383 0 0 0
Interest coverage (cash flow based) 10.998 12.437 16.096 0.000 0.000 0.000

Table 8

Cash flow analysis
Line item (in '$000) TWE Wines
2019 2018 2017 2016 2015 2014
$000 $000 $000 $000 $000 $000
Cash flow from operations 416 295 383 0 0 0
Net (investments in) or liquidation of operating working capital -6 -19 -779 0 0 0
Cash flow before working capital 421 314 1,162 0 0 0
After-tax net interest expense (income) 46 28 23 0 0 0
Operating cash flow before investment in long-term assets 462 323 406 0 0 0
Net (investment in) or liquidation of operating long-term assets -58 -165 -130 0 0 0
Free cash flow available to debt and equity 403 158 276 0 0 0
After-tax net interest (expense or) income -46 -28 -23 0 0 0
Net debt (repayment or) issuance 215 267 -3 0 0 0
Free cash flow available to equity 573 397 250 0 0 0
Dividend (payments) -245 -204 -185 0 0 0
Net stock (repurchase or) issuance -17 -343 -65 0 0 0
Net increase (decrease) in cash balance before FX 311 -150 -0 0 0 0
FX effects on cash balances 1 -2 -11 0 0 0 Manual entry
Net of bank overdraft 0 0 0 0 0 0 Manual entry
Increase (decrease) in cash balances after FX 312 -151 -11 0 0 0
TWE Wines
2019 2018 2017 2016 2015 2014
Working capital 803 798 779 0 0 0