Safeway Vons

Here's my version of the spreadsheet with some explanations...

Only use the first 6 tabs of the file. On the first tab, this is where you
do your sensitivity analysis. You can change the discount rate and terminal
value. These are the only inputs we need for this assignment.

The next two tabs are just historical data for Safeway and Vons. I used the
Vons tab to see if there were any significant trends in balance sheet or
income statement items as a percentage of sales.

Sales growth forecasts are given in the case and calculated in the
Additional Calculations tab. After that, almost everything is driven off
the Common Size tab. Given past trends, we are supposed to forecast each
balance sheet and income statement item as a percentage of sales. I almost
always used the most recent year as the forecast. In some cases, the
forecast turned out to be a calculation because the actual number (found on
the DCF tab) was derived some other way. (e.g., depreciation as a
percentage of PPE).

Note that I did not calculate debt as a percentage of sales. That's because
we are supposed to figure out both long and short term debt in aggregate, so
I didn't try to break it back out into long and short term as a percentage
of sales. It doesn't really matter if the whole Common Size tab is filled
out.

Any additional calculations I needed (e.g., sales forecasts) I did in a
separate sheet creatively called Additional Calculations.

Using the given assumptions of 12% discount rate and 7 times EBITDA
multiple, I get a per share value of lower than what Von's stock price is at
the time of the case. But even using 10% and 8 times EBITDA, I still can't
get to the $58 that Safeway is offering. However, if you use the 9.9
multiple that Safeway says their proposal represents and 11% discount rate,
you get a share price of about $58.

We are also supposed to look at the ratio analysis at the bottom of the DCF
tab and make sure the ratios are in line with expectations. You can compare
them to the ratios given at the bottom of the Vons tab.

I get asset turns that are a little higher than historically to begin with
and then they keep going up, which isn't usually normal. Asset turns tend
to remain relatively constant. The reason why asset turns start out higher
is because total PPE as a percentage of sales has been going down. This
makes sense given the information in the case that Vons is closing and
remodeling lots of stores. Thus, making assets more productive. The reason
why asset turns are increasing over time is because goodwill as an asset is
steadily decreasing as it gets written off and no further acquisitions are
made. Thus assets grow slower than sales. (I checked this. If you have
goodwill grow as a percentage of sales, asset turns stay constant).

The only other thing is that leverage seems to be much lower than
historically. I think it's because debt as a percentage of total assets has
gone down significantly. Given that Vons is generating more cash than
needed to support both operations and investments, extra cash is being paid
out as a dividend. (See line 76 on DCF tab). Thus, it is likely that debt
as a percentage of assets will continue to decrease as it gets paid off over
time. In the model, however, I have kept debt as a percentage of assets
constant at the 1996 level. (See line 28 in Additional Calculations). If
you increase debt and therefore increase leverage, you can get the ROE back
up to 1996 levels. However, without changing the discount rate, changing
the debt has no effect on the DCF share price.

If you have any questions, make sure you "reply to all" so I get it at both
home and school.

- Vons Model.xls