Project #89951 - financial modeling

Problem 1

In Model 10 (Chapter 13) as a simplification we assumed that depreciation for a year will equal 8% of the Gross PP&E balance at the end of that year. In practice, depreciation expense is generally calculated using a depreciation schedule based on the following type of information for the depreciable assets: when an asset was put in service, its expected life and salvage value, and the depreciation method to be used.

Modify Model 10 to reflect depreciation expense calculated using the declining balance depreciation method and the following assumptions:

¨     Of the Gross PP&E on the books at year-end 2008, all but $650 (original purchase cost) had been fully depreciated by that time. This $650 of Gross PP&E were put on the books on 1/1/2006. For depreciating these, assume a salvage value equal to 10% of purchase cost and 10 year depreciation life.

¨     Starting with 2009 additions to Gross PP&E for each year will be put on the books in the middle of the year, will have salvage value equal to 10% of original purchase cost, and 6 year depreciation life.

To calculate the depreciation expense use Excel’s DB function, which is explained and illustrated in Excel’s Help. Be prepared to explain in the class the concept underlying the declining balance depreciation method. Keep all other assumptions the same as in Model 10.

(Note that the depreciation expense based on these assumptions may not be quite consistent with the historical data. Ignore this discrepancy and remember that you cannot change historical data.)

Submit the balance sheet, income statement, and depreciation schedule for your models (printed out with two decimal points). Also include an updated assumptions page.




Problem 2

Management has decided that it wants to reflect the following financing assumptions in the projections for 2009-2012.

¨     For 2009 and 2010 any incremental (external) financing or repayments necessary to balance the balance sheets will comprise 45% short-term debt and 55% long-term debt. (The company will not issue or repurchase any stock in 2009 and 2010.)

¨     For 2011 and 2012 any incremental (external) financing or repayments necessary to balance the balance sheets will comprise 15% short-term debt, 20% long-term debt and 65% equity.

All other assumptions will be the same as those in Model 11. For example, to determine the price at which you will sell or buy-back stocks in 2011-2012, you will use the same approach as in Model 11. To develop this model, it maybe easiest to start with a copy Model 11 and make the necessary modifications. , But you may want to make some changes to its structure.

Note that the financing assumptions in this problem are very different from those in Model 11, not just in numbers but in concept as well. Do not start building this model until you clearly understand the financing assumptions. Also, do not hard code the above percentage numbers. Make them independent variables so that they can be changed easily.

Submit only the balance sheet, income statements and only those financial indicators that are needed for the calculations in the model, printed out with two decimal points. Also include an updated assumptions page.



Problem 3: Financial statements forecasting

(All dollar amounts are in millions of dollars.)

For this problem start with Model 9 for Vitex Corp. in Chapter 13 and then modify it to create a model reflecting the assumptions listed below.

In all cases assume that all transactions take place on the last business day of the year (instead of uniformly through the year as the book models assumes).

In your submission do not include any cash flow statement, assumptions page, or statement of the problem, objective, etc.

Part A

Vitex will repay existing long-term debt as follows (shown with the interest rate for each tranche): $25MM with rate of 9% in 2009, $50 at 8% in 2010, $15 at 7% in 2011, and $20 at 7% in 2012. In each year half of the LTD repaid will be replaced with new issues of LTD at interest rates of 4%, 4%, 5%, and 6%, respectively.

The LTD balance at the end of 2008 was as shown in the book models and the total annual interest expense on this balance was $13.5.

Create a model to calculate the year-by-year LTD balance and interest expense for 2009 to 2012 reflecting the above data. Do it on a separate spreadsheet and make it flexible and easy to follow.

Part B

Create a projected Income Statement and Balance Sheet model for Vitex for 2009 to 2012 reflecting the following assumptions:

  1. Long-term debt balances and interest expense on LTD by year will be as in Part A. On your income statement create a separate line for Interest Expense on LTD and link the interest expense and balance numbers for LTD to the model you created in Part A.
  2. The company will issue $5 of new equity at the projected year-end price at the end of each of 2009 to 2012. (The issuance price will be determined by the P/E-based method used in Model 11.
  3. Use STD to balance the balance sheet. Management wants to maintain a minimum Cash and Marketable Security (C&MS) balance of $10. If in any year all STD is paid off then any excess cash should be deposited in the C&MS account. In the Income Statement create a separate line for Interest Expense on STD. (You are effectively splitting the single interest expense line in the book models into two lines: One for LTD and one for STD.
  4. The dividend policy will be the same as in Model 9.

Note that this problem implies different capital structure (i.e., STD, LTD and SE) constraints from the models in the book. You cannot and should not try to superimpose on this model the capital structure constraints from any of the book models.

Also, this model will need two sets of circular references. Make sure you understand what those would be and incorporate them in your model.


Subject Business
Due By (Pacific Time) 11/01/2015 09:00 pm
Report DMCA

Chat Now!

out of 1971 reviews

Chat Now!

out of 766 reviews

Chat Now!

out of 1164 reviews

Chat Now!

out of 721 reviews

Chat Now!

out of 1600 reviews

Chat Now!

out of 770 reviews

Chat Now!

out of 766 reviews

Chat Now!

out of 680 reviews
All Rights Reserved. Copyright by - Copyright Policy