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. 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 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 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 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 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. Construct a model in Excel to evaluate the project. (Input values in sheet 1) What is the NPV of this investment? Consider several values of cost of capital from 7% to 13%. Looking at the chart, insert your observations and conclusion on page 2. 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. Suppose that you are unsure about the price John would be able to charge. John would like to generate at least $300,000 in NPV with the new kiosk. Find price per hotdog necessary to achieve this goal. Additional tutorial videos: Goal seek:- https://www.youtube.com/watch?v=WFhGjMoZZmM Goal seek:- https://www.youtube.com/watch?v=OhnkuBVTcg8 NPV, IRR using Excel videos:- https://www.youtube.com/watch?v=N0NmVhVtP3g

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. 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 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 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 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 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. Construct a model in Excel to evaluate the project. (Input values in sheet 1) What is the NPV of this investment? Consider several values of cost of capital from 7% to 13%. Looking at the chart, insert your observations and conclusion on page 2. 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. Suppose that you are unsure about the price John would be able to charge. John would like to generate at least $300,000 in NPV with the new kiosk. Find price per hotdog necessary to achieve this goal. Additional tutorial videos: Goal seek:- https://www.youtube.com/watch?v=WFhGjMoZZmM Goal seek:- https://www.youtube.com/watch?v=OhnkuBVTcg8 NPV, IRR using Excel videos:- https://www.youtube.com/watch?v=N0NmVhVtP3g

Essentials Of Investments

11th Edition

ISBN:9781260013924

Author:Bodie, Zvi, Kane, Alex, MARCUS, Alan J.

Publisher:Bodie, Zvi, Kane, Alex, MARCUS, Alan J.

Chapter1: Investments: Background And Issues

Section: Chapter Questions

Problem 1PS

See similar textbooks

Related questions

Question

Project 2- Capital Budgeting

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

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

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

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

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.

Construct a model in Excel to evaluate the project. (Input values in sheet 1)
What is the NPV of this investment?
Consider several values of cost of capital from 7% to 13%. Looking at the chart, insert your observations and conclusion on page 2.
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.
Suppose that you are unsure about the price John would be able to charge. John would like to generate at least $300,000 in NPV with the new kiosk. Find price per hotdog necessary to achieve this goal.

Additional tutorial videos:

Goal seek:- https://www.youtube.com/watch?v=WFhGjMoZZmM

Goal seek:- https://www.youtube.com/watch?v=OhnkuBVTcg8

NPV, IRR using Excel videos:- https://www.youtube.com/watch?v=N0NmVhVtP3g

Expert Solution