You are the CFO for Carco, a small car rental company. You are trying to get some idea of what Carco’s financial and income statements will look like during the current year (year 0) and the next five years.The following relationships hold:
■ Current assets for each year are a “current assets factor” multiplied by the year’s sales,where the current assets factors for different years are independent normal random variables with mean 0.15 and standard deviation 0.02.
■ Each year,“fixed assets at cost” equals depreciation plus fixed assets.
Set up a spreadsheet to model the current year (year 0) and next five years of Carco’s financial future. Now simulate the firm’s future. Use your output to answer the following questions. (Note: Your spreadsheet is allowed to contain circular references. There are many of these. For example, stock purchased each year depends on long-term debt, and long-term debt depends on stock. To resolve the circular references, use the Tools/Options menu item click on the Calculations tab, check the Iterations box, and enter 20 as the Maximum Number of Iterations.This ensures that the spreadsheet will recalculate itself 20 times, which in turn ensures that the values in the spreadsheet will converge to the correct values.)
Questions
1. There is only a 5% chance that total new debt will exceed what value?
2. On the average, total interest payments for the next five years will equal what value?
3. What is the probability that profit will be negative during year 5?