can using this can you help me to solve the excel file. only answer two iterations I can figure out the rest
Overview Problem Definition You are an analyst at an investment fund. The fuad is censidecing an investment in Wralmart. s pat of the decision free cash Dows. Your jub is to determine the cost of eapeital for Walmart. Main Question Determine the WacC for Walmari. Ensure that you estimate the market value of debe by valuing the individual debt instrumests. Once you bave a baseline estimate, you diant to figure out the variability of that estimate. Criven the stondard deviabons in the Monte C:arlo lopuss sectian, and asuming these variahles all follow normal of the variables has the greates contribution to the WACC? Lse at lenst 10,000 simulativas. Notes - Be ennelul that lbe relurns ane daily. You will naxd to divide ibe risk free tale by 252 to gel a daily rate. A fter you got an estimate from CAPM, you will need to multiply it by 252 . - You can feel free mo modify any of the inpat files mo make them easier ta bod. Bur keep in mind thar if ywu did this on the jub, and you needed to keep updating tos medel, you weild have to do this vvery time. If you cleansd it up with oode thea it would be automated. At the end I will stow my madel whict automates the clesmup preses - If there is a range of coupons given, wou can asrame the midpaint of the mange is the caupon. as the market price of the bond. Inputs Download the following fles from the course sirs under Project 1 : - SPSIII Pricesx xis - WMT Dobt Details.xls - WMT Pritis: 514 - KAIT Incece Statememt.xlsx - WMT Bularee Shesa s1tx In these files you will fund the historical stock prices for Walmart, the historical index values for the S\&P S00, the individual deht instruments for Walmart, iand the incorne staternent and balance sheet for Walmart. In addition to the information in the spreadsheets, also consider that Walmart currently has a coupon bond trading in the market (details below). The risk-free rate and L.BOR rates are also given in the inputs table. Also reference Wrimart's stock information in the inputs table. Baseline Model Inputs Monte Carlo Inputs Submission \& Grading Submission Work aff of the "Iroject 3 Template.xlsx" and "Iroject 3 Template.ipynb" files on the course site under l'roject 3. It is up to you whether you submit a pure Python, pure Excel, or combination model, For whichever tools you usc, you should use the templates, so those submitting a combination model will submit both a Jupyter notebook and an Excel workbook, hoth hased off the templastex. With the combination model, your ultimble answer should be in the Jupyter notebook. Regardless of the tools you use, running the model should run the full Monte Carlo simulation based off the model ind simulation inputs, It is not acceptable to hard-code the simulations. If you modify any of the data files, then submit them along with your model. Ensure that the names of the files are the same as the original names. If you do not submit a data file, then I will assume your model works aff the original and copy in the original file. Your bonus score will be based off of which files you subnit. If you do not submit any data files (you're using the original for all), then you will get the full bonus points, assuming the informialion from the data files was correctly incorporated into the model. Solutions Selected solutions with the baseline inputs: - WACC: 5.19\% - MV Debt; S827 billion - Cost ot Equity: 5.96% - Pre-Tax Cost of Debt: 2.34\% Overview Problem Definition You are an analyst at an investment fund. The fuad is censidecing an investment in Wralmart. s pat of the decision free cash Dows. Your jub is to determine the cost of eapeital for Walmart. Main Question Determine the WacC for Walmari. Ensure that you estimate the market value of debe by valuing the individual debt instrumests. Once you bave a baseline estimate, you diant to figure out the variability of that estimate. Criven the stondard deviabons in the Monte C:arlo lopuss sectian, and asuming these variahles all follow normal of the variables has the greates contribution to the WACC? Lse at lenst 10,000 simulativas. Notes - Be ennelul that lbe relurns ane daily. You will naxd to divide ibe risk free tale by 252 to gel a daily rate. A fter you got an estimate from CAPM, you will need to multiply it by 252 . - You can feel free mo modify any of the inpat files mo make them easier ta bod. Bur keep in mind thar if ywu did this on the jub, and you needed to keep updating tos medel, you weild have to do this vvery time. If you cleansd it up with oode thea it would be automated. At the end I will stow my madel whict automates the clesmup preses - If there is a range of coupons given, wou can asrame the midpaint of the mange is the caupon. as the market price of the bond. Inputs Download the following fles from the course sirs under Project 1 : - SPSIII Pricesx xis - WMT Dobt Details.xls - WMT Pritis: 514 - KAIT Incece Statememt.xlsx - WMT Bularee Shesa s1tx In these files you will fund the historical stock prices for Walmart, the historical index values for the S\&P S00, the individual deht instruments for Walmart, iand the incorne staternent and balance sheet for Walmart. In addition to the information in the spreadsheets, also consider that Walmart currently has a coupon bond trading in the market (details below). The risk-free rate and L.BOR rates are also given in the inputs table. Also reference Wrimart's stock information in the inputs table. Baseline Model Inputs Monte Carlo Inputs Submission \& Grading Submission Work aff of the "Iroject 3 Template.xlsx" and "Iroject 3 Template.ipynb" files on the course site under l'roject 3. It is up to you whether you submit a pure Python, pure Excel, or combination model, For whichever tools you usc, you should use the templates, so those submitting a combination model will submit both a Jupyter notebook and an Excel workbook, hoth hased off the templastex. With the combination model, your ultimble answer should be in the Jupyter notebook. Regardless of the tools you use, running the model should run the full Monte Carlo simulation based off the model ind simulation inputs, It is not acceptable to hard-code the simulations. If you modify any of the data files, then submit them along with your model. Ensure that the names of the files are the same as the original names. If you do not submit a data file, then I will assume your model works aff the original and copy in the original file. Your bonus score will be based off of which files you subnit. If you do not submit any data files (you're using the original for all), then you will get the full bonus points, assuming the informialion from the data files was correctly incorporated into the model. Solutions Selected solutions with the baseline inputs: - WACC: 5.19\% - MV Debt; S827 billion - Cost ot Equity: 5.96% - Pre-Tax Cost of Debt: 2.34\%
