### Create a StudySoup account

#### Be part of our community, it's free to join!

Already have a StudySoup account? Login here

# Financial Models FIN 4453

University of Central Florida

GPA 3.79

### View Full Document

## 86

## 0

## Popular in Course

## Popular in Finance

This 12 page Class Notes was uploaded by Haylee Spencer on Thursday October 22, 2015. The Class Notes belongs to FIN 4453 at University of Central Florida taught by Vladimir Gatchev in Fall. Since its upload, it has received 86 views. For similar materials see /class/227496/fin-4453-university-of-central-florida in Finance at University of Central Florida.

## Reviews for Financial Models

### What is Karma?

#### Karma is the currency of StudySoup.

#### You can buy or earn more Karma at anytime and redeem it for class notes, study guides, flashcards, and more!

Date Created: 10/22/15

umversiwm COLLEGE I OF I BUSINESS ADMINISTRATION Florida FIN4453FALL 2009 REV NOVEMBER 5 2009 VLADIMIR A GATCHEV Intel Corp vs Advanced Micro Devices Inc Continued Before deciding which firm to invest in Mel s manager wanted to know the approximate fair value of the equity of both Intel and AMD I CALCULATING HISTORIC OPERATING FREE CASH FLOWS The first step is to calculate the Operating Free Cash Flows for the two finns given the historic financial data You will need to look at the previous case we discussed in class for the nancial statements of the two rms The performed analyses are for illustration purposes and are not intended to serve as promoting any investment in the two rms discussed Intel Corp vs Advanced Micro Devices Inc Continued Intel Corp Tax Rate Free Cash Flow Free Cash Flow as a Percent Free Cash Flows 35 1467 Growth in Forecast AdvancedMicro Devices Inc AMD Tax Rate Free Cash Flow Free Cash Flow as a Percent Free Cash Flows Growth in Forecast 35 2032 2008 15300 35 97390 1677 FIN 4453FALL2009 35 288 2007 00 35 1742 35 1784 35 5309 Intel Corp vs Advanced Micro Devices Inc Continued FIN4453FALL2009 II PREDICTING FUTURE OPERATING FREE CASH FLOWS You will need to rst predict the future growth in sales for both rms Then you will need to predict the Operating Free Cash Flows as a percent of sales Intel Corp 0 Intel Prediction 0f0p FCF as 0f2008 Growth 01 F 4 of 011 FCF 2009 2 576909 2010 3 594216 2011 4 617985 2012 5 648884 2013 6 687817 Constant growth thereafter 6 AdvancedMiero Devices Inc 0 AMD Prediction 0f0p FCF as 0f2008 Growth 01 F 4 of 011 FCF 2009 NA 53751 2010 0 1 59 83 201 1 0 2 1 19 67 2012 2 3 1 8309 2013 4 4 24891 Constant growth thereafter 6 In this case we predicted each of the components of Operating Free Cash Flows only for 2009 For years beyond 2009 we predict only the growth rate of Operating Free Cash Flows Financial analysts would usually predict each component of the Operating Free Cash Flows even for years beyond 2009 For example change in working capital and capital expenditure will be predicted separately You can still use the percent of sales approach however for the individual components of Operating Free Cash Flows Once each component of future Operating Free Cash Flows is predicted you can use the formula on page 1 to predict the Operating Free Cash Flows Intel Corp vs Advanced Micro Devices Inc Continued FIN4453FALL2009 III CALCULATING THE REQUIRED RATES OF RETURN A Using the CAPM see the end of the notes for the Fama French model I will use CAPM to calculate the Required Rate of Return for the equity of the two rms For the debt I will use Intel Corp Required Mte of Return C Nominal Riskfree Rate 500 Expected Msk Premium for the Market 475 Longterm Debt SampP Credit Rating A Credit Spread 065 Required Rate of Return 565 T ax Rate 3500 Taxadjusted Required Rate of Return Common Equity Market Beta 118 Required Rate of Return I 1063I AdvancedMiero Devices Inc Required Mte of Return F 39 39 Nominal Riskfree Rate 500 Expected Msk Premium for the Market 475 Longterm Debt SampP Credit Rating CCC Credit Spread 1275 Required Rate of Return 1775 T ax Rate 3500 Taxadjusted Required Rate of Return 1154 Common Equity Market Beta 208 Required Rate of Return I 148604 You should note in the Excel le that accompanies this lecture that I have used the SLOPE function to calculate the Beta coef cients This is more ef cient as I only need the Betas and not the whole regression output The cost of debt is based on the credit rating of the rm and the credit spreads of Reuters see Appendix Intel Corp vs Advanced Micro Devices Inc Continued FIN4453FALL2009 IV CALCULATING WACC The next step is to calculate the Weighted Average Cost of Capital for the two rms You will need to get the market capitalization of equity for the two rms For the debt I will use the book value of the long terrn debt both for Intel and AMD Intel Corp Source of Funds Value Weight RRR Longterm Liabilities 380900 349 367 Common Equity 10548160 9651 1063 Total Capital 10929060 10000 WACC 1039 IShare price I 1904I Shares quot millions I 554000I AdvancedMiero Devices Inc Source of Fun ds Valu e Weight RRR Longterm Liabilities 536200 6148 1154 Common Equity 335896 3852 1486 Total Capital 872096 10000 WACC I 1282I IShare price I 484I IShares quot millions I 694 00I Notice that we assume a certain share price market value for the equity of the rms in order to calculate the WACC while at the same time we are actually trying to nd the share price market value ofthe rm This problem can be solved by creating a cell in Excel that is equal to share price used in WACC 7 fundamental value per share you will calculate on next page2 Then you need to use the Goal Seek Function in Excel and set this new cell to 0 by changing the cell that contains the actual price per share Intel Corp vs Advanced Micro Devices Inc Continued FIN4453FALL2009 V FINDING THE VALUE OF EQUITY AND THE PRICE PER SHARE In the last step I calculate the fair value of the rm as a whole ThenI subtract the value of the debt to get the fair value that is left to equity If you want to calculate the fair price per share you will need to divide by the total number of shares outstanding Intel Corp Present Value of Uneven Growth Cash Flows 2326463 Present Value of Constant Growth Cash Flows after 2010 10143881 Present Value of the Firm 12470344 Debt 380900 Residual Equity 12089444 Shares Outstanding 554000 Value per Share 39 39 Current market Price AdvancedMiero Devices Inc Present Value of Uneven Growth Cash Flows Present Value of Constant Growth Cash Flows after 2010 Present Value of the Firm Debt Residual Equity Shares Outstanding Value per Share Current market Price Question Based on these calculations what would you do Now suppose that the growth in sales for both rms after 2013 will be 4 or 8 What should the fair values per share be in this case You can use the Scenario Manager to compare the two scenarios growth 4 or 8 Scenario Summai Grow i at 4 Gi ow1h at 8 Changing Cells Intelsalesgrthhafteryear5 6 4 8 A1VJDsalesgrowthafteryear5 6 4 8 Result Cells fundamentalpriceintel 2182 1585 3781 39 priceamd 481 555 347 Intel ctnp vs Advanced Minn Devices Inc Comm FlNMs H ALLZHW VI MORE ADVANCED EXCEL TOOLS A Using the FamszrEnch Harm mm lal p1nstnetnentntn One way te estttnate the mudel ts nstng the regessmn tee1 tn Excel Heweyet1et39s new use thts eppettnntty te see hew yen ean use the LmEsT INDEX TstT and COUNT fnnetdenstn Bicel TH LINEST manor Hare are seme efthe tetnms data 3 H Emu Mm A 57120051 401 5 E32099 503 E 700009 mm 7 6 12009 302 8 712009 02 a e 307 313 i74 sume S JVAWE39 551 1013 915 000 251 415m 29 The LINEST funcnun ts stmtlarte the SLOPE and INTERCEPT tnedens hnttt alluws yen te nd eeefEetents yen ynu haye te se1eet seyeta1 empty eeus befure yen type tn the fennnta F rvnt t lnt cept Then yen ean type tn LINESTHZ H lJZ Mm11 EetAMD tn the Excel le yen ean se1eet X36 te ABKB 5x3 eeus fur the 4 s1epe eeef nents plus the tnteteept Then yen eantypetn LINESTIZ 15112 M611TRUE The syntax ts LINESTameyntys1meyntyseenst stats The Y ts the dependent yanatde tn thts Intel The faetets censt ts 1 tfyen want an tnteeept and 0 tfyen de net mm Stats ts 1 tfyen mm mete stansnes standad enets Resenate and 0 tfyen tnst want the eeefEeents Yuu ynu haye te ptess out Shnl Emaquot when yen ae dene because thts ts at enen Intel Corp vs Advanced N cro Devices Inc r Cm nuzd FIN4453rFALIr2009 In this example I am also requesting the standard errors ofthe coe icients and also the Rsquare of the regression statFl Here is the result for Intel andforAMD Catl ciem gt Sunusra Emr gt R gt Important in The LlNEST function gives you the coe icients in reverse The intercept comes last The rst x variable in column I is the Excess Return o ampP 500 however the coe icient for that variable comes right before the intercept The last in column M x variable is the Up minus Down factor but the coef cient for this variable shows up rst n t c t u u 4139 1 Risktree Rue Required Rate omenirn Nth n illstillL 39 39 39 39 quot quot 39 quot ofthe market You will also have to make assumptions abou size factor smallminusBig the value vs growth factor HighminusLow and the momentum own t the risk premium associated with the factor Upm inusD Intel Corp vs Advanced Micro Devices Inc Continued FIN4453FALL2009 THE INDEX FUNCTION INDEXarrayrow7numcolumninum The INDEX function allows you to pick a speci c number from an array of numbers The syntax is Let s pick only the intercept for Intel INDEXLINESTH2H61JZM611115 In this case the INDEX function requests the element that is in row 1 column 5 of the LINEST table which is the intercept of the model If you want to pick the coef cient on the Excess Return of SampP500 then you can use INDEXLINESTH2H61JZM611114 In this case the INDEX function picks the element that is in row 1 column 4 which is the desired slope coef cient THE TDIST FUNCTION TDISTxdeg7freedomtails The TDIST function gives you the pvalue of a tstat Tstats can be calculated as the coef cient divided by the standard error of the coef cient The tstat has to be a positive number so I will use the ABS function to take the absolute value of the tstat For example for Intel the pvalue of the coef cient for the Excess Return of SampP500 is TDISTABSR45COUNTH2H61COUNTP41P452 The COUNT function simply counts the number of observations in a selected range In this case the degrees of freedom are the number of historic observations we have for Intel 60 monthly returns minus the number of parameters we have to estimate 5 coef cients Since we want a twotailed test you type in 2 for tails We will use this function again in Chapter 12 Intel Corp Vs Advanced Micro Devices Inc 7 Cnnlz39nutd FIN44537FALL72009 USING PIVOT TABLES Pivot Tables are a very powerful tool in Excel I will give you just one quick example ofhow you can use Pivot Tables Here i nan nf the year retum ta heet PivntTahle Data 5 1 A 1 1 E 1 F 1 G 1 7 pm Year Month BeessRuumnflmcl maessmmnmmn BeessRcmmcfSKPSW SmallmmusBig HighmmusLow Up minusDovm 27 November 009 my u 423 ism 772 105 133 453 3 thnberl 009 2009 no am 190m 1 dorm 4 SeptembnrlJO my 9 mm mm 57 s 009 2009 x 503 mm 547 57 y m 2 7 mm 73 334 77 IunelJW 20w 6 450m 424 aquot May 120179 2009 i 021 Jszv 470 9 Am 12009 my t mm mm 416m 10 MamblZW my 3 mm mm 451 11 31y 0179 200 1 37 Mona 405 12 1 uary 2009 low 1 422m 119 om 1g December 1200 200x 2 5 1 37 um 19 Novmbu32WX zoos mm mm mm 15 o b 2m 2m m 447m 4359 29 157 54mm mx 201m 9 Juana 465m 432 17 Augustl200 2m 5 336 490m 5 U Go to the Insert Tab at the top of Excel and select MWquot all from A1 to 161 Then LllLK This is what you get r m in 11 Der Data Items Here Sum 311227 m39m n E w 1 M Intel Corp vs Advanced N cro Devices Inc 7 Cnnh39nued FIN445FALL72009 From the top menu on the right select all returns That means that you want to create a summary of all the returns From the same menu drag Year and drop it in the Row Labels Drag Month and drop it in the Report Filter Box The bottom right menu would look like Drag elds between areas below J Reparmher Culumn Labels lanes v Month v 2i 5 fEx quot 2 Values m Labels year D Defer Lavaufuvdate Click on the rst Sum ofExcess select Value Field Settings and change to Average Do that for all returns Now you have a table that shows you the average return for each factor and also for Intel and AMD by Year By clicking on the little arrow next to Year you can select to display only returns for a given year The Grand Total is the average returns for all 5 years A B C D E F G 1 Month A11 v 2 3 Data Average of Avezage of Average of Average of Average of Excess Rerum Beess Retum Bees Rem mall minus go minus Average ofUp 4 Year x i of swam 39 Low minus Down 5 20m 1 90 1 95 n 6 1005 430 063 7 2005 069 o 84 s 100 006 330 9 2003 A 0 030 10 2009 129 o 34 11 Grand Tom 0 42 029 You can view the average returns only for certain months Just select from the Month menu the months you need and the table will automatically do that foryou As an exercise create a new Pivot Table in which Year is the lter and Month is the Row Label Intel Corp vs Advanced Micro Devices Inc Continued FIN4453FALL2009 Appendix This is the table with the credit spreads that I use for the Required Rate of Return for the Debt 100 1 percentage You can nd the credit rating of a rm if the rm has any from httpww 39 com That link is also at the end of the class web site Enter the Ticker on the right and press Enter On the left menu click on Stock Reports 9 HTML or PDF If HTML go to the Table with Key Stock Statistics and the SampP credit rating if any is there In the PDF report the SampP Credit Rating is at the top right corner of the page

