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. 1. 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 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 kitehen equipment $50,000 - Installation of the kitchen equipment $10,000 - Fumiture and fittings $50,000 4. Otber information: - Marginal tax rate is 35\% - Cost of Capital is 10% Furmiture 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 fumiture and kitchen equipment) is expected to be worth $300,000 after five years of service. Note: The definition of an asse's cost is all coss that are necessary to get an asset in place and ready for tuse. 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 cast of capital from 7% to 13%. Looking at the chart, insert your observations and conelusion on page 2 . Additional tutorial videos: NPV, IRR using Excel videosic hisp://www youtube com/watch? = NONmVhVtP3g HINS: 1. For worksheet one (Model and calculation of NPV) 1. Depreciation starts from year t1 and ends at t 5, 2. There is no operating and depeeciation 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 ia the sam of all the prescnt values of cash fitws for the year 1 to 5 . II. Fer workstiect twe ("Year" values at varieus cost ef capital) PV ( for year n)= Free Cash Flow (1+r)n nhere. N= correspending year (n1,n=2 and is en) Vree cash flow = The free cash flow from the first warksheet for the cerrevpending year If - cout of capaital gives on the same row for each year in the second workaheet 11. 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 a)= 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 workaheet for the correspending year R - cost of capital given on the same row for each year in the second worksheet Thes calculate the NPV. Formula for the NPV = - Cash ouiflow(for year a) +PV( for year n) (Nate: The cash audfow is only for year one at differemt cast of capital, nat for ather yeary) Seroll down: In the highilighted area previde a cenclusion of what you see is bappening in the srapa in just three or fewer lines. : Open recovered workbooks? Your recent changes were saved. Do you want to continue working where you left off? C36 $fx=+c33c35