Assignment Question: 

1. 1.     The Basic SpreadsheetModel (5 marks)
   1. a.     Download the FinAnalysis data for Woolworths from UTS Online.
   2. b.     Use the Lab 9 solution spreadsheet(on UTS Online) as a template for your financial model. Be aware that FinAnalysis uses different terms for some items, so take care inmapping theWoolworths financial data into the year 0 spreadsheet values.
   3. c.      Follow what we did in Lab 9 to project the financial statements one year ahead (i.e., for 2013). Use cash as the plug. At this stage, just use the parameters from Lab 9 –don’t worry thatthese parameter values are wrong – you will calibrate these later. To emphasize, the share price you get from Part 1 will probably not be close to the actual share price but that is ok – you will fix this in parts 2-5.
   4. d.     Once you are satisfied that the one-year projection has worked, project the statements for years 2-5 ahead (i.e., 2014 – 2017).
   5. e.     Calcluate the projected free cashflows(again following Lab 9).
   6. f.      Calculate the Woolworths share price (again, at this stage just use the WACC and long-run FCF growth rate from Lab 9 – you will determine appropriate values for these later). Note: you will need to find out the number of Woolworths’ shares on issue.
   7. g.     Make sure your spreadsheet model changes the share price when you change the parameters. So, for example, if you change the sales growth rate or the WACC, you should get a new share price.

• 2.     Approximate SpreadsheetParameters(5 marks)

• Current Assets / Sales
• Current Liabilities / Sales
• Net Fixed Assets / Sales
• Cost Of Goods Sold / Sales

In the lab 9 solution spreadsheet, these ratios are in cells B4 to B7. Determine appropriate forecasts of these ratios for Woolworths by plotting / examining their historical values and researching company releases.

1. There are other parameters that are not a ratio to sales:

• Depreciation
• Interest rate paid on debt
• Interest rate received on cash
• Tax rate
• Dividend payout ratio

In the lab 9 solution spreadsheet, these ratios are in cells B8 to B12. Determine appropriate values of these ratios for Woolworths by examining their historical values or using common sense approximations. Note, approximations are reasonable here – so, for instance, even if Woolworths doesn’t use a constant rate of depreciation, it is probably ok to assume one in your model.

• 3.     Sales Growth Rate Forecast(5 marks)

The Sales growth forecast is the key parameter in the sales-driven spreadsheet valuation model (cell B3 in the Lab 9 Solution). You can use any information or modelling technique to generate your forecast, but should consider:

• The historical sales growth (which you can see in a plot of the revenues in the income statements over the last decade).
• Woolworths’ own forecast of its future sales.
• Any announcements from Woolworth’s concerning its operations.
• Market commentary on Woolworths and its competitors.

To value Woolworths from the free cashflows, you need to estimate the weighted average cost of capital (WACC) and the long-run free cashflow growth rate (see Benninga handout sections 3.4 and 3.5).

1. You can estimate the cost of equity using either the dividend growth model (assuming you can estimate the dividend growth rate g accurately from the historical dividend payouts) or the CAPM (assuming a constant-beta model fits well).
2. Use Woolworths credit rating (A- S&P/ A3 – Moodys) to determine its cost of debt from yields to maturity in the corporate bond market.
3. Read Benninga section 3.4 on the long-run free cashflow growth rate. You should use your best estimate / guessof the long-run sales growth of Woolworths for the long-run free cashflow growth rate. This is the sales growth rate we forecast will apply from 2018 onwards. Clearly this is difficult to know with any accuracy, although we might suppose that it will be equal to the historical industry sales growth rate. Put in your best guess here, and you will look at the sensitivity of the share price to your choice in part 5.

Now use your estimates of the WACC and the long-run FCF growth rate, along with your free cashflows for 2013 to 2017 from part 3, to calculate the Woolworths share price. Make sure you display this clearly on your report for the marker to see.

• 5.     Sensitivity Analysis and Market Calibration(5 marks)

• 6.     Scenario Analysis(5 marks)

Either:

1. a.     Develop best-and worst-case scenarios for Woolworths’sales growth based on a qualitative analysis of one or more of the following factors:

