Answered step by step
Verified Expert Solution
Question
1 Approved Answer
the first document has all the information you need. the other documets are the ones you need to fill out. 1. John estimates that the
the first document has all the information you need. the other documets are the ones you need to fill out.
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 cquipment $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- 55 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-S100,000 Other food sales-S155,000 4. Working Capital asf * 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 Networking capital is fully recovered (1.c., 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. 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 tuse Excel file to find number of hotdogs (quantity) that the new stand must sell antally to achieve the goal 5. Suppone 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. Years 2 3 5 Price per unit 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 before tas Tax @ 35% Net Income Plus: Depreciation Purchase of retail kiosk Less: Change in Networking capital Free Cash Flow Cost of capital a 10% PV of CFS NPV/Sum of all PVS) NPV Year 1 Year 2 Year 3 Year 4 Year 5 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 Rate 7.00% 8.00% 9.00% 10.00% 11.00% 12.00% 13.00% 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 NPV and cost of capital 3 2 3 Write conclusion of what you see here: Years 0 1 2 3 4 5 ?? 77 ?? ?? 77 3 Price per unit 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 before tax Tax @ 35% Net Income Plus: Depreciation Purchase of retail kiosk Less: Change in Networking capital Free Cash Flow Cost of capital @ 10% PV of CFS NPV/Sum of all PV) Years 0 1 2 2 3 4 5 ?? T? ?? ?? 22 Price per unit 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 Profis Kitchen and service staff License and rent costs Depreciation Earnings before tax Tax @ 35% Net Income Plus: Depreciation Purchase of retail kiosk Less: Change in Net working capital Free Cash Flow Cost of capital 10% PV of CFS NPV(Sum of all PV) 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 cquipment $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- 55 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-S100,000 Other food sales-S155,000 4. Working Capital asf * 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 Networking capital is fully recovered (1.c., 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. 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 tuse Excel file to find number of hotdogs (quantity) that the new stand must sell antally to achieve the goal 5. Suppone 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. Years 2 3 5 Price per unit 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 before tas Tax @ 35% Net Income Plus: Depreciation Purchase of retail kiosk Less: Change in Networking capital Free Cash Flow Cost of capital a 10% PV of CFS NPV/Sum of all PVS) NPV Year 1 Year 2 Year 3 Year 4 Year 5 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 Rate 7.00% 8.00% 9.00% 10.00% 11.00% 12.00% 13.00% 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 NPV and cost of capital 3 2 3 Write conclusion of what you see here: Years 0 1 2 3 4 5 ?? 77 ?? ?? 77 3 Price per unit 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 before tax Tax @ 35% Net Income Plus: Depreciation Purchase of retail kiosk Less: Change in Networking capital Free Cash Flow Cost of capital @ 10% PV of CFS NPV/Sum of all PV) Years 0 1 2 2 3 4 5 ?? T? ?? ?? 22 Price per unit 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 Profis Kitchen and service staff License and rent costs Depreciation Earnings before tax Tax @ 35% Net Income Plus: Depreciation Purchase of retail kiosk Less: Change in Net working capital Free Cash Flow Cost of capital 10% PV of CFS NPV(Sum of all PV) Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started