| | INSTRUCTIONS |
| | The major point of this assignment is for you to realize there are three separate alternatives presented in the case verbiage. |
| | Using the budget figures and projection assumptions given, you are to calculate a 5 year projected income statement and cash flow as well as the WACC for each alternative. |
| | As in a real corporation, you can take each alternative, calculate the decision-making capital budgeting analyses |
| | (NPV and IRR) using a discount rate and risk factors (sometimes referred to as the hurdle rate and/or required rate of return). |
| | Then, a decision can be made as to which alternative would be best for the organization. As you will see, each alternative not only has a different projection, |
| | but a different level of risk that is built into the NPV calculations. Of course, all possible variables are not accounted – but this exercise will give you a |
| | flavor of what is used in the real-world in regards to capital budgeting. |
| | Below are the three scenarios from the case along with additional assumptions in order for you to fulfill the requirements of this assignment. |
| | You can use the formats provided below as a way to organize the analysis. You will need to do the calculations for the various elements and factors as part of your analysis. |
| | Remember … Year 0 is when the initial investment is made, there are no sales or costs in that period … it is the "present" of present value. There may be follow-on investments in subsequent years. |
| | Note -- please realize that the assumptions in the three below scenarios are just that -- "assumptions". They are given to create an example of the decision making process used |
| | Please show your calculations. If necessary, set up additional spreadsheets using the other "sheets", linking from the Scenarios sheet to the appropriate information on the back sheets. That will help you organize your thoughts and help in tracing data to your conclusions. Please submit your completed spreadsheet with your written analysis. |
| | SCENARIO ONE - Paragraph 4 of Furniture Scenario - increase in technology. |
| | 1) Year one projections are the same as the June Budget x 12 |
| | 2) Year 2 and beyond -- Revenues projected at 10% increase per year. |
| | 3) Year 2 and beyond -- Varriable costs* -- increase at the same rate as sales. |
| | 4) Year two and beyond -- Fixed costs increase at a standard inflation rate of 3% per year. |
| | 5) Year two and beyond -- Depreciation increases at 20% per year. |
| | 6) Capital Expenditures -- Initially $7,000,000; and an additional $700,000 is years two, four, and in year five. |
| | 7) Labor wages & benefits decrease by 20% for year two due to technology savings -- then a 10% per year increase in year three and beyond. |
| | | | Year 0 | Year 1 | Year 2 | Year 3 | Year 4 | Year 5 |
| | Revenue |
| | High-End |
| | Mid-Grade |
| | Total Revenue |
| | Cost of Goods * |
| | High-End |
| | Mid-Grade |
| | Total Cost of Goods |
| | Net Revenue |
| | Labor Wages |
| | Office Salaries |
| | Benefits |
| | Supplies |
| | Utilities |
| | Insurance |
| | Property Taxes |
| | Total Operating Expense |
| | Earnings before Taxes & Depr |
| | Depreciation |
| | Earnings before Taxes |
| | Income Taxes (42%) |
| | Net Earnings |
| | Add back Depreciation |
| | Subtract Out Capital Expenditures |
| | TOTAL CASH FLOW FOR ANALYSIS |
| | DISCOUNT RATE to use for calculating the NPV: |
| | Add the calculated WACC to a risk rate your analysts have assigned to this scenario |
| | Risk Rate Assigned = 1.6% |
| | WACC data needed for calculation: |
| | Cost of Debt = 8% |
| | Desired Mix for weighting (Debt = 40%; Equity = 60%) |
| | Cost of Equity data needed: |
| | Next Annual Dividend = $2 |
| | Current Stock Market Value = $40 |
| | Dividend Growth Factor = 7% |
| | NPV = |
| | IRR = |
| | SCENARIO TWO - Paragraph 5 of Furniture Scenario - converting factory from primary manufacturing to primary distribution |
| | 1) Year one projections are the same as the June Budget x 12 |
| | 2) Year 2 -- Revenues decrease 10% and then increase at 5% . |
| | 3) Year 2 and beyond -- Varriable costs* -- increase at the same rate as sales. |
| | 4) Year two and beyond -- Fixed costs increase at a standard inflation rate of 3% per year. |
| | 5) Year two and beyond -- Depreciation increases at 10% per year. |
| | 6) Capital Expenditures -- Initially $5,000,000; and an additional $500,000 is years two, four, and in year five. |
| | 7) Labor wages & benefits decrease by 40% for year two due to converting to distribution -- then a 5% per year increase in year three and beyond. |
| | 8) Office Salaries increase by 20% for year two due to converting to distribution -- then a 5% per year increase in year three and beyond. |
| | | | Year 0 | Year 1 | Year 2 | Year 3 | Year 4 | Year 5 |
| | Revenue |
| | High-End |
| | Mid-Grade |
| | Total Revenue |
| | Cost of Goods * |
| | High-End |
| | Mid-Grade |
| | Total Cost of Goods |
| | Net Revenue |
| | Labor Wages |
| | Office Salaries |
| | Benefits |
| | Supplies |
| | Utilities |
| | Insurance |
| | Property Taxes |
| | Total Operating Expense |
| | Earnings before Taxes & Depr |
| | Depreciation |
| | Earnings before Taxes |
| | Income Taxes (42%) |
| | Net Earnings |
| | Add back Depreciation |
| | Subtract Out Capital Expenditures |
| | TOTAL CASH FLOW FOR ANALYSIS |
| | DISCOUNT RATE to use for calculating the NPV: |
| | Add the calculated WACC to a risk rate your analysts have assigned to this scenario |
| | Risk Rate Assigned = 5.1% |
| | WACC data needed for calculation: |
| | Cost of Debt = 8% |
| | Desired Mix for weighting (Debt = 40%; Equity = 60%) |
| | Cost of Equity data needed: |
| | Next Annual Dividend = $2 |
| | Current Stock Market Value = $40 |
| | Dividend Growth Factor = 7% |
| | NPV = |
| | IRR = |
| | SCENARIO THREE - Paragraph 6 of Furniture Scenario - adding another product to the finish of the furniture - cost is minimal. |
| | 1) Year one projections are the same as the June Actual x 12 |
| | 2) Year 2 and beyond -- Revenues projected at 10% increase per year. |
| | 3) Year 2 and beyond -- Varriable costs* -- increase at the same rate as sales. |
| | 4) Year two and beyond -- Fixed costs increase at a standard inflation rate of 3% per year. |
| | 5) Year two and beyond -- Depreciation increases at 5% per year. |
| | 6) Capital Expenditures -- Initially 500,000; and an additional 50,000 is years two, four, and in year five. |
| | | | Year 0 | Year 1 | Year 2 | Year 3 | Year 4 | Year 5 |
| | Revenue |
| | High-End |
| | Mid-Grade |
| | Total Revenue |
| | Cost of Goods * |
| | High-End |
| | Mid-Grade |
| | Total Cost of Goods |
| | Net Revenue |
| | Labor Wages* |
| | Office Salaries |
| | Benefits * |
| | Supplies |
| | Utilities |
| | Insurance |
| | Property Taxes |
| | Total Operating Expense |
| | Earnings before Taxes & Depr |
| | Depreciation |
| | Earnings before Taxes |
| | Income Taxes (42%) |
| | Net Earnings |
| | Add back Depreciation |
| | Subtract Out Capital Expenditures |
| | TOTAL CASH FLOW FOR ANALYSIS |
| | DISCOUNT RATE to use for calculating the NPV: |
| | Add the calculated WACC to a risk rate your analysts have assigned to this scenario |
| | Risk Rate Assigned = 0% |
| | WACC data needed for calculation: |
| | Cost of Debt = 8% |
| | Desired Mix for weighting (Debt = 40%; Equity = 60%) |
| | Cost of Equity data needed: |
| | Next Annual Dividend = $2 |
| | Current Stock Market Value = $40 |
| | Dividend Growth Factor = 7% |
| | NPV = |
| | IRR = |
neel
fin571_-_wk4_furniture_solution_template_3.xls
