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 - 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, 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 . HINTS: I. For worksheet one (Model and calculation of NPV) 1. Depreciation starts from year t=1 and ends at t=5. 2. There is no operating and depreciation expenses for the year t=0. 3. The total number of days the hotdog stand operates is 365 days. Use 365 while calculating the total number of hotdogs sold in each year. 4. NPV is the sum of all the present values of cash flows for the year 1 to 5 . II. For worksheet two ("Year" values at various cost of capital) 1. Formula for "Year" PV calculation for year 1,2,3,4 and 5 PV ( for year n)= Free Cash Flow /(1+r)n where, N= corresponding year (n=1,n=2 and so on ) Free cash flow = The free cash flow from the first worksheet for the corresponding year R= cost of capital given on the same row for each year in the second worksheet where, N= corresponding year (n=1,n=2 and so on ) Free cash flow = The free cash flow from the first worksheet for the corresponding year R= cost of capital given on the same row for each year in the second worksheet Then calculate the NPV, Formula for the NPV = Cash outflow ( for year n)+PV( for year n) (Note: The cash outflow is only for year one at different cost of capital, not for other years) Scroll down: In the highlighted area provide a conclusion of what you see is happening in the graph in just three or fewer lines. Autosave Home Insert Draw Page Layout Formulas Data Review View O Tell me Write conclusion of what you see here: 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 - 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, 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 . HINTS: I. For worksheet one (Model and calculation of NPV) 1. Depreciation starts from year t=1 and ends at t=5. 2. There is no operating and depreciation expenses for the year t=0. 3. The total number of days the hotdog stand operates is 365 days. Use 365 while calculating the total number of hotdogs sold in each year. 4. NPV is the sum of all the present values of cash flows for the year 1 to 5 . II. For worksheet two ("Year" values at various cost of capital) 1. Formula for "Year" PV calculation for year 1,2,3,4 and 5 PV ( for year n)= Free Cash Flow /(1+r)n where, N= corresponding year (n=1,n=2 and so on ) Free cash flow = The free cash flow from the first worksheet for the corresponding year R= cost of capital given on the same row for each year in the second worksheet where, N= corresponding year (n=1,n=2 and so on ) Free cash flow = The free cash flow from the first worksheet for the corresponding year R= cost of capital given on the same row for each year in the second worksheet Then calculate the NPV, Formula for the NPV = Cash outflow ( for year n)+PV( for year n) (Note: The cash outflow is only for year one at different cost of capital, not for other years) Scroll down: In the highlighted area provide a conclusion of what you see is happening in the graph in just three or fewer lines. Autosave Home Insert Draw Page Layout Formulas Data Review View O Tell me Write conclusion of what you see here
