You and your business partners are considering applying for a franchise. If approved, you expect startup costs to be $1577928. In equipment that is depreciable on a seven-year MACRS schedule. Your plan is to start and operate the business for 6 years at the end of which time you expect to sell the business for $1577928 plus $200,000. You expect to have initial working capital needs of $30,000, but these needs will remain proportionate to sales (they will grow at the same rate as sales grow0. You expect sales in the first year to be $300,000 and that sales will grow by 15% per year. You project annual fixed operating expenses of $75,000 in the first year. These fixed expenses will grow by 5% per year. Your annual variable operating expenses are expected to be 40% of sales.

You expect to pay taxes of 21%. Assume your required return is 8%. Should you apply for a Guthries Franchise? Prepare a report responding to the following prompts:

1. Prepare pro forma income statements and operating cash flow projections. Explain your pro forma statements in your report. You must use Excel (or another spreadsheet package) for your income statement and cash flow projections, and your calculations must make use of the formula and cell-reference functions within Excel.
2. Estimate the total cash flows for this opportunity. Explain your estimates in your report. Again, your calculations must be done within Excel using appropriate formulas and cell references.
3. Estimate the opportunitys NPV. Explain how you arrived at your NPV estimates in the report. Again, your calculations must be done within Excel using appropriate formulas and cell references.
4. Consider what happens to cash flows and NPV if Sales are 20% more than expected. What if sales are 20% less than expected? Discuss this analysis in your report. Your calculations must be such that you simply change one cell (to reflect the change in sales) and your spreadsheet recalculates everything.
5. What happens to your base NPV calculation if your required return is 12% rather than 8%?
6. What is your recommendation? Should you and your partners pursue this opportunity? Explain your recommendation and provide your rationale.

Please help