Details of this Paper

Open the file Prob 05-01. Xls. You will note that...




Open the file Prob 05-01. Xls. You will note that it has five worksheets, or ?tabs?. In this project, you will work only with three. You will not use the WACC or Proj. oNextf Financials and Valuation worksheets. The actual historical (2001 to 2003) financial statements for Van leer are shown in the rightmost tab, labeled Actual. a. The first task is to calculate the historical ratios as discussed in the text. Click on the Historical Analysis tab and fill in the yellow-shaded cells with formulas. Note that many cells have comments, indicated by a red triangle in the upper right hand corner. You can view the comments by letting your cursor hover above one of the cells, and they provide hints or instructions for filling in the cell. There are additional instructions in the sheet itself. You will calculate historical ratios and free cash flows in this worksheet. b. Next click on the Inputs tab to open that worksheet. In the Yellow cells, fill in the projected values for 2004 for the various ratios needed for projecting the operating results. Be careful no to enter anything in other cells (The values are given in Exhibit 5-4). c. Open the worksheet labeled Projection of FCF and use the projected ratios to build formulas that complete the 2004 projections (column D) of operating income entries in the income statement and the operating asset and liability entries in the balance sheet by entering appropriate formulas. For example, the estimate of 2004 sales will equal the actual 2003 sales multiplied by one plus the projected growth rate in sales. To enter the formula, click on cell D6, enter an ?=? sign, click on the adjacent cell, C6, type ?*(1+? then open the Inputs worksheet by clicking on the Inputs tab and click on the call containing the 2004 sales growth rate, which is C4. Now type a close parentheses, ?)?, hit the return key, and the formula will be entered as ?=C6*(1+ Inputs!C4)?, with the numerical result 1110.0. For another example, to get the estimate for COGS for 2004, we want to multiply the sale levels we just estimated by the COGS to sales ration. Click on cell E7, enter a ?=? sign, then click on cell D6 and then enter the ?*? sign. Now go to Inputs worksheet by clicking on the tab, then click on cell C5, and hit return . the formula entered should be ?=D6*Inputs!C5?, and the numerical result, 693.8 d. Finally, using the estimates for the operating portions of the balance sheet and income statements, estimate the components of the free cash flow in the middle section of the worksheet labeled Projection of FCF, again, by entering the appropriate formula?s into the cells. (Check your results against the projections in Exhibit 5-5. When your results agree with Exhibit 5-5, you have successfully completed the project).,Here is Exhibit 5-4 to go along with assignment,Here is Exhibit 5-5 to go with assignment


Paper#2680 | Written in 18-Jul-2015

Price : $25