Homework answers / question archive / About the Valuation Project On Nov 29 2019, it was reported that Warren Buffett offered to buy Tech Data (Ticker: TECD) for $140 per share

About the Valuation Project On Nov 29 2019, it was reported that Warren Buffett offered to buy Tech Data (Ticker: TECD) for $140 per share

Accounting

Share With

About the Valuation Project

On Nov 29 2019, it was reported that Warren Buffett offered to buy Tech Data (Ticker: TECD) for $140 per share. However, private-equity firm Apollo Global Management made a competing offer at $145 per share, revising their earlier offer of $130. Mr. Buffett bowed out of the bidding war. On Nov 27, Tech Data announced that it has accepted the offer by Apollo. For more detailed coverage of the event, see this article. (Links to an external site.)

In this project, you are asked to determine the value of Tech Data using a pro forma model. The basic steps are similar to what we discussed in the Pro-Forma Module (Ch 5 of the textbook). However, a real company's financial statements are bound to be more complicated than the prototype examples covered in class.

I made videos that demonstrate how to value the stock of Caterpillar. Please watch these videos before you start the
project. While doing so, it is important to keep in mind that all companies are different, so the videos are only meant to be viewed as a broad guideline as opposed to an exact blueprint. You will have to adapt to the specific situations of Tech Data.

Project Requirements
The project can be broken down into four main steps (The following instruction is also available as a Word document Instructions_Valuation.docxPreview the document).

1. Download, consolidate, and conduct initial analysis

First, download the financial statements of Tech Data for the most recent 6 fiscal years from SEC Edgar. (Note that you probably won’t need to download 6 files since each file covers multiple years.)   After you download the files, consolidate them into one dataset for each statement (income, balance sheets, cash flow). For example, you should combine 6 year of income statements into the same dataset and sort the years in ascending order.  The cash flow statement is not as important as the other two, so you only need to collect the items you actually need from it. You may also need to look at the spreadsheet related to PP&E in order to obtain information about depreciation and integrate this information into the balance sheet and income statement.
In this process, a challenge is that the name of the same item could change over time and some items might appear or disappear from one statement to another. When this happens, you will need to make sure that items are lined up correctly. (In the video, I used VLOOKUP to do this, but you can also do this manually.)  After downloading the statements, it is important to take time to study the statements carefully (At least do this for income statements and balance sheets.).  In particular, you need to understand the mathematical relationships between the items. For example, some numbers are sums of other numbers; some companies might express costs as negative values while others may express them as positive values. Please be sure that you have fully digested the financial statements before moving on to the next step.


2. Simplify and format the statements

Second,  simplify the actually statements into a format that is as close to the prototype as possible.  Be sure to calculate the items listed in the examples of Module 8.  Then calculate the main ratios based on the simplified financial statements.  The actual financial statements will contain several items that are outside the scope of our examples, therefore some discretion will be needed in handling these items. For example, you may choose to combine some relatively minor items into one; you may also calculate ratios for some of these items or keep them constant.  In building the pro forma model, you can use the historical ratios in ways you see fit.  For example, you may use the historical average or the most recent value. For current assets/sales and current liability/sales ratios, I suggest you use the most recent values.
(Note that in the second step, you should use references to access the data obtained from Step One. Do not just copy the numbers. Also, the next steps should also refer to the cells from the previous step.  This way, when you need to change one step, the rest of update automatically.)


3. Calculate the WACC

Third,  calculate the WACC for Tech Data.  Cost of equity can be calculated using CAPM formula. Please estimate the beta of Tech Data using 10 years of monthly returns. Do not copy the beta from financial websites.  In applying the CAPM formula, you may use 6% as the annual market risk premium (I.e. This is the difference between the expected market return and the risk-free rate). For the risk-free rate, you may use the 3-month treasury bill rate found on Federal Reserve Bank's web site. The skill set needed to finish this part was covered in Module 7.

4. Use your pro-forma projections and stock valuations to discuss the offers made by Buffet and Apollo

Finally, finish pro forma projections and stock valuation. The long-term growth rate of FCF can be estimated as the annual GDP growth rate of the U.S. or long-term government bond rates.  Discuss your opinion of the offers made by Buffett and Apollo.

The Excel workbook you submit should include the results from these steps on separate worksheets.