John is considering opening a hotdog stand on Michigan Avenue. John's market research shows that the clientele is young professionals, typically without children, who like the traditional aspect of eating hotdogs, but also relish his gourmet, specially manufactured low-fat hotdogs and the healthy side dishes his stand also sells. John's overall plan is to get the stand up and running for five years, and then sell the stand off to a new owner and retire to Florida. 1. The revenues at his current location are as follows: - Sales value- $5 per hotdog. Sale price for hotdogs increase every year by 50%. - Average daily sales- #300 hotdogs. The sales increase every year by 20% in quantity. - Drinks- $100,000. - Other food sales- $155,000 2. John estimates that annual operating costs for his stand as follows: - Kitchen and service staff (5 people)-total of $200,000 per year - License and rent costs $150,000 - Raw materials: - -Hotdogs- $2 per hotdog. Raw material price per hotdog goes up by 10% every year. -Drinks- $38,400 -Other food supplies- $58,900 -Nonfood supplies- $50,200 3. John estimates that the cost of starting up a stand will be as follows: - Purchase of retail kiosk (mobile retail food outlet) $500,000 - Specialized kitchen equipment $50,000 - Installation of the kitchen equipment $10,000 - Furniture and fittings $50,000 4. Other information: - Marginal tax rate is 35\% - Cost of Capital is 10% - Cost of the stand (kiosk), together with the cost of the equipment, fumiture and fittings and the installation, is depreciated over five years according to the straight-line method. The stand (together with the furniture and kitchen equipment) is expected to be worth $300,000 after five years of service. Note: The definition of an asset's cost is all costs that are necessary to get an asset in place and ready for use. 1. Construct a model in Excel to evaluate the project. (Input values in sheet 1) 2. What is the NPV of this investment? Priceper unit No of units of hotdogs Total Sale of hotdog Drinks Other food sales Total Sales (Grosssales) 0.00 10 Cost of Raw materials: 11. Hotdogs 12. Total Raw material for the hotdogs 13 Drinks 14 Other food supplies 15 Nonfood supplies 16 37 NPV(Sum of all PVs) Write conclusion of what you see here