John is considering opening a hotdog stand on Michigan Avenue. Johns 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. 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

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 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. Working Capital as follows:

Increase in the receivables (AR) is expected to be equal to 10% of gross sales

Increase in payables associated with the new stand is estimated to be equal to 15% of the cost of raw products

The project will require additional cash in the amount of 5% of gross sales

Net working capital is fully recovered (i.e., reduced to zero) after the completion of the project

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

12:12 PM Sun Nov 21 75% Capital Budgeting Excel.xlsx 3 NPV at various values 4 Goal seek- quantity 5 Goal seek-price 1,2 Model & Calculation of NPV Years Price per il No of units Total Sale of Burgers Drinks Other food sales Total Sales Cost of Raw materials Burgers Drinks Other food supplies Nonfood supplies Total Cost of goods sold Gross Profit Kitchen and service staff License and rent costs Depreciation Earnings foretas Tax 35% Net Inceme Plus: Depreciation Purchase of retail kiosk Les Change in Networking capital Free Cash Flow Cost of capital in PV of NPVSome all PV 12:12 PM Sun Nov 21 75% Capital Budgeting Excel.xlsx 3 NPV at various values 4 Goal seek-quantity 1.2 Model & Calculation of NPV 5 Goal seek-price PY Year 4 Years 0 0 0 Rats 7,000 8.00 9.00 10.00 100 12.00% 13.00 NPV at Cost of Capital Trvat Year Year? Year o ol 0 0 0 0 O 0 O 0 0 ol 0 0 0 0 0 0 0 0 0 ol 0 0 0 0 NPV and cost of capital 3 2 Write.conchiesten af what sense here 12:12 PM Sun Nov 21 75% 1.2 Model & Calculation of NPV Capital Budgeting Excel.xlsx 3 NPV at various values 4 Goal seek-quantity Insert a positive NPV amount here 5 Goal seek-price Desired NPN Years 2 3 4 99 22 Price per unit No of units Total Sale of Burgers Drinks Other food sales Total Sales Cost of Raw materiale Burgers Drinks Other food supplies Nonfood supplies Total Cost of goods salt Gross Profit Kitchen and service stat License and rent costs Depreciation Earnings before tax Tax 35 Net Income Plus Depreciatico Purchase of retail Kiosk Lex Change in Networking capital Free Cash Flow Cost of capital 10% PV of Ch NPV/Sum of all 12:12 PM Sun Nov 21 75% 1.2 Model & Calculation of NPV Capital Budgeting Excel.xlsx 3 NPV at various values 4 Goal seek-quantity 5.800.000 Insert a positive NPV amovat here 5 Goal seek-price Desired NPN Years 0 2 3 79 Price per unit No of units Total Sale of Burgers Drinks Other food sales Total Sales Cost of Raw materiale Burgers Drinks Other food supplies Nonfood supplies Total Cost of goods salt Gross Profit Kitchen and service stat License and rent costs Depreciation Earnings before tax Tax 35 Net Income Plus Depreciatico Purchase of retail Kiosk Lex Change in Networking capital Free Cash Flow Cost of capital 10% PV of Ch NPV/Sum of all 12:12 PM Sun Nov 21 75% Capital Budgeting Excel.xlsx 3 NPV at various values 4 Goal seek- quantity 5 Goal seek-price 1,2 Model & Calculation of NPV Years Price per il No of units Total Sale of Burgers Drinks Other food sales Total Sales Cost of Raw materials Burgers Drinks Other food supplies Nonfood supplies Total Cost of goods sold Gross Profit Kitchen and service staff License and rent costs Depreciation Earnings foretas Tax 35% Net Inceme Plus: Depreciation Purchase of retail kiosk Les Change in Networking capital Free Cash Flow Cost of capital in PV of NPVSome all PV 12:12 PM Sun Nov 21 75% Capital Budgeting Excel.xlsx 3 NPV at various values 4 Goal seek-quantity 1.2 Model & Calculation of NPV 5 Goal seek-price PY Year 4 Years 0 0 0 Rats 7,000 8.00 9.00 10.00 100 12.00% 13.00 NPV at Cost of Capital Trvat Year Year? Year o ol 0 0 0 0 O 0 O 0 0 ol 0 0 0 0 0 0 0 0 0 ol 0 0 0 0 NPV and cost of capital 3 2 Write.conchiesten af what sense here 12:12 PM Sun Nov 21 75% 1.2 Model & Calculation of NPV Capital Budgeting Excel.xlsx 3 NPV at various values 4 Goal seek-quantity Insert a positive NPV amount here 5 Goal seek-price Desired NPN Years 2 3 4 99 22 Price per unit No of units Total Sale of Burgers Drinks Other food sales Total Sales Cost of Raw materiale Burgers Drinks Other food supplies Nonfood supplies Total Cost of goods salt Gross Profit Kitchen and service stat License and rent costs Depreciation Earnings before tax Tax 35 Net Income Plus Depreciatico Purchase of retail Kiosk Lex Change in Networking capital Free Cash Flow Cost of capital 10% PV of Ch NPV/Sum of all 12:12 PM Sun Nov 21 75% 1.2 Model & Calculation of NPV Capital Budgeting Excel.xlsx 3 NPV at various values 4 Goal seek-quantity 5.800.000 Insert a positive NPV amovat here 5 Goal seek-price Desired NPN Years 0 2 3 79 Price per unit No of units Total Sale of Burgers Drinks Other food sales Total Sales Cost of Raw materiale Burgers Drinks Other food supplies Nonfood supplies Total Cost of goods salt Gross Profit Kitchen and service stat License and rent costs Depreciation Earnings before tax Tax 35 Net Income Plus Depreciatico Purchase of retail Kiosk Lex Change in Networking capital Free Cash Flow Cost of capital 10% PV of Ch NPV/Sum of all