Need solutions to highlighted yellow spaces with formulas preferably, thanks!

A B D E F REAL FARM Real Farm Income Statement (in millions of dollars) 2015 2016 2017 2018 1,593 682 478 261 0 Total Revenue Material, Labor, and Other Pruduction Costs Selling, Distribution, and Marketing Expenses Administrative and General Expenses 1 Goodwill and Other Intangible Asset Impairmen 2 Other Operating Expenses 3 Operating Income 7 Net Interest Expense 5 Income Before Income Tax Expense 5 Income Tax Expense 7 Net Income 3 Earnings Per Share (Basic) Dividends per Share 1 2 1,691 810 619 226 290 1 (253) 22 (275) (47) (228) 1,636 713 508 276 0 0 139 19 120 39 81 3 175 13 161 69 92 1,677 743 526 258 0 (6) 157 14 129 50 79 (4.89) 0.60 2.07 0.36 2.18 0.56 2.06 0.60 B 5 Income Statement Balance Sheet Comparable Firms Forecast Assumptions Capital Market Data B D E F - Exhibit 3 REAL FARM Real Farm Balance Sheet (in millions of dollars) 2016 2017 2018 138 136 164 148 94 679 216 120 180 128 72 716 172 130 190 131 54 677 850 1,529 832 1,547 859 1,536 3 Cash and Cash Equivalents Trade Accounts Receivable 0 Inventories 1 Prepaid Expenses 2 Other Current Assets 3 Total Current Assets 4 5 Net Property, Plant, and Equipment and Other Assets 6 Total Assets 7 8 Debt Due within One Year 9 Accounts Payable o Other Current Liabilities 1 Current Liabilities 2 3 Long-Term Debt 4 Other Liabilities 5 Shareholders' Equity 6 Total Liabilities and Shareholders' Equity 7 3 0 0 1 95 272 369 87 245 332 87 255 343 329 196 233 219 763 1,547 235 206 752 1,536 636 1,529 1 2 3 4 5 Income Statement Balance Sheet Comparable Firms Forecast Assumptions REAL FARM Comparable Firms, End of 2018 (in millions of dollars except share price) Shares Share Price Outstanding Total Cash Total Debt Enterprise Value EBITDA Multiple Revenue EBITDA ROA ROE Beta Bond Rating 86 7% 38.3 8.2 714 568 182 4% 1,677 984 1,050 453 3.5 11.7 5.6 204 48 122 30 12.51 56.80 48.28 19.92 22.76 69.34 235 101 197 0 742 11% 9% 10% BB+ B BB 10.2 9.7 7 10 Real Fam Baylor Consolidated Markets Country Wide IdeaMart Cheshire Goods Maxi Market City's Best 5% 4% 31 359 1.63 1.60 1,45 1.36 1.85 0.42 1.75 1.04 B 692 194 1,901 1,890 1,712 1,145 50.9 27.3 44.8 31.1 162 26 1,420 1,090 1,350 1,950 0 250 215 6.5 5.3 12.2 7.1 6.0 156 240 2% 55% 19% 15% 8% 13% 14% 7% 32.65 29.97 BB 114 189 BB- * EBITDA miltiple - Enterprise Value divided by EBITDA Income Statement Balance Sheet Comparable Firms Forecast Assumptions Capital Market Data Question 2 Question 3 Question 4 + B D E F G H 1 J K REAL FARM Real Farm Operating Performance and Financial Forecast Assumptions Operating Performance Revenue Growth Operating Margin 2012 0% 8% 2013 -7% 2% 2014 -1% 5% 2015 -5% -1% 2016 -3% 6% 2017 -2% 9% 2018 7% 9% Actual 2018 *************** Forecast **** 2019 2020 2021 2022 0 1 Forecast Assumptions 2 3 4 Expansion Scenario 5 Revenue Growth 6 Operating Margin 7 Net Working Capital Turnover 8 Fixed Asset Turnover 5.3% 9.4% 5.02 1.95 1.0% 9.0% 6.00 1.95 1.5% 9.0% 6.50 1.95 2.0% 9.0% 7.00 1.95 2.5% 9.0% 7.50 1.95 0.0% 5.5% 7.50 1.95 o Recession Scenario 1 Revenue Growth 5.3% -1.0% -1.0% -0.5% 2 Operating Margin 9.4% 8.0% 7.0% 6.0% 3 Net Working Capital Turnover 5.02 6.00 6.50 7.00 4 Fixed Asset Turnover 1.95 1.95 1.95 1.95 5 6 The ratios are defined as follows: 7 Revenue Growth = annual percentage change in total revenue 3 Operating Margin = operating income divided by total revenue 9 Net Working Capital Turnover = total revenue divided by net working capital Net working capital = current assets less current liabilities 1 Fixed Assets = net PP&E and other assets item in balance sheet 2 Fixed Asset Turnover = total revenue divided by net PP&E and other assets 3 4 5 7 8 Income Statement Balance Sheet Comparable Firms Forecast Assumptions Capital Market Data B D E F G H 1 2 3 4 REAL FARM Capital Market Data 5 6 30-Day Treasury Bill 10-Year Treasury Bond Yield 0.1% 2.8% 8 9 10 11 12 13 14 15 16 17 18 19 20 21 10-Year Corporate Bonds of Industrial Companies AAA AA A+ A A- BBB+ BBB BBB- BB+ BB BB- 2.8% 2.9% 3.2% 3.3% 3.5% 3.8% 4.1% 4.6% 5.8% 6.5% 6.5% 6.8% 8.4% 9.0% B+ 22 B B- 23 24 25 Historical Market Risk Premium Equity Market Index Less Government Debt 5.5% 26 27 28 29 30 31 U.S. Real GDP Annual Growth Rate U.S. GDP Annual Deflator Rate Consumer Price Index Annual Rate 5-Year Forecast 3.3% 1.8% 2.2% 32 33 34 35 36 Corporate Tax rate 39.0% Income Statement Balance Sheet Comparable Firms Forecast Assumptions Capital Market Data A B E F G H . 1 1 2 3 REAL FARM Estimate of Cost of Capital for Real Farm 4 5 6 Risk-Free Rate 7 Market Risk Premium 8 Tax Rate 9 Debt/Value Bond Rating Cost of debt Beta Cost of equity WACC 10 11 12 Real Farm 13 Baylor 14 Consolidated Markets 15 Country Wide 16 IdeaMart 17 Cheshire Goods 18 Maxi Market 19 City's Best 20 21 22 23 24 Mean 25 Income Statement Balance Sheet Comparable Firms Forecast Assumptions Capital Market Data Question 2 B D E F G H 1 K L M N 1 REAL FARM Expansion Scenario 2019 1.0% 9.0% 6.00 1.95 2020 1.5% 9.0% 6.50 1.95 2021 2.0% 9.0% % 7.00 1.95 2022 2.5% 9.0% 7.50 1.95 long term 2023 3.0% 9.0% 7.50 1.95 2 3 4 5 2018 6 Revenue Growth 5.3% 7 Operating Margin 9.4% 8 NWC Turnover 5.02 9 FA Turnover 1.95 10 WACC 0.0% 11 Tax Rate 39% 12 13 Revenue 1,677 14 EBIT 157 15 16 NWC 334 17 Fixed Assets 859 18 ROIC 8.0% 19 20 NOPAT 96 21 - change in NWC (49) 22 - change in NFA 27 23 FCF 118 24 Horizon value 25 Present value in 2018 26 PV of all future CFs 27 Debt 28 Equity 29 Shares 30 31 Implied Price 32 33 *ROIC = Return on invested capital 34 35 Income Statement Balance Sheet Comparable Firms Forecast Assumptions Capital Market Data Question 2 Question 3 B C D E F G H J K L M N o REAL FARM Recession Scenario 2022 0.0% 2019 -1.0% 8.0% 6.00 1.95 2020 -1.0% 7.0% 6.50 1.95 2021 -0.5% 6.0% 7.00 1.95 long term 2023 0.0% 5.0% 7.50 1.95 5.5% 7.50 1.95 1 2 3 4 5 2018 6 Revenue Growth 5.3% 7 Operating Margin 9.4% 8 NWC Turnover 5.02 9 FA Turnover 1.95 10 WACC 0.0% 11 Tax Ratc 39% 12 14 13 Revenue 1,677 14 EBIT 157 15 16 NWC 334 17 Fixed Assets 859 18 ROIC 8.0% 19 20 NOPAT 96 21 - change in NWC (49) 22 - change in NFA 27 23 FCF 118 24 Horizon value 25 Present value in 2018 26 PV of all future CFs 27 Debt 28 Equity 29 Shares 30 31 Implied Price 32 33 *ROIC - Return on invested capital 34 35 Income Statement Balance Sheet Comparable Firms Forecast Assumptions Capital Market Data Question 2 Question 3 Question 4 A B D E F REAL FARM Real Farm Income Statement (in millions of dollars) 2015 2016 2017 2018 1,593 682 478 261 0 Total Revenue Material, Labor, and Other Pruduction Costs Selling, Distribution, and Marketing Expenses Administrative and General Expenses 1 Goodwill and Other Intangible Asset Impairmen 2 Other Operating Expenses 3 Operating Income 7 Net Interest Expense 5 Income Before Income Tax Expense 5 Income Tax Expense 7 Net Income 3 Earnings Per Share (Basic) Dividends per Share 1 2 1,691 810 619 226 290 1 (253) 22 (275) (47) (228) 1,636 713 508 276 0 0 139 19 120 39 81 3 175 13 161 69 92 1,677 743 526 258 0 (6) 157 14 129 50 79 (4.89) 0.60 2.07 0.36 2.18 0.56 2.06 0.60 B 5 Income Statement Balance Sheet Comparable Firms Forecast Assumptions Capital Market Data B D E F - Exhibit 3 REAL FARM Real Farm Balance Sheet (in millions of dollars) 2016 2017 2018 138 136 164 148 94 679 216 120 180 128 72 716 172 130 190 131 54 677 850 1,529 832 1,547 859 1,536 3 Cash and Cash Equivalents Trade Accounts Receivable 0 Inventories 1 Prepaid Expenses 2 Other Current Assets 3 Total Current Assets 4 5 Net Property, Plant, and Equipment and Other Assets 6 Total Assets 7 8 Debt Due within One Year 9 Accounts Payable o Other Current Liabilities 1 Current Liabilities 2 3 Long-Term Debt 4 Other Liabilities 5 Shareholders' Equity 6 Total Liabilities and Shareholders' Equity 7 3 0 0 1 95 272 369 87 245 332 87 255 343 329 196 233 219 763 1,547 235 206 752 1,536 636 1,529 1 2 3 4 5 Income Statement Balance Sheet Comparable Firms Forecast Assumptions REAL FARM Comparable Firms, End of 2018 (in millions of dollars except share price) Shares Share Price Outstanding Total Cash Total Debt Enterprise Value EBITDA Multiple Revenue EBITDA ROA ROE Beta Bond Rating 86 7% 38.3 8.2 714 568 182 4% 1,677 984 1,050 453 3.5 11.7 5.6 204 48 122 30 12.51 56.80 48.28 19.92 22.76 69.34 235 101 197 0 742 11% 9% 10% BB+ B BB 10.2 9.7 7 10 Real Fam Baylor Consolidated Markets Country Wide IdeaMart Cheshire Goods Maxi Market City's Best 5% 4% 31 359 1.63 1.60 1,45 1.36 1.85 0.42 1.75 1.04 B 692 194 1,901 1,890 1,712 1,145 50.9 27.3 44.8 31.1 162 26 1,420 1,090 1,350 1,950 0 250 215 6.5 5.3 12.2 7.1 6.0 156 240 2% 55% 19% 15% 8% 13% 14% 7% 32.65 29.97 BB 114 189 BB- * EBITDA miltiple - Enterprise Value divided by EBITDA Income Statement Balance Sheet Comparable Firms Forecast Assumptions Capital Market Data Question 2 Question 3 Question 4 + B D E F G H 1 J K REAL FARM Real Farm Operating Performance and Financial Forecast Assumptions Operating Performance Revenue Growth Operating Margin 2012 0% 8% 2013 -7% 2% 2014 -1% 5% 2015 -5% -1% 2016 -3% 6% 2017 -2% 9% 2018 7% 9% Actual 2018 *************** Forecast **** 2019 2020 2021 2022 0 1 Forecast Assumptions 2 3 4 Expansion Scenario 5 Revenue Growth 6 Operating Margin 7 Net Working Capital Turnover 8 Fixed Asset Turnover 5.3% 9.4% 5.02 1.95 1.0% 9.0% 6.00 1.95 1.5% 9.0% 6.50 1.95 2.0% 9.0% 7.00 1.95 2.5% 9.0% 7.50 1.95 0.0% 5.5% 7.50 1.95 o Recession Scenario 1 Revenue Growth 5.3% -1.0% -1.0% -0.5% 2 Operating Margin 9.4% 8.0% 7.0% 6.0% 3 Net Working Capital Turnover 5.02 6.00 6.50 7.00 4 Fixed Asset Turnover 1.95 1.95 1.95 1.95 5 6 The ratios are defined as follows: 7 Revenue Growth = annual percentage change in total revenue 3 Operating Margin = operating income divided by total revenue 9 Net Working Capital Turnover = total revenue divided by net working capital Net working capital = current assets less current liabilities 1 Fixed Assets = net PP&E and other assets item in balance sheet 2 Fixed Asset Turnover = total revenue divided by net PP&E and other assets 3 4 5 7 8 Income Statement Balance Sheet Comparable Firms Forecast Assumptions Capital Market Data B D E F G H 1 2 3 4 REAL FARM Capital Market Data 5 6 30-Day Treasury Bill 10-Year Treasury Bond Yield 0.1% 2.8% 8 9 10 11 12 13 14 15 16 17 18 19 20 21 10-Year Corporate Bonds of Industrial Companies AAA AA A+ A A- BBB+ BBB BBB- BB+ BB BB- 2.8% 2.9% 3.2% 3.3% 3.5% 3.8% 4.1% 4.6% 5.8% 6.5% 6.5% 6.8% 8.4% 9.0% B+ 22 B B- 23 24 25 Historical Market Risk Premium Equity Market Index Less Government Debt 5.5% 26 27 28 29 30 31 U.S. Real GDP Annual Growth Rate U.S. GDP Annual Deflator Rate Consumer Price Index Annual Rate 5-Year Forecast 3.3% 1.8% 2.2% 32 33 34 35 36 Corporate Tax rate 39.0% Income Statement Balance Sheet Comparable Firms Forecast Assumptions Capital Market Data A B E F G H . 1 1 2 3 REAL FARM Estimate of Cost of Capital for Real Farm 4 5 6 Risk-Free Rate 7 Market Risk Premium 8 Tax Rate 9 Debt/Value Bond Rating Cost of debt Beta Cost of equity WACC 10 11 12 Real Farm 13 Baylor 14 Consolidated Markets 15 Country Wide 16 IdeaMart 17 Cheshire Goods 18 Maxi Market 19 City's Best 20 21 22 23 24 Mean 25 Income Statement Balance Sheet Comparable Firms Forecast Assumptions Capital Market Data Question 2 B D E F G H 1 K L M N 1 REAL FARM Expansion Scenario 2019 1.0% 9.0% 6.00 1.95 2020 1.5% 9.0% 6.50 1.95 2021 2.0% 9.0% % 7.00 1.95 2022 2.5% 9.0% 7.50 1.95 long term 2023 3.0% 9.0% 7.50 1.95 2 3 4 5 2018 6 Revenue Growth 5.3% 7 Operating Margin 9.4% 8 NWC Turnover 5.02 9 FA Turnover 1.95 10 WACC 0.0% 11 Tax Rate 39% 12 13 Revenue 1,677 14 EBIT 157 15 16 NWC 334 17 Fixed Assets 859 18 ROIC 8.0% 19 20 NOPAT 96 21 - change in NWC (49) 22 - change in NFA 27 23 FCF 118 24 Horizon value 25 Present value in 2018 26 PV of all future CFs 27 Debt 28 Equity 29 Shares 30 31 Implied Price 32 33 *ROIC = Return on invested capital 34 35 Income Statement Balance Sheet Comparable Firms Forecast Assumptions Capital Market Data Question 2 Question 3 B C D E F G H J K L M N o REAL FARM Recession Scenario 2022 0.0% 2019 -1.0% 8.0% 6.00 1.95 2020 -1.0% 7.0% 6.50 1.95 2021 -0.5% 6.0% 7.00 1.95 long term 2023 0.0% 5.0% 7.50 1.95 5.5% 7.50 1.95 1 2 3 4 5 2018 6 Revenue Growth 5.3% 7 Operating Margin 9.4% 8 NWC Turnover 5.02 9 FA Turnover 1.95 10 WACC 0.0% 11 Tax Ratc 39% 12 14 13 Revenue 1,677 14 EBIT 157 15 16 NWC 334 17 Fixed Assets 859 18 ROIC 8.0% 19 20 NOPAT 96 21 - change in NWC (49) 22 - change in NFA 27 23 FCF 118 24 Horizon value 25 Present value in 2018 26 PV of all future CFs 27 Debt 28 Equity 29 Shares 30 31 Implied Price 32 33 *ROIC - Return on invested capital 34 35 Income Statement Balance Sheet Comparable Firms Forecast Assumptions Capital Market Data Question 2 Question 3 Question 4