Work on my excel project
Budget: ₹12,500 – ₹37,500 INR
Requirements about the project:
The expectations of the project include not only correct calculation and analysis in Excel, but also detailed explanation in Word about the calculation results. For example, in part 1 financial analysis part, you are expected to explain the current situations of the company. Are they getting better or worse over time? What are the reasons?
At the beginning of the project, please briefly introduce the company you are working on, including its industry, main products, business advantages and disadvantages, etc.
The due date of this project is by the end of May 2nd.
The rubric of the project is provided at the end of this file.
How to get the data:
You could get the data from SEC’s Edgar System at https://www.sec.gov/, or some other websites.
If you go to SEC website, please click “FILINGS” tab ?”Company Filing Search”. To retrieve the data for your company, enter the name or ticker symbol in a search box. Then, find 10-k annual report and click “filing” button. Click the Interactive Data button next to the most recent 10-k, and then select Financial Statements in the menu on the left side.
You may work on the two statements: Consolidated Statement of Operations (the income statement) and Consolidated Statements of Financial Position (balance sheet).
You need at least three years latest data.
Required analysis:
The specific analysis includes four parts:
Part 1: Financial Analysis (Chapter 3)
Please analyze the financial situations of the companies. You may calculate and analyze all the financial ratios we discussed in class.
Please note that you cannot just list the calculation results. You are required to write a short analysis report to show the trend of the company’s financial situations.
Part 2: Financial Statement Forecasting (Chapter 5)
Please create the pro-forma balance sheet and income statement for next year. You may make reasonable assumptions if necessary. Please clearly write down your assumptions in your report.
If we assume all the discretionary financing sources are from long-term debt, please try to decide what amount of additional long-term debt may be needed in the next period.
Part 3: Common Stock Valuations (Chapter 9)
Using the Yahoo! Finance Web site (https://finance.yahoo.com) get the current price and five-year dividend history for your company. To gather this data, enter the ticker symbol or company name in the search box at the top of the page, and select your company from the list. Record the current price from this page. Now, click on the Historical Data link. To get a table of previous dividends, select Dividends Only in the Show list, set the Time Period to five years prior to today’s date, and click the Apply button. Click the Download Data link to download a file with this data.
You may have the choice of either saving the file or opening it directly in Excel. It is easier to let it open in Excel. Otherwise, save the .csv (comma separated variables) file, and then open it with Excel. It shouldn’t need any further processing. You now have the dividends in a worksheet.
a. Because the company pays dividends periodically (e.g. quarterly), calculate the percentage change in the dividends for each period. Now, calculate the compound quarterly growth rate of the dividends using the geomean function.
b. Now, annualize the quarterly dividend growth rate.
c. Calculate the intrinsic value of the stock using an 10% required rate of return (you may adjust the required rate of return if necessary) and then calculated annual growth rate. Use the sum of the most recent four dividends as the current annual dividend as time 0.
d. Now assume that your company’s dividend growth rate will remain the same for the next five years, and then fall to 75% of its current rate. What is the value of the stock using the two-stage dividend discount model?
e. Now assume that your company’s dividend growth rate will remain the same for the next five years, then fall to 90% of its curre
The expectations of the project include not only correct calculation and analysis in Excel, but also detailed explanation in Word about the calculation results. For example, in part 1 financial analysis part, you are expected to explain the current situations of the company. Are they getting better or worse over time? What are the reasons?
At the beginning of the project, please briefly introduce the company you are working on, including its industry, main products, business advantages and disadvantages, etc.
The due date of this project is by the end of May 2nd.
The rubric of the project is provided at the end of this file.
How to get the data:
You could get the data from SEC’s Edgar System at https://www.sec.gov/, or some other websites.
If you go to SEC website, please click “FILINGS” tab ?”Company Filing Search”. To retrieve the data for your company, enter the name or ticker symbol in a search box. Then, find 10-k annual report and click “filing” button. Click the Interactive Data button next to the most recent 10-k, and then select Financial Statements in the menu on the left side.
You may work on the two statements: Consolidated Statement of Operations (the income statement) and Consolidated Statements of Financial Position (balance sheet).
You need at least three years latest data.
Required analysis:
The specific analysis includes four parts:
Part 1: Financial Analysis (Chapter 3)
Please analyze the financial situations of the companies. You may calculate and analyze all the financial ratios we discussed in class.
Please note that you cannot just list the calculation results. You are required to write a short analysis report to show the trend of the company’s financial situations.
Part 2: Financial Statement Forecasting (Chapter 5)
Please create the pro-forma balance sheet and income statement for next year. You may make reasonable assumptions if necessary. Please clearly write down your assumptions in your report.
If we assume all the discretionary financing sources are from long-term debt, please try to decide what amount of additional long-term debt may be needed in the next period.
Part 3: Common Stock Valuations (Chapter 9)
Using the Yahoo! Finance Web site (https://finance.yahoo.com) get the current price and five-year dividend history for your company. To gather this data, enter the ticker symbol or company name in the search box at the top of the page, and select your company from the list. Record the current price from this page. Now, click on the Historical Data link. To get a table of previous dividends, select Dividends Only in the Show list, set the Time Period to five years prior to today’s date, and click the Apply button. Click the Download Data link to download a file with this data.
You may have the choice of either saving the file or opening it directly in Excel. It is easier to let it open in Excel. Otherwise, save the .csv (comma separated variables) file, and then open it with Excel. It shouldn’t need any further processing. You now have the dividends in a worksheet.
a. Because the company pays dividends periodically (e.g. quarterly), calculate the percentage change in the dividends for each period. Now, calculate the compound quarterly growth rate of the dividends using the geomean function.
b. Now, annualize the quarterly dividend growth rate.
c. Calculate the intrinsic value of the stock using an 10% required rate of return (you may adjust the required rate of return if necessary) and then calculated annual growth rate. Use the sum of the most recent four dividends as the current annual dividend as time 0.
d. Now assume that your company’s dividend growth rate will remain the same for the next five years, and then fall to 75% of its current rate. What is the value of the stock using the two-stage dividend discount model?
e. Now assume that your company’s dividend growth rate will remain the same for the next five years, then fall to 90% of its curre