help please! i have the depreciating schedule as well
i have the template for the answers
. All of the following questions relate to a new hog investment. Questions 1 and 2 consider the investment with and without financing. Question 3 looks at sensitivity analysis and question 4 asks for your evaluation. Question 5 adds in the effects of expectations on growth of revenue and costs. Note; the example in the book uses depreciation rates different from IRS tables. Use the MACRS 150% depreciation rates from the other file. (Pg 85 in 6th edition, pg 29 in seventh) 1. A new hog investment requires an initial outlay of $100,000. There will be 236 sows. The returns per sow each year are $650 and the operating costs are $575 per sow. There is no salvage value (terminal value is $0), a tax rate of 30 percent, and a 10% discount rate. The $100,000 initial investment includes a building and equipment costing $90,000 (7 year life) and livestock costing $10,000 (3 year life). (note: depreciation table is on page 85 of your text). Find the NPV, IRR, MIRR and payback for this investment assuming that nothing is financed. Assume that all cash flows occur at the end of the year. Note: this one assumes that there are 0% growth on both receipts and expenses. It also assumes no financing. Later questions will add that in. Since there is no financing it should not matter what the finance rate is. You can put in 8% which will be used in the next question. 2. Now use the above information but assume that 80% is financed on a 10 year loan with equal payments at an interest rate of 8%. Find NPV, IRR, MIRR, and payback for this investment. 3. Conduct a sensitivity analysis of the NPV using different discount rates of 6%, 8%, 10%, 12%, and 14%. Give me the NPVs for each the scenario in part 1 and part 2. If you have the spreadsheet set up in excel you should just be able to change the discount rate and write down the NPV. 4. Tell me whether you would undertake the investment or not. With either decision tell me why you chose what you did, what decision criteria you used (e.g. NPV, IRR, etc) and why you used them. Also tell me if there is any other information that you would want to know before making the decision. 5. Now redo both scenarios assuming that the returns and operating expenses are expected to grow at 2%. Give me the IRR, MIRR, NPV, and payback.
Module 10 Template (1) (Protected View] - Excel Jessica Sharp Ga File Home Insert Page Layout Formulas Data Review View Developer Help Tell me what you want to do Share G30 fx M N 0 O P R S T U B 1 Chapter 10 hog examples 2 3 Numbers to Input 4 Analysis Numbers 3 D E E F G 1 J K 1 Formulas used Pmtrate periodis, PV) Units per year ppent (rate perioduper pv.lv) Sanit-retum ipnotcrate.period,oper . pw.f) Sunit-cost TRR(range of cash flows) Payment NPVirale, Tage of cash flows) MIRR(range of cash flows, finance rate, reitvestrate) Net present value Depreciable Assets IRR Ruildings 7 year MIRR Equipament payback Livestock 3 year Depreciation schedule 1 2 3 5 6 7 8 0.333333 0.444444 0.1481481 0.0740741 0.142857 0.244898 0.1749271 0.1249479 0.089249 0.089249 0.08925 0.04462 1 2 4 5 6 7 8 9 9 10 7 Terminal 8 Growth 9 receipts 10 Expenses 11 Tax rate 12 % financed 13 Finance rate 14 Discount rate 15 Yrs financed 16 17 18 Year 0 19 20 Receipts 21 Terminal value 22 Cash inflow 23 24 Down 25 Operating expenses 26 Depreciation 27 Interest 28 Principal 29 Taxable income 30 Income taxes 31 Cash outflow 32 Net cash flow 33 34 cumm cf 23 ... ch 10 example Ready ED 10] 7096 H Type here to search O 3' 1 e 100% 3:14 PM 11/1/2021 Depreciation Sch, homework 10 (1) - Excel Jessica Sharp Ga File Home Insert Page Layout Formulas Data Review View Developer Help Tell me what you want to do Share X = Calibri - 11 a Wrap Text General ST EN Ar o Insert EX Delete Ed Format - A A A- Paste Launch BIU H Merge & Center $ % +000 .00 7.0 Conditional Format as Cell Formatting Table Styles Styles Sort & Find & Filter Select v Editing Clipboard Font Alignment Number Cells Stock Connector E8 - f 8.74 A H. 1 L M N O P O R 5 T T U U v w 3 yrat 1 Z 3 4 4 6 7 1 1 2. 3 4 5 6 / 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 8 9 10 11 12 13 14 15 16 17 18 19 20 21 B C D 6 Annual Depreciation Rales Under MACRS I Percentage lales) 5 year 7 year 10 year 15 Year 20 Year 25 15 10.71 7.5 5 3.75 37.5 25.5 19.13 13.08 9.5 7.22 25 17.95 15.03 11.79 3.55 6.68 12 16.66 12.25 10.02 7.7 6.18 16.66 12.25 8.74 5.93 5./1 B.23 12.25 8.74 6.23 5.28 12.25 8.74 5.9 4.99 6.13 8.74 5.9 4.52 8.74 5.9 4.46 8.74 5.9 4.46 4.37 5.9 4.46 5.9 4.46 5.9 4.46 5.9 4.46 5.9 4.46 2.95 4.46 4.46 4.46 4.46 4.46 2.23 Sheet1 Sheet2 Sheet3 Ready to al 79% H Type here to search o Et 3' 100% 3:10 PM 11/1/2021 3. Module 10 Template (1) (Protected View] - Excel Jessica Sharp Ga File Home Insert Page Layout Formulas Data Review View Developer Help Tell me what you want to do Share G30 fx M N 0 O P R S T U B 1 Chapter 10 hog examples 2 3 Numbers to Input 4 Analysis Numbers 3 D E E F G 1 J K 1 Formulas used Pmtrate periodis, PV) Units per year ppent (rate perioduper pv.lv) Sanit-retum ipnotcrate.period,oper . pw.f) Sunit-cost TRR(range of cash flows) Payment NPVirale, Tage of cash flows) MIRR(range of cash flows, finance rate, reitvestrate) Net present value Depreciable Assets IRR Ruildings 7 year MIRR Equipament payback Livestock 3 year Depreciation schedule 1 2 3 5 6 7 8 0.333333 0.444444 0.1481481 0.0740741 0.142857 0.244898 0.1749271 0.1249479 0.089249 0.089249 0.08925 0.04462 1 2 4 5 6 7 8 9 9 10 7 Terminal 8 Growth 9 receipts 10 Expenses 11 Tax rate 12 % financed 13 Finance rate 14 Discount rate 15 Yrs financed 16 17 18 Year 0 19 20 Receipts 21 Terminal value 22 Cash inflow 23 24 Down 25 Operating expenses 26 Depreciation 27 Interest 28 Principal 29 Taxable income 30 Income taxes 31 Cash outflow 32 Net cash flow 33 34 cumm cf 23 ... ch 10 example Ready ED 10] 7096 H Type here to search O 3' 1 e 100% 3:14 PM 11/1/2021 Depreciation Sch, homework 10 (1) - Excel Jessica Sharp Ga File Home Insert Page Layout Formulas Data Review View Developer Help Tell me what you want to do Share X = Calibri - 11 a Wrap Text General ST EN Ar o Insert EX Delete Ed Format - A A A- Paste Launch BIU H Merge & Center $ % +000 .00 7.0 Conditional Format as Cell Formatting Table Styles Styles Sort & Find & Filter Select v Editing Clipboard Font Alignment Number Cells Stock Connector E8 - f 8.74 A H. 1 L M N O P O R 5 T T U U v w 3 yrat 1 Z 3 4 4 6 7 1 1 2. 3 4 5 6 / 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 8 9 10 11 12 13 14 15 16 17 18 19 20 21 B C D 6 Annual Depreciation Rales Under MACRS I Percentage lales) 5 year 7 year 10 year 15 Year 20 Year 25 15 10.71 7.5 5 3.75 37.5 25.5 19.13 13.08 9.5 7.22 25 17.95 15.03 11.79 3.55 6.68 12 16.66 12.25 10.02 7.7 6.18 16.66 12.25 8.74 5.93 5./1 B.23 12.25 8.74 6.23 5.28 12.25 8.74 5.9 4.99 6.13 8.74 5.9 4.52 8.74 5.9 4.46 8.74 5.9 4.46 4.37 5.9 4.46 5.9 4.46 5.9 4.46 5.9 4.46 5.9 4.46 2.95 4.46 4.46 4.46 4.46 4.46 2.23 Sheet1 Sheet2 Sheet3 Ready to al 79% H Type here to search o Et 3' 100% 3:10 PM 11/1/2021 3
