Prepare Financial Forecast Using eVal Model

Prepare Financial Forecast Using eVal Model

This project is closely aligned with the Course Outcomes and Finance Program Objectives.  Completion of this project can be used as part of a portfolio to show potential employers the student is skilled at performing company valuations and financial statement analysis and can be included on the student’s resume.

Evaluation: The Assignment #2 is 20% of final course grade.

No more than 20% of the text of the project should be made up of quotes.

For this assignment, use the company that was previously assigned for you by your professor for all three class assignments.

Please note that if you missed the assignment #1 it is probably too late to submit the written report for the assignment #1. But you need to go to Content – Assignment #1, read the Project Description of the Assignment #1, download the Excel file with eVal model, and get the data for your company from the database. You have to complete the necessary steps described in the Assignment #1 to complete the model for the company assigned for you. Assignment #2 is a continuation of the Assignment #1.

The actual eVal Excel forecasting model must be submitted with the assignment.

Open eVal model and go to the “Forecasting Assumptions” worksheet. You will see 5 years of historical data on each of forecasting assumptions lines and 11 years of forecast data in shaded cells. The model calculated 5 years of historical data based on the company’s information you entered while completing the assignment #1. Your job is to fill in shaded cells of forecast assumptions.

Play with the model a bit to see how forecast changes.

YOUR SPECIFIC ASSIGNMENT IS:

1. The paper should begin with a short introduction (explains the purpose of the paper and provides an overview of the contents that follow) (5% of the project grade)
2. Explanation of Sales Growth Rates – Include an explanation about sales growth rate projections for the company for the next 11 years. Justify the terminal growth rate. (15 % of the project grade).

You analysis should be based on your findings Step 1 in the Assignment #1. Remember, the part of the assignment #1 was STEP 1: Collecting information and data about the company and projections of the company’s future financial performance. What do your forecasts indicate for the future sales growth rates? How will the industry forecast affect future sales growth rates? What will be the firm-specific influences on sales growth rates forecast?

Justify the terminal growth rate.  Note: it is difficult to justify terminal or long-term growth rates that exceed the long-term growth rate of the economy, e.g., about 3%; in the short-run, higher rates may be appropriate. It was the part of your assignment #1 (Step 2 of the assignment #1) to find the forecast of the U.S. economy growth rates (short-term and long-term). Please use your findings of the forecast of long-term growth rates of the U.S. economy to justify the terminal sales growth rates of the company. You should reference the source of the information about U.S. economy growth rates.

It is important to check that your terminal sales growth rate assumption is not greater than the cost of equity capital (See the number in cell B20 on “Model Summary” worksheet). If your terminal sales growth rate assumption is greater than the cost of equity capital, the model will not work, and you will get error messages.

3. Explanation of All Other Assumptions – (20% of the project grade).

Include an explanation of all other assumptions related to future operating performance including costs, margins, efficiency, capitalization, etc. that appears on the “Forecasting Assumptions” worksheet of eVal model for the next 11 years. Any changes in the assumptions should be explained and analyzed.

Note: We provided an Excel worksheet titled “Analysis of eVal Assumptions Forecast”. This worksheet is posted in Content – Assignment #2 and must be submitted with the assignment.

All assumptions used in the forecast need to be explained in the written analysis. An Excel worksheet “Analysis of eVal Assumptions Forecast” should be incorporated as a “table” in the written analysis.

4. Explanation and Interpretation of Projected Financial Statements – Explanation and interpretation of the projected financial statements and discounted free cash flows should include pro forma financial statements (income statements, balance sheets, and statements of cash flows). (15% of the project grade).
5. Your analysis should be based on your findings Step 1 in the Assignment #1 and projections of the company’s future financial performance. What do your forecasts indicate for the future financial statements and cash flows?
6. Please include the analysis of the Net Issuance of Common Stock (line 39 of Cash flow Analysis worksheet of the eVal model). What does it mean “Net Issuance of Common Stock” in Cash Flow Analysis? Why is it important? What does the positive or negative amount of Net Issuance of Common Stock mean?
7. What does it mean “plug” of the model? Why is it important?
8. Does your forecast correspond to the market conditions? Does management anticipate using excess cash for stock repurchases, if so? If not, what will be the source of financing of stock repurchase? Do you need to go back to your forecasting assumptions and change them to make the forecast more reliable? (Please do that and explain in your written report!)

5. Explanation and Interpretation of Overall Forecast Results and the projected ratio analysis (20% of the project grade)

Click on the Ratio Analysis and Cash flow Analysis tabs and see a complete ratios and cash flows implied with your assumptions. Do these forecasted ratios correspond to your finding and your views about the firm’s future? What do your forecasts imply for the future ratios and cash flows? How reasonable ratio and free cash flow analysis of the future financial statements are?

Please also include in your written report the analysis of the “Implied Return of Equity” that appears on the line 11 of the “Forecasting Assumptions” worksheet of the eVal model.

6. Reflection – the students should write a paragraph in their own words reflecting on specifically what they learned from the assignment and how they think they could apply what they learned in the workplace. (10% of the project grade)

Organization, Presentation, Spelling, punctuation, and grammar totally are 15% of the assignment grade.

Writing Instructions

The discussion portion of the analysis should be about 5-7 pages in length (more is OK!), double spaced, and should employ APA style and format for reference citations. Supporting data (e.g., figures, tables, etc.) and references should be submitted limited to four separate attachments in an appendix after the written portion of the paper.

The paper should begin with a short introduction (explains the purpose of the paper and provides an overview of the contents that follow) and then proceed to examine the four topics outlined in the previous section.

Written projects:

• Must be typed, double-spaced, in 12-point Times New Roman or Arial font, with one-inch margins
• Must have the title page in APA-7^th style
• Must have in-text citations in APA-7^th edition style
• Must have reference list in APA-7^th edition style. Please note that you must reference the data you are using for the project
• Must be prepared using word processing software (Microsoft Word preferred)