Login Register


Corporate Finance


The objectives of this assignment are to “Understand how to approach and solve financial problems”; “Explain and model financial planning and control process using a spreadsheet application and financial calculator”; “Prepare, analyze and judge financial statements and pro-forma financial statements for projecting funds needed”; “Explain, interpret and justify decisions based on decision rules”; and “Identify, estimate, analyze and assess cash flows”. You will prepare a set of pro forma financial statements to project funds needed by a firm using an iterative approach. You will use an Excel workbook that has a lot of work already completed. There are, of course, many ways to estimate funds needed with canned programs, but you need to go through the process to understand fully the underlying assumptions and their effects. You will also estimate these funds using a simple formula. Finally, you will explain the differences in the two methods as well as the assumptions you had to make along the way. This particular set of pro forma statements is fairly simple and straightforward to estimate. You will use this set of skills to do the same thing for a real company in your next course in finance, financial statement analysis. You will also demonstrate your Excel skills in this assignment. Workbook Save to your hard drive and then open the workbook that is posted along with this assignment. Read the Information sheet and begin building the pro forma statements within the workbook that you saved. The first thing you should do is read the Information worksheet. There is a lot of data in this worksheet, so I set up a table in which you can summarize the data. Then, you can reference this summary table throughout the rest of the workbook for your formulas. The summary table is in cells A8 through C24 and it has the title Assumptions & Requirements. Next, I would go to the EFR Equation worksheet and figure that out. The EFR equation is in your book. Then you go to the combined income statement, cash flow statement and balance sheet worksheet (IS CF BS) to figure out what you need to do there. See pages 778-783 for an example in the book. They leave out the detail, but the idea is the same as for this problem. There are two other worksheets that you need to complete: Ratio Analysis and Common Size IS BS. You are the financial manager making the assumptions and the forecast. Use the pass method (see the worksheet IS BS CF) in Excel using formulas and functions to estimate the pro forma financial statements along with ratios and common size statements. Or, you can use the GOALSEEK function in Excel (Tools/Goalseek). Your answers must converge, i.e., your balance sheet must balance. I need to see that you understand how the decisions you make as a financial manager (the basis for which are given mostly as assumptions in the Information worksheet) affect the pro formas, ratios and common size statements. You can certainly check your work with a calculator, but you will get no credit for just plugging in numbers where you could have used a formula and/or cell reference. I also need to see that you know how to use Excel. Grading That workbook should contain all of your work, including your explanations. Do not insert a Microsoft Word document into Excel for your explanations. Doing this makes it difficult to grade, so just start typing your answers in Excel. You can format your answers to make it easy to read and grade, however, so please do so.