Rick is considering opening a hotdog stand on Michigan Avenue. Ricks 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. Rick'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. Rick 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

2. Rick 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 burger goes up by 10% every year.

-Drinks- $38,400

-Other food supplies- $58,900

-Nonfood supplies- $50,200

3. 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

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, furniture 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?

3. Consider several values of cost of capital from 7% to 13%. Looking at the chart, insert your observations and conclusion on page 2.

4. Set some goal value for NPV (choose a value yourself) and use Excel file to find number of hotdogs (quantity) that the new stand must sell annually to achieve the goal.

5. Suppose that you are unsure about the price Rick would be able to charge. Rick would like to generate at least $300,000 in NPV with the new kiosk. Find price per hotdog necessary to achieve this goal.

please answer all, thank you.

LOUIS Chech fx D Years 0.00 1.00 2.00 E 3.00 G 5.00 4.00 Price per unit No of units Total Sale of Burgers Drinks 6 Other food sales Total Sales (Gross sales) 10 Cost of Raw materials: 11 Burgers 12 Total Raw material for the burgers 13 Drinks 14 Other food supplies 15 Nonfood supplies 16 17 Total Cost of goods sold 18 19 Gross Profit 20 21 kitchen and service staff 22 License and rent costs 23 Depreciation 1.2 Model Calculation of NPY 24 Carines before tax 26 Tax @ 35 25 27 20 Net Income 10 Plus Depreciation 31 Purchase of retail kosk 32 33 Free Cash Flow 34 35 Cost of capital 10% 36 PV offs NPVSum of all PV) 38 NEV $ $ $ $ $ $ 5 $ Rate 7.00 8.00% 5 9.OONIS 6 10.DONS 7 11.OONS 12 OONS 13 DONS 10 11 12 13 34 C NPVR Cost of Capital Tryout Year Year 2 Year3 $ $ $ - $ - $ $ $ - $ - $ - $ $ $ $ $ 5 $ $ $ $ Years $ $ $ $ 5 $ $ - NPV and cost of capital 27 11 TELFIE!!! 20 21 22 23 24 25 26 Desired NPV 1000000 s-Insert a positive NPV amount here Years 0 1 2 4 5 22 2 72 72 77 Price per unit No of units Total Sale of Burgers 7 Drinks Other food sales 10 Total Sales 11 12 Cost of raw material: 1 Burgers 14 Drinks Is Other food supplies 16 Nonfood supplies 17 13 Total Cost of goods sold 19 20 Gross Prof 21 22 Kitchen and service staff 23 License and rentos 23 License and rent costs 24 Depreciation 23 tarings before tas 26 27 Tax 35% 29 Net Income 31 Plus Depreciation 32 Purchase of retail kiosk 33 34 Free Cash Flow 35 36 Cost of capital 10% 37 PV of CF 38 NPVISum of all pys) D Desired NPY $300.000 Desired NPV Years 0 1 2 5 72 22 22 22 77 Price per unit No of units Total Sole of Burgers Drinks Other food sales 30 Total Sales 11 12 Cost of Raw materials: 13 Burgers 34 Drinks 1s Other food supplies 16 Nonfood supplies 17 18 Total Cost of goods sold 19 20 Gross Profit 22 22 Kitchen and service staff 23 License and rent costs C D 2 License and rent costs 24 Depreciation 25 Earnings before tax 26 27 Tax 35% 29 Net Income 30 31 Plus: Depreciation 32 Purchase of retail ask 33 34 Free Cash Flow 35 36 cost of capital @ 10% 37 PV of C 38 NPV/Sum of all PVs) 39 Difference