Questions for Review
Questions for Discussion
You are working with an entrepreneur who has developed a new product and is seeking venture capital to go into immediate production. She wants you to develop a spreadsheet application. She has orders for 100,000 units in the first year at a selling price of $6.00 per unit. According to her most likely scenario, both numbers are expected to increase 20% annually. She is able to rent a production facility for $50,000 a year for five years. The variable manufacturing cost is $1.50 per unit and is projected to increase at 10 percent a year. Administration costs another $25,000 a year and will likely increase at 5 percent annually. Develop a five-year financial forecast similar to that in Figure 9.7 showing profits before and after taxes. You should assume a tax rate of 36 percent.
Figure 9.7 A Pro Forma Spreadsheet DSS.
Create 4 additional scenarios for this situation - Scenario 1 is rapid increase in sales (30% per year) and slow increase in variable costs (5% per year); Scenario 2 is rapid increase in sales (30% per year) and rapid increase in variable costs (15% per year); Scenario 3 is slow increase in sales (10% per year) and slow increase in variable costs (5% per year); Scenario 4 is slow increase in sales (10% per year) and rapid increase in variable costs (15% per year).
Your worksheet should be completely flexible and capable of accommodating a change in any of the initial conditions or projected rates of increase, without having to edit or recopy any of the formulas. One way to meet this design goal is to create a table of the assumptions about initial conditions and rates of increase, and then reference the table cells as absolute references when building formulas.
|DSSResources.COMsm is maintained and all its pages are copyrighted (c) 1995-2002 by D. J. Power (see home page). Please contact firstname.lastname@example.org. This page was last modified Wednesday, May 30, 2007. See disclaimer and privacy statement.|