assignment Due in 12 hours

Project 3

Free Cash Flow Firm Valuation

Comment: Project overviews are meant to layout the minimum requirements for a given project and to explain the motivation for a given project. Meeting the minimum requirements does not guarantee an A or even a passing grade. These projects are very rich, with many possible extensions to the analysis, various interpretations and recommendations from the results, and alternative methods of analysis that can be applied or discussed relative to the primary method introduced in the project. The finance faculty wishes to give students a chance to differentiate themselves to potential employers in the interview process and have left “meet on the bone” for the students to explore.

In industry, project overviews are rarely, if ever, created. Your boss will simply say “do this” and expect you to formulate the required aspects of the project. In addition, detailed written reports, as are required for this class, are also rarely done in industry. However, employers want to know that potential employees are not only technically capable, but also can communicate their findings effectively in written form. Proof read your papers. DO NOT write sentences that you do not understand but think that the professor will. Use proper grammar, spelling, and effective organization of the material presented. Do not think “what does the professor want”, rather think “what would impress my boss” or “what would get the customer to choose my company”. Bosses and customers are busy. They hate too much information more than to little information. Be clear and to the point. Look for ways to table or plot critical information so that readers can understand the results at a glance. Good Luck.

Motivation: By necessity, the data used in our projects is publically available data. A company will have more detailed internal data to forecast sales and other financial parameters. However, financial analyst can only legally use publically available data to evaluate stock prices and firm values. There are multiple ways to value a firm. P/E ratios, free cash flow, dividend discount models, and other methods are applied to value a company for mergers, acquisitions, and for investment decisions. In this project we use the sales forecast to estimate the value of the stock price and high and low limits for the price. The project uses a simple Monte Carlo simulation to estimate the value of a firm. In forecasting free cash flow, there is uncertainty about the level of sales and uncertainty about the percent of sales that will be free cash flow. It is the joint distribution of sales and percent free cash flow that should be used in the valuation of the company. Although not part of the project, the uncertainty of the discount rate can also be incorporated into the financial model. This project introduces the concept and application of a sales forecast, integrated with a Monte Carlo simulation, to value a company.

There are two key Excel concepts introduced in this project. The first concept is that of Excel macros. Macros allow the user to execute a series of commands at the press of a button. We also introduce macro programming. There are many applications in finance and business where macros will be required. Our goal is to introduce the student to the macro process and programing language. The second Excel concept is Goal Seek. Goal Seek allow the Excel user to identify the exact input value needed to get an exact output value. The use of Goal Seek requires the correct setup of the spread sheet, but will allow the user to quickly answer many questions regarding many applications in business.

Basic requirements of the report:

1) A short description of the company assigned to the student is required. The description should be no longer that one page, double spaced, 11 point type, Garamond font. Give the reader key details of what the company does and how it performs, without boring them with details about the founding date, the address, and other needless information that can be quickly referenced but has no bearing on the sales forecast. You may copy this from the prior report, incorporating any feedback from the professor. Comment by Barriga Saenz, Miguel A: Talk about the company. That was my fault because I didn’t mention. But, you should ask me at reading the requirements. The Company name is “Casey's General Stores Inc”.

2) The Monte Carlo simulation must be based over 100 years and 500 lifetimes.

3) A printout of the macro program is require and should be the first item in the appendix. Comment by Barriga Saenz, Miguel A: The macro goes at the end. In the appendiz seccion, not in a section of excell macros.

4) Create a table of the leverage adjusted discount rate data. Comment by Barriga Saenz, Miguel A: Explain the table, make at least two or more sentence

5) Create a table of the stock price information based on the calculated discount rate. Comment by Barriga Saenz, Miguel A: I don’t see this table, also explain a little of this table

6) Create a table of the stock price information based on the Goal Seek discount rate. Comment by Barriga Saenz, Miguel A: You just create a the table but, you haven't explain it, niether a little sentence of it. Elaborate more

7) All tables and plots, except the working tables, must be integrated in the text and labeled as Table 1, Table 2, or Figure 1, Figure 2 ect.

8) Working tables should be labeled and included in the appendix as indicated in the videos.

Remember, you are building a portfolio of projects and work examples that can be used in the interview process, not just creating a report for a grade. While the projects will give you many marketable skills in Excel, the ability to clearly communicate your findings is another marketable skill that can only be demonstrated through the writing of your report.