Create Three Pivot Tables Financial Modeling Proj

By /

Create Three Pivot Tables Financial Modeling Proj

  • Use the table provided to create a pivot table and find the inputs needed for the stock valuation section
  • Stock Valuation
  • Stock Portfolio
  • Stock Growth
  • Questions
    • How did you estimate the growth rate for GE’s free cash flow(i.e. what criteria and method did you use)?
    • Does it appear that GE was appropriately valued at the beginning of 2018? Explain.
    • How did you estimate the growth rate for GE’s dividends (i.e. what criteria and method did you use)?
    • How did you select a stock to add to your portfolio (i.e. why did you think the stock would be beneficial and what did you do to determine if this was true)?How beneficial was the stock you selected?
    • Compare the risk and return of your stock to the portfolio you created.
  • Visual Basic
    • Include macros (with buttons) where appropriate to automatically update your model.
      • Make sure there are no pop-ups that show up after your macro is created.
    • Include at least one scroll bar or spin button.
  • Use the database provided to create three pivot tables showing information relevant to finding the firm’s free cash flow and WACC. Format the table and make sure the values make sense.
    • You might need to copy the pivot tables, paste them as text, and make a few additional edits to get the final table.
  • Find the firm’s free cash flow for 2016 and 2017.
  • Find the firm’s WACC (for the weights use book value for debt and market value for equity). Do not include preferred stock.

Note: This is the only part of the final project that does not require all of the numbers to be directly linked, but you must show all of your work.

  • Everything from this part of the project on should be directly linked and either automatically update when the inputs are changed or when a macro button is pressed.
  • For the WACC use the share price at the beginning of January. You will need to download the historical prices for the portfolio section.
  • Ideally, we would use market value for the firm’s debt, but it takes longer to find/calculate and is outside the scope of the project.

Note:

Notes

  • All the numbers except the actual share price are shown in thousands.
  • Refer to projects 3 and 7 for additional examples. The calculations will be exactly the same as they were for project 7, but you will need to have them in a database format. Suggested column headers.
  • Hint: The FCF growth rates should be randomly generated for each year and the remaining columns should just have equations.

Please strictly follow the instruction step by step and answer all question with around 2-5 sentences. I also upload the project 3 and 7 for you as additional example. Please make this project nice and beautiful. All works should in the excel.