### BOOM! Enjoy Your Free Notes!

We've added these Notes to your profile, click here to view them now.

### You're already Subscribed!

Looks like you've already subscribed to StudySoup, you won't need to purchase another subscription to get this material. To access this material simply click 'View Full Document'

## Why people love StudySoup

#### "There's no way I would have passed my Organic Chemistry class this semester without the notes and study guides I got from StudySoup."

#### "Selling my MCAT study guides and notes has been a great source of side revenue while I'm in school. Some months I'm making over $500! Plus, it makes me happy knowing that I'm helping future med students with their MCAT."

#### "Knowing I can count on the Elite Notetaker in my class allows me to focus on what the professor is saying instead of just scribbling notes the whole time and falling behind."

#### "Their 'Elite Notetakers' are making over $1,200/month in sales by creating high quality content that helps their classmates in a time of need."

### Refund Policy

#### STUDYSOUP CANCELLATION POLICY

All subscriptions to StudySoup are paid in full at the time of subscribing. To change your credit card information or to cancel your subscription, go to "Edit Settings". All credit card information will be available there. If you should decide to cancel your subscription, it will continue to be valid until the next payment period, as all payments for the current period were made in advance. For special circumstances, please email support@studysoup.com

#### STUDYSOUP REFUND POLICY

StudySoup has more than 1 million course-specific study resources to help students study smarter. If you’re having trouble finding what you’re looking for, our customer support team can help you find what you need! Feel free to contact them here: support@studysoup.com

Recurring Subscriptions: If you have canceled your recurring subscription on the day of renewal and have not downloaded any documents, you may request a refund by submitting an email to support@studysoup.com

Satisfaction Guarantee: If you’re not satisfied with your subscription, you can contact us for further help. Contact must be made within 3 business days of your subscription purchase and your refund request will be subject for review.

Please Note: Refunds can never be provided more than 30 days after the initial purchase date regardless of your activity on the site.