Project 2- Capital Budgeting . . . 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. 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 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- $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-S155,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 - . Print Layout View Sec 1 Pages: 1 of 2 Words: 43 of 433 www.wibun bu www.sw. vy................ 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 use Excel file to find number of hotdogs (quantity) that the new stand must sell annually to achieve the goal. 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. Find price per hotdog necessary to achieve this goal. 0.00 H 1 Years 2. Price per unit No of units 4 Total Sale of Burgers 5 Drinks Other food sales 7 Total Sales (Gross sales 1.00 5.00 109500.00 547500.00 1000000.00 155000.00 D 2.00 750 131600.00 985500.00 100000.00 255000.00 3.00 1125 157680.00 1773900.00 100000.00 155000.00 4.00 16.88 18921600 3193020.00 100000.00 155000.00 G 5.00 25.31 227059,20 5747436.00 100000.00 155000.00 B02500.00 1240500.00 2028900,00 344802000 5002435.00 2.66 2.00 219000.00 38400.00 58900.00 50200.00 220 29080.00 38400.00 58900.00 50200.00 2.42 381585.60 38400.00 58900.00 50200.00 503692.99 38400,00 58900.00 50200.00 2.93 664874.75 38400,00 58.900.00 50200.00 166500.00 436580.00 529085.60 651192.92 812374.25 10 Cost of Raw materials: 11 Burgers 12 Total Raw material for the burgers 13 Drinks 14 Other food supplies 15 Nonfood supplies 15 27 Total cost of toods sold IR 10 Gross Profit 20 21 kitchen and service stall 22 License and rent costs 21 Depreciation 24 Coming before tax 25 26 Tax 35% - 2 Net Income 436000.00 B01920.00 1499814.40 2796827.01 5190061.25 200000.00 150000.00 62000.00 24000.00 200000.00 150000.00 62000.00 391920.00 200000.00 150000.00 62000.00 108781440 200000.00 150000.00 62000.00 238482701 200000.00 150000.00 62000.00 4778061 25 M40000 137172.00 380735.04 83468945 167232144 15600.00 254748.00 707079.36 155013256 3105739.31 H 150000.00 62000.00 24000.00 D 150000.00 62000.00 391920.00 150000.00 62000.00 1087814.40 150000.00 62000.00 2384827.03 150000.00 62000.00 4778061.25 8400.00 137172.00 380735 04 834689.45 1672321.44 1560000 254748.00 707079,36 155013756 3105739,81 22 Ucense and rent costs 23 Depreciation 24 Earnings before tax 25 26 Tax 35% 27 26 Net Income 29 30 Plus: Deprecation 1 Purchase of retail Niosk 12 3 Free Cash Flow 14 15 Cost of capital 10% 16 PV of CFS 37 NPVISum of all PV) 18 1.00 0.91 033 0.75 0.66 0.62 NPV Year 4 Year 5 1 2 3 4 5 NPV at Cost of Capital Tryout % Year 1 Year 2 Year 3 $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ Rate 7.00% $ 8.00%S 9.00% $ 10.00% $ 11.00% $ 12.00% $ 13.00% $ luu 6 $ $ $ $ $ $ $ $ $ $ $ $ $ $ 7 8 NPV and Cost of capital $100 $0.90 SO 50.30 50.60 SO 50 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 $0.40 NMNO 50.30 5020 30:30 5 DOM 11 2.00 400 SOON NOW 100% 12.00 EBON Write conclusion of what you see here: 2.2 Model & Calculation of NPV 3 NPV at various values 4 Goal Seek-quantity 5 Goal seek - price H 25 26 27 Write conclusion of what you see here: 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 SO 51 52 53 + + 1.2 Model & Calculation of NPV 3 NPV at various values 4 Goal seek-quantity 5 Goal seek-price H 1000000 Insert a positive NPV amount here Year 0 1 2 3 4 5 Desired NPV 2 1 4 Price per unit No of units Total sale of Burgers 7. Drinks Other food sales 72 ?? 22 72 ?? 10 Total Sales 12 cost of raw materials 11 Burgers 14 Drinks 15 Other food supplies 16 Nonfood supplies in Total Cost of goods sold 10 20 Gross Profit 22 Kitchen and service stat 23 License and rent costs 240 Deprecation 25 Canning before tas 26 > Tax 35% 21 20 Net Income MORGAN IN PA 20 Gross Profit 21 22 Kitchen and service staff 21 Ucense and rent costs 24 Depreciation 25 Carines before tax 26 22. Tax 35% 28 29 Net Income 30 11 Pus: Depreciation 32 Purchase of retait kiosk 33 34 Free Cash Flow 35 36 Cost of capital 10% 37 PV of CF 38 NPVISum of all PV) 40 41 42 45 46 47 I MOGLA Come Nova S Goals per G $300,000 Desired NPV Years 0 1 2 3 4 5 1 Desired NPV 2 2 Price per unit 5 No of units 5 Total Sale of Burgers 7 Drinks Other food sales 72 72 22 22 22 10 Total Sales 31 12 Cost of Raw material D Burgers 14 Drinks 15 Other food supplies 16 Nonfood supplies 12 18 Total Cost of toods sold 19 20 Gross Profit 21 22 Otchen and service stall 23 License and rent costs 24 Depreciation 25 Carines before tak 26 27 Tax15 21 7 Net Income na NTV INVM C50 22 Kitchen and service statt 21 Ucense and rent costs 24 Depreciation 25 Earnings before tax 26 27 Tax 35 23 29 Net Income 30 * Plus Depreciation 22Purchase of retail kiosk 11 1 Free Cash Flow 30 16 Cost of capital 10% 37 P of C 38 NPV Sum of all 10 40 Ofference 4 44 16 41 49 50 MAL SNV Project 2- Capital Budgeting . . . 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. 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 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- $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-S155,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 - . Print Layout View Sec 1 Pages: 1 of 2 Words: 43 of 433 www.wibun bu www.sw. vy................ 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 use Excel file to find number of hotdogs (quantity) that the new stand must sell annually to achieve the goal. 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. Find price per hotdog necessary to achieve this goal. 0.00 H 1 Years 2. Price per unit No of units 4 Total Sale of Burgers 5 Drinks Other food sales 7 Total Sales (Gross sales 1.00 5.00 109500.00 547500.00 1000000.00 155000.00 D 2.00 750 131600.00 985500.00 100000.00 255000.00 3.00 1125 157680.00 1773900.00 100000.00 155000.00 4.00 16.88 18921600 3193020.00 100000.00 155000.00 G 5.00 25.31 227059,20 5747436.00 100000.00 155000.00 B02500.00 1240500.00 2028900,00 344802000 5002435.00 2.66 2.00 219000.00 38400.00 58900.00 50200.00 220 29080.00 38400.00 58900.00 50200.00 2.42 381585.60 38400.00 58900.00 50200.00 503692.99 38400,00 58900.00 50200.00 2.93 664874.75 38400,00 58.900.00 50200.00 166500.00 436580.00 529085.60 651192.92 812374.25 10 Cost of Raw materials: 11 Burgers 12 Total Raw material for the burgers 13 Drinks 14 Other food supplies 15 Nonfood supplies 15 27 Total cost of toods sold IR 10 Gross Profit 20 21 kitchen and service stall 22 License and rent costs 21 Depreciation 24 Coming before tax 25 26 Tax 35% - 2 Net Income 436000.00 B01920.00 1499814.40 2796827.01 5190061.25 200000.00 150000.00 62000.00 24000.00 200000.00 150000.00 62000.00 391920.00 200000.00 150000.00 62000.00 108781440 200000.00 150000.00 62000.00 238482701 200000.00 150000.00 62000.00 4778061 25 M40000 137172.00 380735.04 83468945 167232144 15600.00 254748.00 707079.36 155013256 3105739.31 H 150000.00 62000.00 24000.00 D 150000.00 62000.00 391920.00 150000.00 62000.00 1087814.40 150000.00 62000.00 2384827.03 150000.00 62000.00 4778061.25 8400.00 137172.00 380735 04 834689.45 1672321.44 1560000 254748.00 707079,36 155013756 3105739,81 22 Ucense and rent costs 23 Depreciation 24 Earnings before tax 25 26 Tax 35% 27 26 Net Income 29 30 Plus: Deprecation 1 Purchase of retail Niosk 12 3 Free Cash Flow 14 15 Cost of capital 10% 16 PV of CFS 37 NPVISum of all PV) 18 1.00 0.91 033 0.75 0.66 0.62 NPV Year 4 Year 5 1 2 3 4 5 NPV at Cost of Capital Tryout % Year 1 Year 2 Year 3 $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ Rate 7.00% $ 8.00%S 9.00% $ 10.00% $ 11.00% $ 12.00% $ 13.00% $ luu 6 $ $ $ $ $ $ $ $ $ $ $ $ $ $ 7 8 NPV and Cost of capital $100 $0.90 SO 50.30 50.60 SO 50 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 $0.40 NMNO 50.30 5020 30:30 5 DOM 11 2.00 400 SOON NOW 100% 12.00 EBON Write conclusion of what you see here: 2.2 Model & Calculation of NPV 3 NPV at various values 4 Goal Seek-quantity 5 Goal seek - price H 25 26 27 Write conclusion of what you see here: 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 SO 51 52 53 + + 1.2 Model & Calculation of NPV 3 NPV at various values 4 Goal seek-quantity 5 Goal seek-price H 1000000 Insert a positive NPV amount here Year 0 1 2 3 4 5 Desired NPV 2 1 4 Price per unit No of units Total sale of Burgers 7. Drinks Other food sales 72 ?? 22 72 ?? 10 Total Sales 12 cost of raw materials 11 Burgers 14 Drinks 15 Other food supplies 16 Nonfood supplies in Total Cost of goods sold 10 20 Gross Profit 22 Kitchen and service stat 23 License and rent costs 240 Deprecation 25 Canning before tas 26 > Tax 35% 21 20 Net Income MORGAN IN PA 20 Gross Profit 21 22 Kitchen and service staff 21 Ucense and rent costs 24 Depreciation 25 Carines before tax 26 22. Tax 35% 28 29 Net Income 30 11 Pus: Depreciation 32 Purchase of retait kiosk 33 34 Free Cash Flow 35 36 Cost of capital 10% 37 PV of CF 38 NPVISum of all PV) 40 41 42 45 46 47 I MOGLA Come Nova S Goals per G $300,000 Desired NPV Years 0 1 2 3 4 5 1 Desired NPV 2 2 Price per unit 5 No of units 5 Total Sale of Burgers 7 Drinks Other food sales 72 72 22 22 22 10 Total Sales 31 12 Cost of Raw material D Burgers 14 Drinks 15 Other food supplies 16 Nonfood supplies 12 18 Total Cost of toods sold 19 20 Gross Profit 21 22 Otchen and service stall 23 License and rent costs 24 Depreciation 25 Carines before tak 26 27 Tax15 21 7 Net Income na NTV INVM C50 22 Kitchen and service statt 21 Ucense and rent costs 24 Depreciation 25 Earnings before tax 26 27 Tax 35 23 29 Net Income 30 * Plus Depreciation 22Purchase of retail kiosk 11 1 Free Cash Flow 30 16 Cost of capital 10% 37 P of C 38 NPV Sum of all 10 40 Ofference 4 44 16 41 49 50 MAL SNV