• Industry (e.g., market share) factors,
• Macro factors (e.g., GDP growth),
• Company-specific factors

For each scenario, list the values of the key spreadsheet variables (sales growth, WACC, etc.) that you selected to generate the share price. Justify your choices.

1. Develop best- and worst-case scenarios for Woolworths’ sales growth using a regression model for Woolworths’ sales. The explanatory variables could include GDP growth, Retail Sales, Consumption, etc. After collecting the data and fitting a satisfactory model, decide on which values of your explanatory variables correspond to the two scenarios (e.g., a recession would have negative GDP growth and low consumption). Use the sales growth forecasts from both scenarios to generate share prices from your spreadsheet model.

Assignment Solution:

Sensitivity Analysis

Sensitivity Analysis is the concept introduced in accounting for analysis of impact of change of a specific variable on the parameters like Net Present Value, which are important to estimate the impact on the business of the company and to study the effect of variation on these parameters.

Sensitivity Analysis can be helpful in two ways. Firstly it will help in identifying the parameters that have the major impact on the performance of the important ratios and the values and thus assist in controlling such factors or eliminate the conditions leading to increased impact (Kotas, 1986). Secondly it also helps in creating various cases which will cover most if not all and thus gives the estimate whether the business will be successful or not. Thus the outcome of sensitivity analysis is that it gives the estimate of the effect of the individual factors and thus makes it little comfortable in the sense that the firms can estimate that which individual factor may be impacting the performance.

In the model that has been prepared the sensitivity analysis has been performed firstly in single dimension and then in two dimensional matrix. The single dimension shows that the share price is sensitive most to the cost of goods sold as seen from the graph as the slope of the line is maximum.

The current assets and the current liability have the very little impact on the share price. Thus as long as the share price is concerned the current assets and current liabilities may not be of much concern as it is having little impact and thus there won’t be much deviation with their changes.

The two dimensional sensitivity analysis has been performed between Long Run FCF Growth and the Sales Growth.  The grid has been prepared wherein the share price deviations have been considered with the change in the sales and the Long Run Free Cash Flow. This analysis shows that Free Cash Flow and the Sales both have the same impact but it can be said that the free cash flow has more impact than the sales as the percentage change in share price is more in case of change in cash flow than the sales growth.

Scenario Analysis

Scenario analysis is similar to the sensitivity analysis however the difference being that in case of scenario analysis, a practical situation is supposed to be generated wherein the group of factors are collectively impacting the net present value and other such financial ratios unlike sensitivity analysis where in the impact of a particular factor is analyzed (Hull, 2009). This is to say that scenario analysis may be more practical approach wherein a situation is supposed to be there where all the factors collectively contribute to the performance. In sensitivity analysis it is supposed that the factors are independent of other factors. This may not be true and thus scenario analysis seems to be more practical approach.

The scenario analysis includes various factors after making suitable assumption and thus the effect is estimated on the net present value or similar factors. In the present problem the scenarios have been generated taking into consideration WOW Sales, GDP, Retail Sales and the consumption. Based on the calculations it has been observed that in the worst scenario there is 1.4% GDP growth, 2.54% retail sales growth and 3.53% Consumption growth and the best scenario has the 3.8% GDP growth, 8.35% retail sales growth and 9.18% consumption growth.

Based on these scenarios and the regression model that has been developed the sales growth have been estimated. Taking into consideration these values it has been observed that in the worst case the share price will be $ 22.99 and in the best case the share value will be $ 31.26. Thus this has shown the range within which the share price value may be there. However the disadvantage of the scenario analysis is that the number of cases that may have to developed is a lot more as the cases of various combinations have to considered and thus will lead to deeper analysis as compared to the sensitivity analysis.

References:

Hull J.C. (2009). Risk Management and Financial Institutions (2nd Edition). Prentice Hall

Kotas R. (1986).Management Accounting for Hotels and Restaurants. Routledge

LI46

But you can order it from our service and receive complete high-quality custom paper.  Our service offers Accounting  essay sample that was written by professional writer. If you like one, you have an opportunity to buy a similar paper. Any of the academic papers will be written from scratch, according to all customers’ specifications, expectations and highest standards.”