PLEASE SHOW HOW TO SOLVE USING EXCEL, FOR EXAMPLE: =PV(C42,C41.C40,0,0)
I DONT NEED IT SOLVED BY HAND ! I NEED HELP FIGURING HOW TO SOLVE USING EXCEL.
THANK YOU !
PICTURE 1: DATA
PICTURE 2: DATA
PICTURE 3: NEEDS TO BE SOLVED
B44 fx A B C D E F G Balance Sheet For the Years ended 2015 to 2019 and pro forma 2020 Value in Millions 2020 2019 2018 2017 2016 2015 Assets Current Assets Cash and Equivalents 6.985 6,985 4.734 6,096 3.984 3,113 8 Receivables 4.559 4,185 4,062 3,038 3,090 2,931 9 Inventories 5.454 5,006 4,400 3,640 3,456 3,384 10 Other Current Assets 4.749 4,749 3.184 2.630 2.193 2,268 11 Total Current Assets 21,747 20,925 16,380 15,404 12,723 11,696 12 13 Non-Current Assets 14 Property, Plant & Equipment (Gross) 27,325 26,325 25,304 23,542 23,070 22,82 15 Accumulated Depreciation (14.889) 11,993 11,196 10,438 9.721 9,726 16 Property, Plant & Equipment (Net) 12,436 14,332 14,108 13, 104 13,349 13,095 17 Intangibles 24,163 24,163 23,900 13,507 13,430 8,300 18 Other Non-Current Assets 2.703 2.703 1.925 1.875 1.831 2,103 19 Total Non-Current Assets 39,302 41,198 39,933 28,486 28,610 23,498 20 Total Assets 61.049 62,123 56,313 43,890 41,333 35,194 21 22 Liabilities & Shareholder's Equity 23 Current Liabilities 24 Accounts Payable 4.142 3,802 3,617 2,795 2.205 2,075 25 Short-Term Debt 11.441 11,441 8.287 2,172 3,731 2,233 26 Other Current Liabilities 10.672 9,796 10.243 7,391 6.768 5,538 27 Total Current Liabilities 26,255 25,039 22,147 12,358 12,704 9,846 28 29 Non-Current Liabilities 30 Long-Term Debt 3,191 7,009 8,320 8,898 31 Deferred Income Taxes 2.847 2,847 2.248 1,396 1,077 894 32 Other Non-Current Liabilities 3.230 3,230 2.808 33 Total Non-Current Liabilities 2,291 2,088 1,845 6,077 6,077 8.247 10,696 11,485 11,637 34 Total Liabilities 32,332 31,116 30,394 23,054 24.189 21,483 35 Shareholder's Equity 36 Preferred Stock Equity 1.483 1,483 1,526 1,580 1.634 1,701 37 Common Stock Equity 4.273 4,273 3,667 2,494 1,726 1,559 38 Retained Earnings 30.524 25,251 20,726 16,762 13,784 10,451 39 Total Equity 36.280 31,007 25,919 20,836 17.144 13,711 40 Total Liabilities & Equity 68,612 62,123 56,313 43,890 41,333 35,194 41 42 Total Common Shares Outstanding 2,564 2,564 2,635 2,659 2,670 2,706A33 X v fx A B C D E F G H J K L Chicago Turkey Data IN Old Situation New Situation 3 Turkeys per Year 500,000 500,000 0 4 Cost per Turkey 7.00 6.00 5 Shipping Cost per Turkey 1.00 1.50 6 Selling Price 10.00 10.00 Total Variable Cost per Turkey I've already provided the the Old and New situation and calculated the Intial Outlay. What you 8.00 7.50 shoud focus on is that, under the Old Situation, there were no fixed costs and now there is a 8 Additional Gross Profit per Turkey 0.50 9 Investment in Additional Turkeys $ 15,000.00 fixed Labor Expense of $130,000 and Depreciation. REMEMBER: Depreciation is not a cash flow 10 Land Cost $ 150,000.00 item, however the tax savings from depreciation is a cash flow savings. 11 Buildings Cost $ 300,000.00 12 Labor Expense $ $ 130,000.00 Now, complete the areas shaded in Purple. 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% Period Cash Flow 21 Initial Outlay $465,000.00 22 Annual After-tax Cash Flow 23 Terminal Cash Flow oo 24 25 26 27 Profitability Measures 28 Payback Period 29 NPV 30 Profitability Index 31 IRR 32 MIRRD33 X V fx A B C D E F G 2 Income Statement 3 For the Years ended 2015 to 2019 and pro forma 2020 Dollars in Thousands 2020 2019 2018 2017 2016 2015 5 Sales 59.841 56,745 $ 51,407 $ 43,396 $ 42,401 $ 39,244 Cost of Sales 28,237 25,920 23,343 20,438 20,005 19.831 7 Gross Operating Profit 31,604 30,825 28,064 22,958 22,396 19,413 8 Selling. General & Administration Expenses 19.620 18,010 16,504 13,383 12,571 12,406 Depreciation & Amortization 2,895 1.895 1,733 1,703 1,693 2.271 10 EBIT 9,089 10,920 9,827 7,872 8,132 4,736 11 Other Income, Net 346 346 152 238 308 674 12 Interest expense 834 834 629 561 603 794 13 Pre-tax Income 8,601 10,432 9,350 7,549 7,837 4,616 14 Income Taxes 2.580 3,130 2.805 2,265 2,351 15 Total Net Income 1,385 $ 6,020 $ 7,302 $ 6,545 $ 5,284 $ 5,486 $ 3,231 16 17 ASSUMPTIONS 18 Tax Rate 30%
