Determine the NPV and the IRR of the project calculated using Excels IRR function under different assumptions about first-year sales, cost of capital, and revenue growth (sensitivity analysis). Report your results by choosing the option presented in the following multiple-choice questions
-
(Base case) First year sales are 3%, the cost of capital is 12%, and revenue growth is option 1 in the C6 cell, The NPV and IRR are closest to:
[A] 51,543; 13.2% [B] 30,767; 12.5%
-
First year sales are 2%, the cost of capital is 12%, and revenue growth is option 1 in the C6 cell. The NPV and IRR are closest to:
[A] -421,080; 4.2% [B] -361,280; 5.3%
-
First year sales are 4%, the cost of capital is 12%, and revenue growth is option 1 in the C6 cell. The NPV and IRR are closest to:
[A] 343,678; 16.8% [B] 482,614; 18.8%
-
First year sales are 3%, the cost of capital is 10%, and revenue growth is option 1 in the C6 cell. The NPV and IRR are closest to:
[A] 201,453; 13.6% [B] 165,383; 12.5%
-
First year sales are 3%, the cost of capital is 15%, and revenue growth is option 1 in the C6 cell. The NPV and IRR are closest to:
[A] -145,516; 12.5%
[B] 103,060; 11.1%
-
First year sales are 3%, the cost of capital is 12%, and revenue growth is option 2 in the C7 cell (fixed at 0%). The NPV and IRR are closest to:
[A] -200,305; 8.3% [B] -136,657; 9.6%
-
First year sales are 3%, the cost of capital is 12%, and revenue growth is option 3 in the C8 cell (fixed at 10%). The NPV and IRR are closest to:
[A] 21,115; 12.9% [B] 18,957; 12.3%
12/31/2018 12/31/2017 12/31/2016 12/31/2015 11,379,000 618,000 11,997,000 29,820,000 1,682,000 1,000 49,146,000 11,972,000 608,000 12,580,000 30,649,000 1,583,000 -1,000 49,735,000 7,826,000 701,000 8,527,000 28,188,000 1,553,000 -1,000 43,888,000 7,686,000 508,000 8,194,000 27,353,000 1,551,000 0 42,504,000 32,461,000 32,331,000 30,134,000 29,341,000 -21,668,000 -21,215,000 -19,303,000 -18,616,000 10,793,000 11,116,000 10,831,000 10,725,000 226,000 122,000 104,000 475,000 36,265,000 36,788,000 36,199,000 32,021,000 3,088,000 3,741,000 4,689,000 3,486,000 296,000 572,000 729,000 571,000 74,237,000 75,620,000 73,584,000 67,987,000 123,382,000 125,356,000 117,470,000 110,495,000 Balance Sheet (all numbers in thousands) Breakdown Assets Current Assets Cash Cash And Cash Equivalents Short Term Investments Total Cash Net Receivables Inventory Other Current Assets Total Current Assets Non-current assets Property, plant and equipment Gross property, plant and equipment Accumulated Depreciation Net property, plant and equipment Equity and other investments Goodwill Intangible Assets Other long-term assets Total non-current assets Total Assets Liabilities and stockholders' equity Liabilities Current Liabilities Total Revenue Accounts Payable Taxes payable Accrued liabilities Deferred revenues Other Current Liabilities Total Current Liabilities Non-current liabilities Long Term Debt Deferred taxes liabilities Deferred revenues Other long-term liabilities Total non-current liabilities Total Liabilities Stockholders' Equity Common Stock Retained Earnings Accumulated other comprehensive income Total stockholders' equity Total liabilities and stockholders' equity 10,207,000 6,558,000 6,986,000 6,451,000 7,513,000 6,209,000 6,461,000 6,028,000 3,941,000 11,165,000 7,251,000 38,227,000 4,510,000 11,552,000 1,000 37,363,000 1.18 4,705,000 11,035,000 1,000 36,275,000 4,353,000 11,021,000 -1,000 34,269,000 35,605,000 39,837,000 3,696,000 545,000 3,445,000 3,746,000 1,719,000 1,721,000 68,226,000 70,268,000 106,453,000 107,631,000 34,655,000 424,000 3,600,000 1,778,000 62,803,000 99,078,000 33,428,000 253,000 3,771,000 2,063,000 61,802,000 96,071,000 55,151,000 54,566,000 53,935,000 53,262,000 159,206,000 153,126,000 152,759,000 146,124,000 -29,490,000 -26,592,000 -29,398,000 -29,607,000 16,796,000 17,594,000 18,246,000 14,262,000 123,382,000 125,356,000 117,470,000 110,495,000 Income Statement (all numbers in thousands) Breakdown TTM 12/31/2018 12/31/2017 12/31/2016 12/31/2015 Total Revenue 77,131,000 79,590,000 79,139,000 79,920,000 81,742,000 Cost of Revenue 41,055,000 42,655,000 42,913,000 41,625,000 41,057,000 Gross Profit 36,075,000 36,936,000 36,227,000 38,294,000 40,684,000 Operating Expenses Research Development 5,751,000 5,379,000 5,787,000 5,751,000 5,247,000 Selling General and Administrative 19,754,000 18,863,000 19,555,000 20,479,000 19,894,000 Total Operating Expenses 24,938,000 23,651,000 24,372,000 25,102,000 24,763,000 Operating Income or Loss 11,137,000 13,285,000 11,855,000 13,192,000 15,921,000 Interest Expense 1,183,000 723,000 615,000 630,000 468,000 Total Other Income/Expenses Net 578,000 -1,482,000 17,000 -339,000 421,000 Income Before Tax 10,607,000 11,342,000 11,400,000 12,330,000 15,945,000 Income Tax Expense 2,888,000 2,619,000 5,642,000 449,000 2,581,000 Income from Continuing Operations 7,719,000 8,723,000 5,758,000 11,881,000 13,364,000 Net Income 7,712,000 8,728,000 5,753,000 11,872,000 13,190,000 Net Income available to common sha 7,712,000 8,728,000 5,753,000 11,872,000 13,190,000 Reported EPS Basic 9.57 6.17 12.43 13.48 Diluted 9.52 6.14 12.38 13.42 Weighted average shares outstanding Basic 912,000 932,800 955,400 978,700 Diluted 916,300 937,400 958,700 982,700 EBITDA 16,545,000 16,556,000 17,341,000 20,268,000 EBIT 12,065,000 12,015,000 12,960,000 16,413,000 Cash Flow (all numbers in thousands) Breakdown TTM 12/31/2018 12/31/2017 12/31/2016 12/31/2015 Cash flows from operating activities Net Income 7,712,000 8,728,000 5,753,000 11,872,000 13,190,000 Depreciation & amortization 5,521,000 4,480,000 4,541,000 4,381,000 3,855,000 Deferred income taxes 853,000 -931,000 -1,132,000 1,387,000 Stock based compensation 607,000 510,000 534,000 544,000 468,000 Change in working capital 604,000 554,000 6,813,000 1,231,000 -2,444,000 Accounts receivable -345,000 419,000 1,218,000 200,000 Inventory -127,000 18,000 -14,000 133,000 Accounts Payable 126,000 47,000 197,000 81,000 Other working capital 12,780,000 11,283,000 12,951,000 12,808,000 12,857,000 Other non-cash items -1,000 1,000 544,000 -200,000 Net cash provided by operating activites 15,438,000 15,247,000 16,724,000 16,958,000 17,008,000 Investments in property, plant and equipment -2,658,000 -3,964,000 -3,773,000 -4,150,000 -4,151,000 Acquisitions, net -32,646,000 -139,000 -701,000 -6,133,000 -3,750,000 Purchases of investments -7,041,000 -4,964,000 -5,917,000 -3,073,000 Sales/Maturities of investments 6,487,000 3,910,000 5,692,000 2,842,000 Other investing activites -882,000 -504,000 -2,028,000 -892,000 -397,000 Net cash used for investing activites -26,609,000 -4,913,000 -7,096,000 -10,976,000 -8,159,000 Cash flows from financing activities Debt repayment -8,533,000 -6,816,000 -6,395,000 -5,622,000 Common stock issued 204,000 322,000 Common stock repurchased -3,434,000 -4,614,000 -4,533,000 -3,502,000 -4,609,000 Dividends Paid -5,685,000 -5,666,000 -5,506,000 -5,256,000 -4,897,000 Other financing activites -89,000 112,000 174,000 204,000 321,000 Net cash used privided by (used for) financing 10,112,000 -10,470,000 -6,417,000 -5,791,000 -9,165,000 Net change in cash -1,506,000 -630,000 4,146,000 140,000 -790,000 Cash at beginning of period 11,731,000 12,234,000 7,826,000 7,686,000 8,476,000 Cash at end of period 11,604,000 11,972,000 7,826,000 7,686,000 Free Cash Flow Operating Cash Flow 15,438,000 15,247,000 16,724,000 16,958,000 17,008,000 Capital Expenditure -2,658,000 -3,964,000 -3,773,000 -4,150,000 -4,151,000 Free Cash Flow 12,780,000 11,283,000 12,951,000 12,808,000 12,857,000 12/31/2018 12/31/2017 12/31/2016 12/31/2015 11,379,000 618,000 11,997,000 29,820,000 1,682,000 1,000 49,146,000 11,972,000 608,000 12,580,000 30,649,000 1,583,000 -1,000 49,735,000 7,826,000 701,000 8,527,000 28,188,000 1,553,000 -1,000 43,888,000 7,686,000 508,000 8,194,000 27,353,000 1,551,000 0 42,504,000 32,461,000 32,331,000 30,134,000 29,341,000 -21,668,000 -21,215,000 -19,303,000 -18,616,000 10,793,000 11,116,000 10,831,000 10,725,000 226,000 122,000 104,000 475,000 36,265,000 36,788,000 36,199,000 32,021,000 3,088,000 3,741,000 4,689,000 3,486,000 296,000 572,000 729,000 571,000 74,237,000 75,620,000 73,584,000 67,987,000 123,382,000 125,356,000 117,470,000 110,495,000 Balance Sheet (all numbers in thousands) Breakdown Assets Current Assets Cash Cash And Cash Equivalents Short Term Investments Total Cash Net Receivables Inventory Other Current Assets Total Current Assets Non-current assets Property, plant and equipment Gross property, plant and equipment Accumulated Depreciation Net property, plant and equipment Equity and other investments Goodwill Intangible Assets Other long-term assets Total non-current assets Total Assets Liabilities and stockholders' equity Liabilities Current Liabilities Total Revenue Accounts Payable Taxes payable Accrued liabilities Deferred revenues Other Current Liabilities Total Current Liabilities Non-current liabilities Long Term Debt Deferred taxes liabilities Deferred revenues Other long-term liabilities Total non-current liabilities Total Liabilities Stockholders' Equity Common Stock Retained Earnings Accumulated other comprehensive income Total stockholders' equity Total liabilities and stockholders' equity 10,207,000 6,558,000 6,986,000 6,451,000 7,513,000 6,209,000 6,461,000 6,028,000 3,941,000 11,165,000 7,251,000 38,227,000 4,510,000 11,552,000 1,000 37,363,000 1.18 4,705,000 11,035,000 1,000 36,275,000 4,353,000 11,021,000 -1,000 34,269,000 35,605,000 39,837,000 3,696,000 545,000 3,445,000 3,746,000 1,719,000 1,721,000 68,226,000 70,268,000 106,453,000 107,631,000 34,655,000 424,000 3,600,000 1,778,000 62,803,000 99,078,000 33,428,000 253,000 3,771,000 2,063,000 61,802,000 96,071,000 55,151,000 54,566,000 53,935,000 53,262,000 159,206,000 153,126,000 152,759,000 146,124,000 -29,490,000 -26,592,000 -29,398,000 -29,607,000 16,796,000 17,594,000 18,246,000 14,262,000 123,382,000 125,356,000 117,470,000 110,495,000 Income Statement (all numbers in thousands) Breakdown TTM 12/31/2018 12/31/2017 12/31/2016 12/31/2015 Total Revenue 77,131,000 79,590,000 79,139,000 79,920,000 81,742,000 Cost of Revenue 41,055,000 42,655,000 42,913,000 41,625,000 41,057,000 Gross Profit 36,075,000 36,936,000 36,227,000 38,294,000 40,684,000 Operating Expenses Research Development 5,751,000 5,379,000 5,787,000 5,751,000 5,247,000 Selling General and Administrative 19,754,000 18,863,000 19,555,000 20,479,000 19,894,000 Total Operating Expenses 24,938,000 23,651,000 24,372,000 25,102,000 24,763,000 Operating Income or Loss 11,137,000 13,285,000 11,855,000 13,192,000 15,921,000 Interest Expense 1,183,000 723,000 615,000 630,000 468,000 Total Other Income/Expenses Net 578,000 -1,482,000 17,000 -339,000 421,000 Income Before Tax 10,607,000 11,342,000 11,400,000 12,330,000 15,945,000 Income Tax Expense 2,888,000 2,619,000 5,642,000 449,000 2,581,000 Income from Continuing Operations 7,719,000 8,723,000 5,758,000 11,881,000 13,364,000 Net Income 7,712,000 8,728,000 5,753,000 11,872,000 13,190,000 Net Income available to common sha 7,712,000 8,728,000 5,753,000 11,872,000 13,190,000 Reported EPS Basic 9.57 6.17 12.43 13.48 Diluted 9.52 6.14 12.38 13.42 Weighted average shares outstanding Basic 912,000 932,800 955,400 978,700 Diluted 916,300 937,400 958,700 982,700 EBITDA 16,545,000 16,556,000 17,341,000 20,268,000 EBIT 12,065,000 12,015,000 12,960,000 16,413,000 Cash Flow (all numbers in thousands) Breakdown TTM 12/31/2018 12/31/2017 12/31/2016 12/31/2015 Cash flows from operating activities Net Income 7,712,000 8,728,000 5,753,000 11,872,000 13,190,000 Depreciation & amortization 5,521,000 4,480,000 4,541,000 4,381,000 3,855,000 Deferred income taxes 853,000 -931,000 -1,132,000 1,387,000 Stock based compensation 607,000 510,000 534,000 544,000 468,000 Change in working capital 604,000 554,000 6,813,000 1,231,000 -2,444,000 Accounts receivable -345,000 419,000 1,218,000 200,000 Inventory -127,000 18,000 -14,000 133,000 Accounts Payable 126,000 47,000 197,000 81,000 Other working capital 12,780,000 11,283,000 12,951,000 12,808,000 12,857,000 Other non-cash items -1,000 1,000 544,000 -200,000 Net cash provided by operating activites 15,438,000 15,247,000 16,724,000 16,958,000 17,008,000 Investments in property, plant and equipment -2,658,000 -3,964,000 -3,773,000 -4,150,000 -4,151,000 Acquisitions, net -32,646,000 -139,000 -701,000 -6,133,000 -3,750,000 Purchases of investments -7,041,000 -4,964,000 -5,917,000 -3,073,000 Sales/Maturities of investments 6,487,000 3,910,000 5,692,000 2,842,000 Other investing activites -882,000 -504,000 -2,028,000 -892,000 -397,000 Net cash used for investing activites -26,609,000 -4,913,000 -7,096,000 -10,976,000 -8,159,000 Cash flows from financing activities Debt repayment -8,533,000 -6,816,000 -6,395,000 -5,622,000 Common stock issued 204,000 322,000 Common stock repurchased -3,434,000 -4,614,000 -4,533,000 -3,502,000 -4,609,000 Dividends Paid -5,685,000 -5,666,000 -5,506,000 -5,256,000 -4,897,000 Other financing activites -89,000 112,000 174,000 204,000 321,000 Net cash used privided by (used for) financing 10,112,000 -10,470,000 -6,417,000 -5,791,000 -9,165,000 Net change in cash -1,506,000 -630,000 4,146,000 140,000 -790,000 Cash at beginning of period 11,731,000 12,234,000 7,826,000 7,686,000 8,476,000 Cash at end of period 11,604,000 11,972,000 7,826,000 7,686,000 Free Cash Flow Operating Cash Flow 15,438,000 15,247,000 16,724,000 16,958,000 17,008,000 Capital Expenditure -2,658,000 -3,964,000 -3,773,000 -4,150,000 -4,151,000 Free Cash Flow 12,780,000 11,283,000 12,951,000 12,808,000 12,857,000