John Levitt owns a popular burger stand on a trendy section of Melrose Boulevard. Following the success of his first burger stand, "Johnny's," which has been in operations for five years, John is now considering opening a second burger stand in another trendy location, on Sunset Boulevard in the Silver Lake area. John's market research shows that the clientele in both areas is similar: young professionals, typically without children, who like the traditional aspect of eating burgers, but also relish his gourmet, specially manufactured low-fat burgers and the healthy side dishes his stand also sells. John's overall plan is to get the second stand up and running for four years, and then sell both stands off to a new owner and retire to Santa Barbara. John estimates that the cost of starting up a second stand will be as follows: Purchase of retail kiosk (mobile retail food outlet) $900,000 Installation of specialized kitchen equipment $80,000 Furniture and fittings $50,000 John estimates that yearly operating costs of the new location would be identical to those of his current stand: Labor costs, inclusive of all overhead costs: Kitchen and service staff (3 people) 5200,000 License and rent costs $150,000 Raw materials: Burgers (275 per day x 7 days x 52 weeks), see burgers' cost below Drinks S38,400 Other food supplies S145,800 Nonfood supplies $50,200 The revenues at his current location are as follows: Sales of burgers $8 per burger Average daily sales 275 burgers Drinks S190,000 In addition to contributing profits, Jimmy expects that opening a second stand will decrease the cost of purchasing gourmet burgers from 90 cents to 75 cents in both locations. This is due to economies of scale, since the new outlet would double output over the current level of demand. John also expects that he will be able to manage both locations himself, avoiding hiring a second manager for the new location Assume that: Increase in the receivables (AR) is expected to be equal to 12% of gross sales; the project will require additional cash (for giving change to the stand's customers paying cash) in the amount of 5% of gross sales. There will be no considerable investment in inventory as John implements "Just-in-time" inventory system to keep its burgers fresh. Increase in payables associated with the new stand is estimated to be equal to 15% of the cost of raw products Net working capital is fully recovered (i.c., reduced to zero) by the end of year 4 The marginal tax rate is 34 percent. Cost of Capital is 10 percent. Cost of the stand (kiosk) is depreciated over five years according to the straight-line method. The stand is expected to be worth $300,000 after four years of service. 1. Construct a model in Excel to evaluate the project. Use "Home Net spreadsheet as an example (but do not follow it literally as this is a different project which has its own unique features). 2. What is the NPV of this investment? 3. Consider several values of cost of capital (for example, check values between 7% and 13% with 1% step) and compute NPV for each of these values. Use Data Table" Construct NPV profile: Let cost of capital be your X-variable and NPV be your Y-variable. 4. Set some goal value for NPV (choose a value yourself) and use "goal seek to find number of burgers that the new stand must sell annually to achieve the goal. To learn how to use "goal seek please see tutorial video (my own tutorial video is available with the project) BONUS: 5. 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. Using "goal seek find price per burger necessary to achieve this goal John Levitt owns a popular burger stand on a trendy section of Melrose Boulevard. Following the success of his first burger stand, "Johnny's," which has been in operations for five years, John is now considering opening a second burger stand in another trendy location, on Sunset Boulevard in the Silver Lake area. John's market research shows that the clientele in both areas is similar: young professionals, typically without children, who like the traditional aspect of eating burgers, but also relish his gourmet, specially manufactured low-fat burgers and the healthy side dishes his stand also sells. John's overall plan is to get the second stand up and running for four years, and then sell both stands off to a new owner and retire to Santa Barbara. John estimates that the cost of starting up a second stand will be as follows: Purchase of retail kiosk (mobile retail food outlet) $900,000 Installation of specialized kitchen equipment $80,000 Furniture and fittings $50,000 John estimates that yearly operating costs of the new location would be identical to those of his current stand: Labor costs, inclusive of all overhead costs: Kitchen and service staff (3 people) 5200,000 License and rent costs $150,000 Raw materials: Burgers (275 per day x 7 days x 52 weeks), see burgers' cost below Drinks S38,400 Other food supplies S145,800 Nonfood supplies $50,200 The revenues at his current location are as follows: Sales of burgers $8 per burger Average daily sales 275 burgers Drinks S190,000 In addition to contributing profits, Jimmy expects that opening a second stand will decrease the cost of purchasing gourmet burgers from 90 cents to 75 cents in both locations. This is due to economies of scale, since the new outlet would double output over the current level of demand. John also expects that he will be able to manage both locations himself, avoiding hiring a second manager for the new location Assume that: Increase in the receivables (AR) is expected to be equal to 12% of gross sales; the project will require additional cash (for giving change to the stand's customers paying cash) in the amount of 5% of gross sales. There will be no considerable investment in inventory as John implements "Just-in-time" inventory system to keep its burgers fresh. Increase in payables associated with the new stand is estimated to be equal to 15% of the cost of raw products Net working capital is fully recovered (i.c., reduced to zero) by the end of year 4 The marginal tax rate is 34 percent. Cost of Capital is 10 percent. Cost of the stand (kiosk) is depreciated over five years according to the straight-line method. The stand is expected to be worth $300,000 after four years of service. 1. Construct a model in Excel to evaluate the project. Use "Home Net spreadsheet as an example (but do not follow it literally as this is a different project which has its own unique features). 2. What is the NPV of this investment? 3. Consider several values of cost of capital (for example, check values between 7% and 13% with 1% step) and compute NPV for each of these values. Use Data Table" Construct NPV profile: Let cost of capital be your X-variable and NPV be your Y-variable. 4. Set some goal value for NPV (choose a value yourself) and use "goal seek to find number of burgers that the new stand must sell annually to achieve the goal. To learn how to use "goal seek please see tutorial video (my own tutorial video is available with the project) BONUS: 5. 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. Using "goal seek find price per burger necessary to achieve this goal