Hello, dear friend, you can consult us at any time if you have any questions, add WeChat: daixieit

ECON-430-01: Financial Economics II

Spring 2024

Midterm Exam I - Excel Section

Instructions

• Go to notebowl, and in the tab assignments look for “Exam I ”. Download the excel file “ECON-430 S24 Exam I.xlsx” and the Exam 1- Excel Section pdf file.

• To get full points, you must show all your work and justify ALL your answers. In the case of excel, it is necessary to work with references to well-identified cells from your own file. Using as an input any typed number in any excel formula may affect your grade.

• Be organized in the way you present your results; and put yourself in the shoes of the person who is going to read your file or exam.

• Remember that it is necessary to work with references to well-identified cells from your own file. Using as an input any typed number in any excel formula may affect your grade.

• The excel portion has a weight of 40% of the exam’s grade and it needs to be completed in 12 hours after 4:30 p.m. today (when it will become available on Canvas).

• Please save your excel file as: “your last name and the initial of your first name”-E1.xls. For example, a file under my name should be saved as “cadenasp-E1.xls”. Once you are finished, submit your file via notebowl. The process is the same as the one you have been following for the excel assignments.

• Make sure to save your work every 5-10 minutes to make sure you will not be in the uncomfortable position of losing what you have done. If you do lose your work, you will be held responsible and I will have no choice but to grade your exam based solely on what you submit.

Excel Section

1. You work for a financial consultant firm that is currently working on a project for the company “The Lord of the Games, Inc.”. Suppose we are at the end of 2021 and that you are given an incomplete excel file that will assess, based on assumptions from a particular strategic plan (let’s call it plan “A”), the value of the company for the next five (5) years. You have all the information in the excel file to complete the work. Here is what you are being asked to do:

(a) There are parts of the forecast that need to be completed. Based on the assumptions described in the file, complete the forecast.

(b) Make sure that in the balance sheet, total assets are equal to total liabilities plus equity using “Long-Term Debt” as the plug-in variable. As you complete the financial statements forecast, you may want to make sure to enable, in excel, iterative calculations (for Windows: Go to File, then Options, then Formulas. Check on “Enable Iterative calculation”. For Macs: Go to Excel, then hit Preferences, then Calculation -under Formulas and Lists-, then check “Use iterative calculation”.

(c) Complete, in full, the Financial Cash Flows that you have in the excel sheet. Use a formula to check that your Cash Flow from Assets is equal to the Cash Flows to Investors (use row 83 for this purpose).

(d) Find the value of the firm for the next 5 years based on strategic plan A.

(e) If the value of “The Lord of the Games, Inc.” for the next five years under strategic plan B is $1, 000, 000, which strategic plan should the company follow? A or B? Why?

(f) Imagine that the client, after looking at the file, asks why financial cash flows are being used to assess the value of the firm rather than profits. What would you say to your client?

(g) What would be the lowest sales growth that can be tolerated (for break-even)? (use goal seek)

(h) Complete the table at the bottom using excel Data Table

2. Tobago Petroleum is considering a new project that complements its existing busi-ness. The machine required for the project costs $3.8 million. The marketing department predicts that sales related to the project will be $2.5 million per year for the next four years, after which the market will cease to exist. The machine will be depreciated down to zero over its four-year economic life using the straight-line method. Cost of goods sold and operating expenses related to the project are predicted to be 25 percent of sales. Tobago also needs to add net working capital of $150, 000 immediately. Net working capital will remain constant throughout the life of the project. Additionally, it is assumed that initial investment in net working capital will be recovered in full at the end of the project’s life. The corporate tax rate is 36 percent. The required rate of return for Tobago (or Hurdle Rate) is 17 percent. Should Tobago proceed with the project? Note: Straight line deprecia-tion is calculated by dividing the difference between an asset’s cost and its expected salvage value (which in this case is zero) by the number of years it is expected to be used.