Provide an NPV analysis of the offer to Whistler Blackcomb of $1.4 Billion using the data in the excel sheet provided. Based on this information, should Whistler accept or reject the offer? Hint: complete all the green boxes in the valuation excel sheet provided. Income Statement $000s Revenues Operating Expenses SG&A Depreciation Amortization EBIT Interest Income Before Tax Income Tax Net Income 2015-09-30 2016-09-30 Ratios & Rates ####### ####### ############## 262,254 312,092 140,122 165,593 30,094 36,369 29,659 26,016 0.076 12,509 12,509 12,509 12,509 12,509 12,509 49,870 71,605 (12,509) (12,509) (12,509) (12,509) 20,536 12,362 29,334 59,243 (12,509) (12,509) (12,509) (12,509) 8,049 15,713 21,285 43,530 (12,509) (12,509) (12,509) (12,509) Shares Outstanding 38,042 38,255 DCE Valuation ############################ Free Cash Flow OCF NWC Change in NWC CAPEX FCF PVIF $0.94 $0.89 $0.84 $0.79 0.025 PV of FCF in 2017-2020 Terminal Growth Rate Terminal Value in 2020 PV of Terminal Value D = E = Price = 257,465 (257,465) ($6.73) Balance Sheet 2020-09-30 $000s Cash Accounts Receivable Inventory Other Current Assets Total Current Assets PP&E Other Assets Goodwill & Intangibles Total Assets 2015-09-30 5,682 3,783 22,590 4,578 36,633 315, 312 9,868 432,352 794, 165 2016-09-30 latios & Rate: 2017-09-30 ####### ####### 8,510 0.027 5,000 0.016 24,995 0.080 5,259 0.017 43,764 317, 162 9,995 9,995 9,995 9,995 430,555 430,555 430,555 430,555 801,476 440,550 440,550 440,550 9,995 430,555 440,550 0.105 0.120 Accounts Payable Other Current liabilities Total Current Liabilities Total Debt (Short plus Long Term) Other Liabilities Total Liabilities Common Stock Retained Earnings Whistler Blackcomb Shareholder E Non-Controlling Interest Total Equity Total Liabilities & Shareholders' Ec 28,793 29,675 58,468 305,232 29,780 393,480 444,775 (90,666) 354,109 46,576 400,685 794,165 32,918 37,338 70,256 257,465 30,285 358,005 444,881 (45,846) 399,035 44,335 443,370 801,376 30,285 30,285 444,881 30,285 30,285 444,881 30,285 30,285 444,881 30,285 30,285 444,881 444,881 44,335 489,216 519,501 444,881 44,335 489,216 519,501 444,881 44,335 489,216 519,501 444,881 44,335 489,216 519,501 2015 2016 latios & Rate: 2017 -0.11 (312,092) 2018 0.03 2019 0.0275 2020 0.025 0.09 0.076 ASSUMPTIONS Sales Growth Change in Sales Depreciation Rate gx gCAPEX mCAPEX CAPEX TOTAL 0.076 0.47 Provide an NPV analysis of the offer to Whistler Blackcomb of $1.4 Billion using the data in the excel sheet provided. Based on this information, should Whistler accept or reject the offer? Hint: complete all the green boxes in the valuation excel sheet provided. Income Statement $000s Revenues Operating Expenses SG&A Depreciation Amortization EBIT Interest Income Before Tax Income Tax Net Income 2015-09-30 2016-09-30 Ratios & Rates ####### ####### ############## 262,254 312,092 140,122 165,593 30,094 36,369 29,659 26,016 0.076 12,509 12,509 12,509 12,509 12,509 12,509 49,870 71,605 (12,509) (12,509) (12,509) (12,509) 20,536 12,362 29,334 59,243 (12,509) (12,509) (12,509) (12,509) 8,049 15,713 21,285 43,530 (12,509) (12,509) (12,509) (12,509) Shares Outstanding 38,042 38,255 DCE Valuation ############################ Free Cash Flow OCF NWC Change in NWC CAPEX FCF PVIF $0.94 $0.89 $0.84 $0.79 0.025 PV of FCF in 2017-2020 Terminal Growth Rate Terminal Value in 2020 PV of Terminal Value D = E = Price = 257,465 (257,465) ($6.73) Balance Sheet 2020-09-30 $000s Cash Accounts Receivable Inventory Other Current Assets Total Current Assets PP&E Other Assets Goodwill & Intangibles Total Assets 2015-09-30 5,682 3,783 22,590 4,578 36,633 315, 312 9,868 432,352 794, 165 2016-09-30 latios & Rate: 2017-09-30 ####### ####### 8,510 0.027 5,000 0.016 24,995 0.080 5,259 0.017 43,764 317, 162 9,995 9,995 9,995 9,995 430,555 430,555 430,555 430,555 801,476 440,550 440,550 440,550 9,995 430,555 440,550 0.105 0.120 Accounts Payable Other Current liabilities Total Current Liabilities Total Debt (Short plus Long Term) Other Liabilities Total Liabilities Common Stock Retained Earnings Whistler Blackcomb Shareholder E Non-Controlling Interest Total Equity Total Liabilities & Shareholders' Ec 28,793 29,675 58,468 305,232 29,780 393,480 444,775 (90,666) 354,109 46,576 400,685 794,165 32,918 37,338 70,256 257,465 30,285 358,005 444,881 (45,846) 399,035 44,335 443,370 801,376 30,285 30,285 444,881 30,285 30,285 444,881 30,285 30,285 444,881 30,285 30,285 444,881 444,881 44,335 489,216 519,501 444,881 44,335 489,216 519,501 444,881 44,335 489,216 519,501 444,881 44,335 489,216 519,501 2015 2016 latios & Rate: 2017 -0.11 (312,092) 2018 0.03 2019 0.0275 2020 0.025 0.09 0.076 ASSUMPTIONS Sales Growth Change in Sales Depreciation Rate gx gCAPEX mCAPEX CAPEX TOTAL 0.076 0.47