Looking for help with this problem. Will go more indepth upon discussion.

Suggested Steps for Successful Completion of Project: Step 1: Gather the most recent 61 monthly stock prices for the S&P500 and your company from Yahoo! Finance ( http:llnanceyahoo.com) Note: see the Lesson 10 assignment if you need a refresher on how to do this. Note: While it is perfectly ne to get historical data from different sources, for the purpose of this assignment please use Yahoo! Finance only. Step 2: Calculate the Expected Return on the Market using the above data. Note: see footnote [4] in the Full Case for more detail on this. Step 3: Go to the following website and get the latest appropriate 5-year Daily Treasury Yield Curve Rate h s://www.treas . ov/resourcecenter/datacha-center/interestrates/Pa es/TextViewas x?data7 'eld Note: see footnote [8] in the Full Case for more detail on this. Step 4: Get the Beta for ELP from Yahoo! Finance (this is the comparable Beta) Note: While it is perfectly ne to get beta from different sources, for the purpose of this assignment please use Yahoo! Finance only. Step 5: Unlever the Beta for ELP from Yahoo! Finance and use this as the beta corresponding to the unlevered beta of PSUWC Hint: Use the Latest Available (9/16) Total Liabilities (D) and Total Stockholder Equity (E) info from Yahoo! Finance ("Financials" tab & Quarterly Balance Sheet Data) Hint: The Hamada Equation should be usedto complete Steps 5 and 6. Also, assume that the tax rate for ELP and PSUWC is the same. Step 6: Re-lever the Beta calculated in Step 5 for the corrseponding D and E numbers for PSUWC (or/and in sheet "Rd with DtoE") Note: Use this beta (and the Rm, Rfvalues gathered before) to calculate the Cost of Equity (Re). Step 7: Find the appropriate cost of debt (Rd) using the data given in sheet "Rd with DtoE" (the use ofVLOOKUP will help automate the process) Hint: You should calculate the new D/E ratio before starting this step. Step 8: Calculate the WACC for the rm for the chosen DIE split. Hint: You should calculate the new Wd and We numbers before starting this step. Step 9: Complete the Cash Flow portion of the worksheet using the WACC calculated above and the given information. (including the NPV Calculation 8:. Acceptance Decision) IMPORTANT: All cash inows need to be POSITIVE and all cash outows need to be NEGAHVE. Step 10: Create the above results to create the Data Table in the area shown and create the plots requested. (ONLY FOR CASE A) Hint: Go through the howto videos available on Canvas Step 11: Complete theWorksheet "Answer Sheet" as needed. (see instructions on this sheet) Hint: Explain/Clarify any assumptions or methods used. Z Note: Use this sheet to convey any comments to the instructor. F, , 7, 1 V , , ,, A, _; 7 4 ,_ , , \ _ V ~ , , . i , '3' H g -- 100% o 1 tion of the D/E ratio 7 Times New Roman 12 ' " General * mama A Aa- E ii Q n... G) 75/25 DIE Spa: 15/75 0/15st Crew NPVPmlefor Varying Debt FinalDeclslml: Await/Raj" .1 which D/E my : Currency ; _(DE Orrrancy [0] 1 tion of the D/E ratie I. Given the fnllowing data on proposed capital budgeting project. V , Parameters Economlc llle nfproject In yarn. 6 Price of New Equipment 3 32,000,00000 Change in NWC Fixed Com Variable Costa Salv- value (New E "t al '11! Rate 26.0% % Generation 30000 kw per hour 74.0% Fiut Year Capacity famr 63% Capacity Factor Gruwth Rate 19% Sale Prlne 3 0.138 per kwhm1r Cult GIBondr I 1,085.00 Nut: CHI] C20 and C21 Include the Initial (May'l) cull MI. mmmsmck s [4.00 Cdnmbtnmghlmmopuaungnahm Call: "6-141 cont-In theurmlnal cull dun. Spreadsheet for determining Cull Flows Tlnrellne: Year 0 1 2 3 4 6 ' 1]. Net Investment Outlay = Initial CF: Price 0 (32.000.000.00) Change in NWC 11!. Cash Flow: from Operation: Revenue Generation Capacityfacmr 63% Electricity Generation 165564000 Revmuer $2,847,832.00 I I Costa Variable Costs Fixed Costs Depreciation Loan Interest Earnings Before Taxes Taxes Net Income Depreciaan Net operating CFs ' IV. Terminal Cash Flaw: Salvage Value Tax on Salvage Value Return of NWC Disposal Fee mu Flnal Cash 17an my] CaahFlnws Normal View Select destlnatlnn and press ENTER or choose Paste m -mamJym cam lameD "db._ = H In! F54 ' v A E C D E Ll J. = Colt MBondl 3 1,085.00 Note Cd]: C10 ll' C21 Include the IIHII (May'l) "I MI. Coltofstock I 14.00 mnmlmmwmm Cdk ISO-Ml until- the ternllnl ell! m Spreadsheet for determining Cull Flows Tlmellne: Yen 0 1 2 5 6 1]. Net Investment Outlny = lnltlnl CF: Price 3 (32,000,000.00) Change in NWC III. Cull Flows from Operation Revenue Generation Capacityfactor 63% Electricity Generation 165564000 Revenues $22,847,832") a Com 1 Variable Com Fixed Costs Depreciation Loan Interest Earnings Before Taxes Thxes Net Income Depreciation Net operating CFs lV. Terminal Cull Flaws Salvage anue '[hx on Salvage Value Return ofNWC Disposal Fee V. Fin-l Cull Flow Cash Flows Present Value ofCFs NPV of Project Accept This Project |:|El (on in own merits)? Recommend Management Accept Which Cne(s)? m ColnnaDlhmgll Spreadllleet for determining Cash Flow: mmczomcu lnchdnhhlaumday'ueanm canal-141 continuum-aluminum "shape-Mum Timeline: 1]. Net Investment Outlay Initial Ch 1 2 Price Change in NWC lll. Cull Flows from Operations Revenue Generation Electricity Genmtwn Revmues 3 Com Variable Com Fixed Costs mum LoanInterest Earnings Before Tam Thxes Net Income Depreciation Net opemting CF: Salvage Value x on Svage Value Return ofNWC II 1 030 =CaseA!C30 V ,,,, , , 10",, ,,, , , , ,,,., , W ,, ,, , 7,, 7 "7.7, ,7,,. Eamingx Befm'e Taxes Thus Net Income mm: N01 upswing CFs IV. Terminal Cull Flaws Salvage Value Tax on Salvage Valli: Return of NWC Disposal Fee V. Fin-l Cull Flnw Cash Flaws Present Value of CFs V of Project Accept This Project El (on its own maria)? |:| A J icalNl'V Prole 0% 2% 4% 6% 8% CF PV(CF)_ 1'V(CF1 PV(CF)_ [men PV(CF) (265,000.00) 3 (265,000.00) $ (265,000.00) S (265,000.00) (265,000.00) S (265,000.00) m CIICI C W i -@, "it WEEMM PSUWC's existing plant has excess capacity, in a rlly depreciated building, to install and run the new equipment. Due to relatively rapid advances in the technology, the project was expeomd to be discontinued in six years. The proposed project was capable ofpmvr'ding 30000 kW [1] perhourpower. Typically, PSUWC ran its plants 24 hours a day, 7 days aweekat an average of 63 % Capacity factor [2] , which is what the project would start with. However, his engineers had assured him that the implementation of the new technology would enable the- to increase their capacity factor by 19 % a year till they ' reached a 100% capacity factor. (This meant that the capacity factor for year 2, CFZ, would be = CF1*(1+growth_rate), till 100% was reached and then would stay at 100%). Atotal investment of s 32,000,000.00 USD for new equipment was required The equipment had xed maintenance contracts of S 3,800,000.00 per year with a salvage value of S 6,000,000.00 and variable costs were 47 % of revenues. The new equipment would be depreciated to zero rising straight line depreciation. The new project required an increase in working capital of S 6,000,000.00 and S 900,000.00 of this increase would be offset with amounts payable. PSUWC would be able to sell all the electricity it generated at the rate of 3 0.138 per kilowatt hour in the market they served. The corporate tax rate was 40 % and PSUWC currently has 1,000,000 shares of stock outstanding at a current price of 3 14.00. The company also has 30,000 bonds outstanding, with a current price of 3 1,085.00 'nre bonds pay interest semi- annually at acoupon rate of6.00 %. Thebonds have a parvalue of$l,000 andwill mature in 20 years. E3155 rgh'te'cbrpinyms'mcih'm' ' "ding it sweater; traded """ fie'fdxfuiei-e is n6 pu-blicly ava-ilal-rle En'cialT' information. However, management believes that given the industry they are in the most reasonable comparable publicly traded company is Companhia Pamaense de Energia - COPEL (NYSE Ticker Symbol EL?) [3] . In addition, management believes the 5&1) 500 is a reasonable proxy for the market portfolio. Therefore, the cost of equity is calculated using the beta omELPandthemarketriskpremiumbasedonthe S&P500annual expecmdrateol'return [4]. Tomknewthatbecauseofthesizeoftheproposedprojea, hehadtotake into accountthechangeincapitalstmcturethe newpmject wouldcausehis rm. To this end, he had a choice betweenraising the new capital needed eitherusing 26 % I74 % split between issuing bands/equity or a 74 % /26 % split between issuing bonds/equity . The bonds would have to be retiredattheendoftheproject'slife [6]. TomknewthstthecostofdebtwoulddependonthenewD/Eratiothatthe rm would have based on his decision to raise capital. Tom loobd at the worksheet titled Rd with DtcE, realizing that the cost of debt increased with an increasing DIE ratio [7] . Additionally, the state government had promised to raise the debt for PSUWC via the issuance of bonds, with the caveat that upon termination of the project PSUWC would have to pay a Nuclear Waste Disposal Fee, equivalent to the amount of money raised via the issuance of debt. Tonr nmded to calculate the rate at which he would have to discount the project to calculate the Net presert Value of the proposed project based on his decision of raising capital and the current capital market environmau. This discount rate, the WACC, would obviously inuence the NPV and could a'ect the decision of whether or not to accept the project. Thankfully, he had all the information nwded to calculate this and hence the NPV. Tom needed to clearly show all his calculations and sources for all parameter estimates used in the calculation of the WACC. Gathering all the available inform W i -@. "it WEEMWM Even though the company has stock outstanding it is not pub y traded Therefore, there is no publicly available nancial information However, management believes that given the industry they are in the most reasonable comparable publicly traded company is Companhia Paranaense de Energia - COPEL (NYSE Ticker Symbol ELF) [3] . In addition, management believes the S&P 500 is a reasonable proxy for the market portfolio. Thaefore, the cost of equity is calculated using the beta 'omELPandthemarketriskpremiumbasedonthe SELPSOOannual expmdrateofretm'n [4]. Tom knew that because of the size of the proposed project, he had to take into account the change in capital structure the new project would cause his rm. To this end, he had a choice between raising the new capital needed either using 26 % I74 % split between issuing bonds/equity or a 74 % /26 % split between issuing bonds/equity . The bonds would have to he retiredattheendoftheproject'slife [6]. Tomlcnewthatthe costofdebtwoulddependontheuewD/Eratinthatthe rm would have based on his decision to raise capital. Tom looked at the worksheet titled Rd with DtcE, realizing that the cost of debt increased with an increasing D/E ratio [7] . Additionally, the state government had promised to raise the debt for PSUWC via the issuance of bonds, with the caveat that upon termination of the project PSUWC would have to pay a Nuclear Waste Disposal Fee, equivalent to the amount of money raised via the issuance of debt. Tom traded to calculate the rate at which he would have to discount the project to calculate the Net presem Value of the proposed project based on his decision of raising capital and the current capital market environmmt. This discount rate, the WACC, would obviously inuence the NPV and could affect the decision of whether or not to accept the project. 'Thanld'ully, he had all the information needed to calculate this and hence the NPV. Tom needed to clearly show all his calculations and sources for all parameter estimates used in the calculation of the WACC. Gathering allthe avaahleinfomaomTomgotalarge cupofextrastrongco'eeandsatdowntoworkonthe developmmt of his Capiual Budgeting project model. His correct [commendation to the board was critical to the future growth ofre rml Minimum requirements for the Project 1. Calculate the WACC for the company. . 2. Create a paral income statement incmnental cash ows from this project in the Blank Template worksheet. 3. Enter formulas to calculate the NPV by nding the PV ofthe cash ows over the next six years. (You can either use the EXCEL formula PVO or use mathematical formula for PV of a lump sum.) 4. Set up the EXCELworksheet so that you are able to change the parameters in 134-1312, E3-E8,& Gll-GlZ. These are highlighted in Yellow on the Blank template. The other cells which need input 'orn you are highlighted in Light Blue on both worksheets. Rmrthe two cases: CaseAand CaseB. The DIE split for raising new capital are already lled in in the . worksheets. The numbers in the worksheet CaseB, which are identical to the numbers in worksheet CaseA will populate automaticallywhenyoulliniuformationintheworksheetCaseA-ThesearehighlighwdinOrange.Thenurnba'sin CaseB which DO NOT populate nwd to be calculated. Note that it is important to use the VIDOKUP function for calculating the cost of debt for automatic updating of the excel sheet. 5. Cells 354:64 List the Debt Fraction for New Capital varying om 0% to 100%. Using the Data Table Concept, calculate the WACC and NPV for each value. 6. Using the data generated using Data Tables, plot the N'PV & WACC as a function of Debt Fraction. Make sure to plot the rmmmmaa W i -@, a mammals: . A Tom nwded to calculate the rate at which he would have to discount the project to calcuhrte the Net present Value of the proposed project based on his decision of raising capital and the current capital market environment. This discount rate, the WAOC, would obviously inuence the NPV and could affect the decision of whether or not to accept the project Thankfully, he had all the information needed to calculate this and hence the NPV. Tom needed to clearly show all his ' calculations and sources for all parameter estimates used in the calculation of the WACC. Gathering all the available information, Tom got a large cup ofextm strong coffee and sat downto work on the development of his Capital Budgeting project model. His correct recommendation to the board was critical to the future growth ofte rm! Minimum requirements for the Project. 1. Calculate the WACC for the company. 2. Create a partial income statement incremental cash ows from this project in the Blank Template worksheet 3. Enterformulasto calculatetheNPbendingthePVofthe cashowsoverthenext sixyests. (Youcaneitherusethe EXCEL formuhr PVO or use mathematical formula for PV of a lump sum.) 4. Set up the EXCELworksheet so that you are able to change the parameters in 134312, E3-E8,& GllGlZ. These are highlighted in Yellow on the Blank template. The other cells which need input 'om you are highlighted in Light Blue on bodiworsheetsllrmthetwocases: CaseAandCaseB.TheD/Esplitformisingnewcapitalarealreadylledininthe worksheets. The numbers in the worksheet CaseB, which are identical to the numbers in worksheet CaseA will populate automaticallywhenyoullmmfomammthewarksheetCaseA-ThesemehighghmdinOrangeJhenumbasin CaseBwhichDONOTpopulateneedtobe calculadeotethatitisimporlnntto usetheV'lJOOKUPlnction for calculating the cost of debt for automatic updating of the excel sheet 5. Cells 1354:64 List the Debt Fraction for New Capital varying from 0% to 100%. Using the Data Table Concept, calculate ' the WACC and NPV for each value. 6. Using the data generated rising Data Tables, plot the NPV & WACC as a mction of Debt Fraction. Make sure to plot the NPV on the primary Y-ain's and the WACC on the mondary Y-axis. The Debt Fraction should he on the X-axis. Alternately, ' you can also plot N'PV & Debt Fraction as a mction ofWAOC. Ifyou do so, ensure that you plot the MN on the primary Y- axis and the Debt Fraction on the secondary Y-axis. The WACC should be on the Xaxis in this case. Comment on any peculiarities you may notice in this plot and discuss the possible reasons for these (whether this plot looks di'erentfromtheNPVPmlesywhawsmdiedbefmewheretheNPVisploedas amctionofthediscounttateetc). Be specic as to why there may be differences. For your reference, a typical NPV prole is plotwd below cells A69-G78 using some random cash ows. Cells A69-G78 contains the data and calculations used to generate the plot shown. NOTE: There is a plethora of information available on the Internet regarding creating plots in excel (Google how to plot a graph in excel) and creating plots with secondary axis (Google - how to plot a graph in excel with two y axis). 7. Make a clear recommendation whether the company should accept or reject the project for each case scarario. 8. Ifyou have any discussion/explanation for any assumptions you have made or nwd to provide more discussion, you may do so on the worksheet titled Answer Sheet 9.Turninyourprojectinthedropboic 1 [4] Tom would calculate the Expected Return on the Markm using 5 years of past monthly data Therefore, he would collect 61 months ofprieedataforthesdcl'andcaleulatelereturns forthepastSOmonths. Hewouldienmultiplythisby 12 to esmatetheanmmlemectedrate. Tmranembemdthntifthe expmdrateofreturnfbrmemnrketwastoolowoohigh, or negative, he would have to use a forward looking rate of an historical average ofabout 9.5%, as the calculmed value for the current 5-year period may not be a representation of the future. Tom would eonside a E(an) between 13-12% acceptable. [5] This meant that, for example, ifhe needed to raise $34,000,000 & ifhe used a 75/25% split between issuing bonds/equity, he would raise 75% of the needed capital of $34,000,000 by issuing bonds and the rest through issuing equity. Costs of issuing new bonds and equity are ignored for the purposes of this project [6] Thisrneantthatthecapitalraisedviaissuingdebtwouldhavetoberetmnedtotheinvestms attheendnfthepmjects life. [7] For example, a DA! ratio between 1.0 to 1.5 meant that the cost of debt would be 5.3% in the current climate. [8] Tom remembered that when he considered the Riskfree rate (R1) in his calendations, he should consider the risk-ee rate with a maturity similar to that of his project. He decided to use the 5-year rate available from the Treasury website for his calmdaons. tion of ihe D/E raiie ll a n E F H K L M N r R 1 v w x Y Grilling Rubric for Final Project in FIN 301 BmIniAk 1m WACC Cllcuhnn o 05 1 2 m 6nd: Poulbh Pol-nix WWII MW No Cduamu or lament Cdcuhtion, Dorm CHAN-an Mm: (7.1mm Cdnulnon using 2 Bxplnuuns BridExplauon, using my: Capital 1mm Cupiul 8mm Rumble animal: Sin-mm with Wall wiih Fxplmnrion Cm alum No delm'nns or 111mm Cdculnipn Car-w: Cdmdlon Conan Ccnm band 2 Blplmlmu (manned using Band (ulnakmd manna Duh) on Edwin: 13m): am wim Duh), WWW with Wan Explmlon Ensemble mm Beta Nucnnhomar Beth-laden Bethludwniufumlon BehClkIlhbdwiiDe 3 Expllnlonl information hm Yahoo! from Yahoo! Fin-m Ind lnu'ing and binning mhumo mammomhm wmmfazupiul subtequm odcuhoul (21min) mm (3 wink) (1 paint) mom erlut No Cllmlnim or Emmi: n Mnkn Emmi: fnt Mum Rzlum Emma hr Mum 2 Mum Emlmlun: Rmnixmmwubln ismlulmhsodm Ram-nixmmmbkl hnotbuaionhismrinll mom-1mm buednnhimrimlmnkzt mm Fem path-mm perfmmne Ink-flu am No Calculations or Brim-m um bud on Biplnulion afRisk e: m: 1 Bxphuo'on. um Milly yield: mod on current 5-yul 1' but numb]: m bond BIT-bi." yidd upland/m Conormunym.) Nucaimhomat Eimtemblldm ElmntbuedanCAPM llnluul CAPMo ex lamina with kiwi/an WAOC Na Caamm Wwannwi: mlcmdlmwim Wmmm 2 Expllnlunl albumen-rind mimmmieddmugh Expllmonufnll my: from um: fmm lbw: assumptions with pox-mm mumptiom win: no animation expiration Toni Ponihle mm mm WACC cllmhlion portion or project 13 E 'u A'vHHd'v' I m om mummy ' , , J arulng nubrlc Normal View Select destlnatlnn and press ENTER er choose Paste . MM!" 1 tion of 'he D/E rati mummm Ixilpmperlyhbhdwim mph-rim Hwy-aim dicuk m Mimi wim Ipelug Ind gum-w awn Fin-J Deciaiun is collect win: mum expllutinn ofbulis ofrbeciliun Ind .11 ummpom VeryOrpniZBiNe-nmd Ensyfonadamfollaw wimnllinpnum slumptiomclelrly identifldinoneuu mu Pnlllble mm m m 1mm 11me Automation 0 l 3 II the Slim setup [or No Alma-lion 060% mun-ted This! PoIIlble Point ' organ" Student seared 707. Tot-.1 Number of Students Percentage of sum-m Pu- W | { Please Note: Students should get full credit for PV and NPV calculations and decsions if they are consistent wiIh inputs om prior steps. Inuthetwords,ifthestudentincmrecystatesaWACCnf1%m30%causiugallNPVcalc|ationstubeinmlhcycansgetlcreditfortheNPVcalcauonanddecision' ' ' HighlighmdamhycowabowisnpmedhAACSBnpthlmsedauhkcheckthmmmWigwam Nomal View Select destlmtlgn and press ENTER ar chwse Paste GM'M WWI m-m-m "II I! 9 i 5 Topic ExpllnlnnlDluuuion Note: Him'ngAlt-Euwrin mlmblcsyaumwemulple liuzsl'u duesamenlliurul. Like Thlslins- which was mdbyhhtingAkEm na' tthaladin thslineabnve vannlylim You cmalsocapypaalewma Waddacmutandjmlu'tAlt-Bmwllmyau nzedtagataamliuin mtitl'snatfvnnmd [WW Wehlm nfDelu anquuity, Wd .1 We Cast ofDebt Calculation Bdafvr u Finn W Market Raum Risk-Fin Ram WA CC WA CC Calculation Initial Cash Flaws L" Answer 3mm