Lab 5 - DCF (Filled)

xlsx

School

Oregon State University, Corvallis *

*We aren’t endorsed by this school

Course

123

Subject

Finance

Date

Jan 9, 2024

Type

xlsx

Pages

12

Uploaded by AdmiralJay493

Report
342 Term Project -- Lab 5: DCF Name: Company Stock Ticker: Prompt: Instructions: Do Not Delete Any Columns, Rows, or Cells in this Workbook. It will me Part A: Practice On the tab "DCF - Practice" -The CFO has provided you with some Pro Forma (abbreviated) fina -The red flags in each cell tell you what should be in the cell or give -Blue font always means you inputted a value yourself and will be g -The cells with orange boxes will be our "driver cells". The assumpti Part B: You Try On the tab "DCF - You Try" -Repeat the analysis from the practice DCF for your own company -You will need to get data for your own company from the same sou -Driver cells can either be the default assumption or you can hardco -You can mess around with assumptions until you get a price that yo Tips / FAQ: Make sure you have your signs correct when subtracting things like chang The CFO has tasked you with performing a fundamental valuation of the CFO will have a better idea of whether the stock is overvalued or underva significantly undervalued, they might be interested in buying back some sell some issue new shares at a premium to fund future projects. To do so, you (DCF). This information will go on slide 19 and 20 of your presentation. You must work on this lab by yourself, though you can ask questions to not submit anyone else's workbook as your own work.
Applied Materials Inc. AMAT ess up the grading! ancial statements and internal estimates of growth. Fill in the table where necessary with that informati you some guidance. More detailed instructions are on the right side of the page, including the order y graded for accuracy, black font is a calculation and will be graded for accuracy and using a formula to ref tions we make in these cells (calculations or input values) will drive forecasts for future periods. These w urces as the 3M example ode or calculate your own assumptions ou believe makes sense (does not have to be equal to today's price). Your assumptions must be "reason ge in NWC and in the FCF calculation company as a whole. With this valuation, the alued relative to internal estimates. If the stock is shares at a discount. If it is overvalued, they might will need to perform a discounted cash flow analysis classmates, the TA, or the professor. You may
tion. you should fill things in. ference other cells. See the key on the right hand side of the page. will all be formulas and must contain references to other cells. nable" and your must provide a rationale for them in your term project/
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
59632
86612
Discounted Cash Flow Valuation Discount (n) 1.00 2.00 3.00 4.00 5.00 Capitalization (FY0) % Steps: Net Debt 11,725 19% 1.) Fill all input cells using sample company data Past---> Future---> Total Equity 51,575 81% 2.) Use input data to calculate growth, margin, ratios, and other missing numbers in FY-4 FY-3 FY-2 FY-1 FY0 FY1 FY2 FY3 FY4 Total Capitalization 63,300 3.) Use numbers from FY-4 to FY to calculate driver cells in FY2 to FY4 Sales 32,136 32,184 35,355 34,229 31,737 32,859 33,061 33,264 33,468 4.) Calculate remaining calculation cells in FY2 to FY4 using driver cells YoY 0.1% 9.9% -3.2% -7.3% 3.5% 0.6% 0.6% 0.6% WACC 5.) Fill in Capitalization table, WACC, Terminal Growth Risk free rate Ignore in this Lab, for later Lab 6.) Discount future cash flows back to present using PV=FV/(1+r)^t EBITDA 8,417 8,755 9,284 8,947 7,908 8,679 8,658 8,711 8,764 Market risk premium Ignore in this Lab, for later Lab 7.) Add together the discounted projected cash flows and the discounted terminal v Margin 26.2% 27.2% 26.3% 26.1% 24.9% 26.4% 26.2% 26.2% 26.2% Beta Ignore in this Lab, for later Lab 8.) Subtract off net debt to see how much future cash is going to shareholders, then Cost of Equity 7.0% D&A 1,593 1,911 1,915 1,831 1,848 2,013 1,852 1,863 1,875 Wtd. Cost of equity 5.7% Key: % of sales 5.0% 5.9% 5.4% 5.3% 5.8% 6.1% 5.6% 5.6% 5.6% 5,921 <--- Input cells (hard code only, graded for accuracy) Cost of debt 2.46% 33,061 <--- Calculation cells (formulas only, graded for accuracy and usi EBIT 6,824 6,844 7,369 7,116 6,060 6,666 6,806 6,848 6,890 Wtd. Cost of debt 0.5% 0.6% <--- Driver cells (formulas, graded for accuracy, must contain a re Margin 21.2% 21.3% 20.8% 20.8% 19.1% 20.3% 20.6% 20.6% 20.6% 5,473 <--- Free cash flow output (formulas, graded for accuracy, must c WACC (Discount Rate) 6.2% Capex 1,699 1,501 1,603 1,749 1,695 1,737 1,674 1,685 1,695 A real DCF would not have the boxes filled in with color. So once you are done, you c Capex / D&A 1.07 0.79 0.84 0.96 0.92 0.86 0.90 0.90 0.90 Terminal Growth Terminal Growth Rate 0.5% Chg. Working Capital 3,285 -666 -1,203 121 187 34 34 34 Working capital 3,749 7,034 6,368 5,165 5,286 5,473 5,507 5,540 5,574 NPV / Implied Share Price Calculation % of sales 11.7% 21.9% 18.0% 15.1% 16.7% 16.7% 16.7% 16.7% 16.7% NPV through FY0-FY4 23,829 NPV terminal value 78,402 Pre-tax Profit (EBT) 5,712 6,711 7,204 6,392 5,445 6,410 6,300 6,339 6,378 Enterprise Value 102,231 Margin 17.8% 20.9% 20.4% 18.7% 17.2% 19.5% 19.1% 19.1% 19.1% Tax 1,142 1,327 1,283 615 800 1,282 1,070 1,076 1,083 Implied Equity Value 90,506 Tax Rate 20.0% 19.8% 17.8% 9.6% 14.7% 20.0% 17.0% 17.0% 17.0% Diluted Shares Outstanding 549 Net income 4,570 5,384 5,921 5,777 4,645 5,128 5,230 5,263 5,295 Equity value per share $164.78 Free Cash Flow 5,576 2,642 7,064 7,786 5,292 5,473 5,880 5,916 5,952 Discounted FCF 4,985 4,856 4,915 4,658 4,415 Sample Company Data The CFO has provided some historical data and projections below to help you create your DCF.
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
Source: https://www.marketscreener.com/quote/stock/3M-COMPANY-4836/financials/ Source: https://finance.yahoo.com/quote/MMM/balance-sheet?p=MMM
n FY-4 to FY1 value (sum of all cash flows from FY5+ as a growing perpetuity) n divide by sharecount to find the present value of all future projected CFs per share ing a formula to reference other cells) eference to other cells) contain a reference to other cells) can change the fill color to transparent. Keep the blue text color to show it’s a hardcoded value.
86612
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
Discounted Cash Flow Valuation Discount (n) 1.00 2.00 3.00 4.00 5.00 Capitalization (FY0) % Steps: Net Debt 56,500 45% 1.) Get the company data, see instructions below in the "Company Data" section Past---> Future---> Total Equity 69,540 55% 2.) Fill all input cells using sample company data FY-4 FY-3 FY-2 FY-1 FY0 FY1 FY2 FY3 FY4 Total Capitalization 126,040 3.) Use input data to calculate growth, margin, ratios, and other missing numbers in Sales 30,000 30,000 30,000 30,000 30,000 30,000 30,000 30,000 30,000 4.) Use numbers from FY-4 to FY to calculate driver cells in FY2 to FY4 YoY 0.0% 0.0% 0.0% 0.0% 0.0% 0.0% 0.0% 0.0% WACC 5.) Calculate remaining calculation cells in FY2 to FY4 using driver cells Risk free rate Ignore in this Lab, for later Lab 5.) Fill in Capitalization table, WACC, Terminal Growth EBITDA 30,000 30,000 30,000 30,000 30,000 30,000 30,000 30,000 30,000 Market risk premium Ignore in this Lab, for later Lab 6.) Discount future cash flows back to present using PV=FV/(1+r)^t Margin 100.0% 100.0% 100.0% 100.0% 100.0% 100.0% 100.0% 100.0% 100.0% Beta Ignore in this Lab, for later Lab 7.) Add together the discounted projected cash flows and the discounted terminal v Cost of Equity 7.0% 8.) Subtract off net debt to see how much future cash is going to shareholders, then D&A 0 0 0 0 0 0 0 0 0 Wtd. Cost of equity 3.9% % of sales 0.0% 0.0% 0.0% 0.0% 0.0% 0.0% 0.0% 0.0% 0.0% Cost of debt 2.46% Key: EBIT 30,000 30,000 30,000 30,000 30,000 30,000 30,000 30,000 30,000 Wtd. Cost of debt 1.1% 5,921 <--- Input cells (hard code only, graded for completion) Margin 100.0% 100.0% 100.0% 100.0% 100.0% 100.0% 100.0% 100.0% 100.0% 33,061 <--- Calculation cells (formulas only, graded for completion and WACC (Discount Rate) 5.0% 0.6% <--- Driver cells (formulas or hard coded assumptions of your ow Capex 30,000 30,000 30,000 30,000 30,000 30,000 #DIV/0! #DIV/0! #DIV/0! 5,473 <--- Free cash flow output (formulas, graded for completion, mu Capex / D&A #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! Terminal Growth Terminal Growth Rate 0.5% Chg. Working Capital 0 0 0 0 0 0 0 0 Working capital 30,000 30,000 30,000 30,000 30,000 30,000 30,000 30,000 30,000 NPV / Implied Share Price Calculation % of sales 100.0% 100.0% 100.0% 100.0% 100.0% 100.0% 100.0% 100.0% 100.0% NPV through FY0-FY4 #DIV/0! NPV terminal value #DIV/0! Pre-tax Profit (EBT) 30,000 30,000 30,000 30,000 30,000 30,000 30,000 30,000 30,000 Enterprise Value #DIV/0! Margin 100.0% 100.0% 100.0% 100.0% 100.0% 100.0% 100.0% 100.0% 100.0% Tax 0 0 0 0 0 0 0 0 0 Implied Equity Value #DIV/0! Tax Rate 0.0% 0.0% 0.0% 0.0% 0.0% 0.0% 0.0% 0.0% 0.0% Diluted Shares Outstanding 843 Net income 30,000 30,000 30,000 30,000 30,000 30,000 30,000 30,000 30,000 Equity value per share #DIV/0! Free Cash Flow 0 0 0 0 0 0 #DIV/0! #DIV/0! #DIV/0! Discounted FCF 0 0 #DIV/0! #DIV/0! #DIV/0! Questions Question 1: How does the price compare to today's price for your company? It is different Question 2: How does the price compare to the price implied by your previous multiple method analysis? Not sure Company Data You will need to screenshot the pages from marketscreener.com (all financials except for working capital) and yahoo finance (working capital) and paste below The screenshots should look very similar to the ones on the practice tab, except your numbers and years will of course be different. Here are the links to the pages for 3M… find your own company page: Note: The grading is slightly different on the "You Try" tab Note: A real DCF would not have the boxes filled in with color. So once you are don https://www.marketscreener.com/quote/stock/3M-COMPANY-4836/financials/ https://finance.yahoo.com/quote/MMM/balance-sheet?p=MMM
n FY-4 to FY1 value (sum of all cash flows from FY5+ as a growing perpetuity) n divide by sharecount to find the present value of all future projected CFs per share using a formula to reference other cells) wn, graded for completion) ust contain a reference to other cells) ne, you can change the fill color to transparent. Keep the blue text color to show it’s a hardcoded value.
86612
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help