Question

1 Approved Answer

Posted on Aug 01, 2024

PLEASE SHOW HOW TO SOLVE USING EXCEL, PLEASE INCLUDE ALL FORMULAS USING FOR EXAMPLE: =PV(C42,C41.C40,0,0) B4 fix =C4+1 A B I c D E F

PLEASE SHOW HOW TO SOLVE USING EXCEL, PLEASE INCLUDE ALL FORMULAS USING FOR EXAMPLE: =PV(C42,C41.C40,0,0)

image text in transcribed

B4 fix =C4+1 A B I c D E F G H 1 2 3 4 Value in Millions Balance Sheet For the Years ended 2015 to 2019 and pro forma 2020 2020 2019 2018 2017 2016 2015 6,985 4,185 5,006 4,749 20.225 4,734 4,062 4.400 3,184 16.380 6,036 3,038 3,640 2,630 15.400 3,384 3,090 3,456 2,193 12.723 3,113 Same as previous year 2,931 Avg % of Sales of Prior 5 years 3,384 Avg % of Sales of Prior 5 years 2.268 Same as previous year 11.695 5 Assets 6 Cuma Asas 7 Cash and Equivalents 8 Receivables 9 Inventories 10 Other Current Assets 11 Tota/Cumarts 12 13 Mont-Quent Assers 14 Property. Plant & Equipment (Gross) 15 Accumulated Depreciation 16 Property, Plant & Equipment (Net) 17 Intangibles 18 Other Non-Current Assets 19 Toxa/Mar-CumartAsses 20 Total Assets 21 22 Liabilities & Shareholder's Equity 23 Cum Liabilities 24 Accounts Payable 25 Short-Term Debt 26 Other Current Liabilities 27 Toxa/Current Liables 28 29 Novruzlabs 26,325 11,993 14,332 24,163 2,703 47.1899 62,123 25,304 11196 14,108 23.900 1.925 23,542 10,438 13,104 13,507 1,875 29.485 43,890 23,070 9,721 13,349 13.430 1,831 29.610 41,333 22,821 See ASSUMPTIONS 9,726 Calculate (Hint: See Income) 13,095 8.300 Same as previous year 2,103 Same as previous year 56,313 35,194 3,802 11,441 9,796 25.000 3,617 8,287 10,243 22. NI 2,795 2,172 7,391 2359 2,205 3,731 6,768 2,075 Avg% of Sales of Prior 5 years 2.233 Same as previous year 5,538 Avg % of Sales of Prior 5 years 3,191 2.248 2,808 8.247 30,394 2,847 3,230 7,009 1,396 2 291 10.695 23,054 Calculate See Discretionary Funding") on line 44. Balance using 8,898 the techniquel showed you in dass. 894 Same as previous year 1,845 Same as previous year 8,320 1077 2,088 31.116 24.189 21,483 1,483 4,273 25,251 1,526 3.667 20,726 1,580 2,494 16,762 30 Long-Term Debt 31 Deferred Income Taxes 32 Other Non-Current Liabilities 33 Tota/N-CumartLiabileres 34 Total Liabilities 35 Sharwars Equity 36 Preferred Stock Equity 37 Common Stock Equity 38 Retained Earnings 39 Tota/E 40 Total Liabilities & Equity 41 42 Total Common Shares Outstanding 43 44 Discretionary Funding Needed 45 46 ASSUMPTIONS 47 Net Additions to Plan and Equipment 48 Life of New Equipment inYears 49 New Depreciation (Straight Line) 1634 1726 13,784 7.14 41,333 1,701 Same as previous year 1,559 Same as previous year 10,451 Calculate (Hint: See Income) 62,123 56,313 43,890 35,194 2.564 2.564 2.635 2,659 2,670 2,706 $10,000 10 (--Calculate this A33 fr D F F G H J - K L 1 0 6.00 $ I've already provided the the Old and New situation and calculated the Intial Outlay. What you shoud focus on is that, under the Old Situation, there were no fixed costs and now there is a fixed Labor Expense of $130,000 and Depreciation. REMEMBER: Depreciation is not a cash flow item, however the tax savings from depreciation is a cash flow savings. Now, complete the areas shaded in Purple. B Chicago Turkey Data 2 Old Situation New Situation 3 Turkeys per Year 500,000 500,000 4 Cost per Turkey 7.00 $ 5 Shipping Cost per Turkey 1.00 $ 1.50 6 Selling Price 10.00 $ 10.00 7 Total Variable Cost per Turkey S 8.00 $ 7.50 8 Additional Gross Profit per Turkey $ 0.50 9 Investment in Additional Turkeys $ 15,000.00 10 Land Cost $ 150,000.00 11 Buildings Cost $300,000.00 12 Labor Expense $ $ 130,000.00 13 Depreciation Salvage Value (Buildings) $100,000.00 14 Annual Depreciation Expense $ 40,000.00 15 Actual Salvage Value (Land) $ 200,000.00 16 Actual Salvage Value (Buildings) $100,000.00 17 Life of Project 5 18 Tax Rate 34% 19 Weighted Average Cost of Capital 11% 20 Cash Reinvestment Rate 1.5% 21 Initial Outlay $465,000.00 22 Annual After-tax Cash Flow 23 Terminal Cash Flow 24 25 26 27 Profitability Measures 28 Payback Period 0 29 NPV 0 30 Profitability Index 0 31 IRR 0 32 MIRR Period Cash Flow 0 1 2 3 4 5 B4 fix =C4+1 A B I c D E F G H 1 2 3 4 Value in Millions Balance Sheet For the Years ended 2015 to 2019 and pro forma 2020 2020 2019 2018 2017 2016 2015 6,985 4,185 5,006 4,749 20.225 4,734 4,062 4.400 3,184 16.380 6,036 3,038 3,640 2,630 15.400 3,384 3,090 3,456 2,193 12.723 3,113 Same as previous year 2,931 Avg % of Sales of Prior 5 years 3,384 Avg % of Sales of Prior 5 years 2.268 Same as previous year 11.695 5 Assets 6 Cuma Asas 7 Cash and Equivalents 8 Receivables 9 Inventories 10 Other Current Assets 11 Tota/Cumarts 12 13 Mont-Quent Assers 14 Property. Plant & Equipment (Gross) 15 Accumulated Depreciation 16 Property, Plant & Equipment (Net) 17 Intangibles 18 Other Non-Current Assets 19 Toxa/Mar-CumartAsses 20 Total Assets 21 22 Liabilities & Shareholder's Equity 23 Cum Liabilities 24 Accounts Payable 25 Short-Term Debt 26 Other Current Liabilities 27 Toxa/Current Liables 28 29 Novruzlabs 26,325 11,993 14,332 24,163 2,703 47.1899 62,123 25,304 11196 14,108 23.900 1.925 23,542 10,438 13,104 13,507 1,875 29.485 43,890 23,070 9,721 13,349 13.430 1,831 29.610 41,333 22,821 See ASSUMPTIONS 9,726 Calculate (Hint: See Income) 13,095 8.300 Same as previous year 2,103 Same as previous year 56,313 35,194 3,802 11,441 9,796 25.000 3,617 8,287 10,243 22. NI 2,795 2,172 7,391 2359 2,205 3,731 6,768 2,075 Avg% of Sales of Prior 5 years 2.233 Same as previous year 5,538 Avg % of Sales of Prior 5 years 3,191 2.248 2,808 8.247 30,394 2,847 3,230 7,009 1,396 2 291 10.695 23,054 Calculate See Discretionary Funding") on line 44. Balance using 8,898 the techniquel showed you in dass. 894 Same as previous year 1,845 Same as previous year 8,320 1077 2,088 31.116 24.189 21,483 1,483 4,273 25,251 1,526 3.667 20,726 1,580 2,494 16,762 30 Long-Term Debt 31 Deferred Income Taxes 32 Other Non-Current Liabilities 33 Tota/N-CumartLiabileres 34 Total Liabilities 35 Sharwars Equity 36 Preferred Stock Equity 37 Common Stock Equity 38 Retained Earnings 39 Tota/E 40 Total Liabilities & Equity 41 42 Total Common Shares Outstanding 43 44 Discretionary Funding Needed 45 46 ASSUMPTIONS 47 Net Additions to Plan and Equipment 48 Life of New Equipment inYears 49 New Depreciation (Straight Line) 1634 1726 13,784 7.14 41,333 1,701 Same as previous year 1,559 Same as previous year 10,451 Calculate (Hint: See Income) 62,123 56,313 43,890 35,194 2.564 2.564 2.635 2,659 2,670 2,706 $10,000 10 (--Calculate this A33 fr D F F G H J - K L 1 0 6.00 $ I've already provided the the Old and New situation and calculated the Intial Outlay. What you shoud focus on is that, under the Old Situation, there were no fixed costs and now there is a fixed Labor Expense of $130,000 and Depreciation. REMEMBER: Depreciation is not a cash flow item, however the tax savings from depreciation is a cash flow savings. Now, complete the areas shaded in Purple. B Chicago Turkey Data 2 Old Situation New Situation 3 Turkeys per Year 500,000 500,000 4 Cost per Turkey 7.00 $ 5 Shipping Cost per Turkey 1.00 $ 1.50 6 Selling Price 10.00 $ 10.00 7 Total Variable Cost per Turkey S 8.00 $ 7.50 8 Additional Gross Profit per Turkey $ 0.50 9 Investment in Additional Turkeys $ 15,000.00 10 Land Cost $ 150,000.00 11 Buildings Cost $300,000.00 12 Labor Expense $ $ 130,000.00 13 Depreciation Salvage Value (Buildings) $100,000.00 14 Annual Depreciation Expense $ 40,000.00 15 Actual Salvage Value (Land) $ 200,000.00 16 Actual Salvage Value (Buildings) $100,000.00 17 Life of Project 5 18 Tax Rate 34% 19 Weighted Average Cost of Capital 11% 20 Cash Reinvestment Rate 1.5% 21 Initial Outlay $465,000.00 22 Annual After-tax Cash Flow 23 Terminal Cash Flow 24 25 26 27 Profitability Measures 28 Payback Period 0 29 NPV 0 30 Profitability Index 0 31 IRR 0 32 MIRR Period Cash Flow 0 1 2 3 4 5