Help with this question!
Question 1: You and your team are concerned that the model is based on only one sales growth rate and one tax rate. Fill in the table below based on manually changing the key inputs to the model. (Be sure to change the key inputs back to formulas after answering this question). Case Analysis Tax Rate Sales Growth YOY (%) Earnings Per Share Basic 2026E Weak Case 15% 1.5% Steady State 17% 3.5% Strong State 17% 5.5% 2020A Assumptions 2017A 1.) Sales Growth YOY (%) 1.01% 2.) Cost of Sales % of Sales 54% 3.) Selling, Marketing and Administrative % of Sales 25% 4.) Business Realignment Costs 256,475 5.) Other Income (Expense), net (104,459) 6.) Corporate Tax Rate 32% 7.) Shares Outstanding Basic 192,367 8.) Shares Outstanding Diluted 199,479 9.) Accounts Receivable, Prepaid Expenses, Other Assets 1.01% 10.) Inventories 7.36% 11.) Goodwill and Other Intangibles 12.) Property, Plant and Equipment % of Sales 28% 13.) Accounts Payable 7.36% 14.) Accruals and Other Long-Term Liabilities 1.01% 15.) Current Portion of Long-Term Debt 16.) Long-Term Debt 17.) Common Stock Par 18.) Dividends (Payout Ratio) 40% 19.) Share Repurchase as a % of Net Income 20.) Depreciations % of PPE 12% 21.) Other Comprehensive Income on Balance Sheet 22.) Cash and cash equivalents as a % of Sales 5% 23.) Interest rate on Debt is calculated as average estimated interest rate 2018A 3.67% 54% 24% 76,832 (74,766) 17% 204,438 211,033 3.67% 3.83% 821,061 27% 3.83% 3.67% 2019A 2.51% 55% 24% 120,597 (71,043) 17% 203,846 210,566 2.51% 3.51% 1,801,103 27% 3.51% 2.51% 2.05% 55% 23% 27,646 (138,327) 15% 202,970 209,281 2.05% 1.94% 1,985,955 28% 1.94% 2.05% 2021A 10.08% 55% 22% 3,525 (119,081) 17% 201,296 207,808 10.08% 10.66% 1,988,215 29% 10.66% 10.08% Average 3.86% 54.45% 23.71% 0.00 (101,535) 17% 201,296 207,808 3.86% 3.06% Notes Sales growth forecasted at historical year over year growth rate average Cost of Sales forecasted as historical average percent of sales SG&A forecasted as historical average percent of sales Drive to Zero and carry forward Other Income (Expense) forecasted as historical average Corporate Tax Rate is 2021 Estimated Tax Rate Carry forward last year shares outstanding Basic Carry forward last year shares outstanding Diluted Grow at same rate as sales Grow at same rate as Cost of Sales Carry forward last year values Property, Plant and Equipment forecasted as average percent of Sales Grow at same rate as Cost of Sales Grow at same rate as Sales Taken from Maturity Schedule in 2021 10-K Sum of Current Portion of Long-Term Debt and Long-Term Debt stays constant Carry Forward Last Year Dividend Payout estimated at historical average payout ratio Share Repurchase rate estimated at historical share repurchase percent of net i 28% 3.06% 3.86% 48% 53% 50% 46% 47% 14% 14% 13% 12% 13% Carried forward at last year Cash Needed is forecasted at average % of Sales excluding 2020A 8% 6% 14% 4% 6% Income Statement (thousands) 2017A 2018A 2019A 2020A 2021A 2022E 2023E 2024E 2025E 2026E Sales % YOY Growth Net Sales 7,515,426 7,791,069 7,986,252 8,149,719 8,971,337 9,317,858 9,677,763 10,051,569 10,439,814 10,843,055 1.01% 3.67% 2.51% 2.05% 10.08% 3.86% 3.86% 3.86% 3.86% 3.86% $ 7,515,426 $ 7,791,069 $ 7,986,252 $ 8,149,719 $ 8,971,337 $ 9,317,858 $ 9,677,763 $ 10,051,569 $ 10,439,814 $ 10,843,055 Income Statement (thousands) 2017A 2018A 2019A 2020A 2021A 2022E 2023E 2024E 2025E 2026E Sales % YOY Growth Net Sales Cost of Sales % YOY Growth Gross Profit Selling, Marketing and Administrative Expense Business Realignment Costs Operating Profit Interest Expense Other Income (Expense), net Income Before Income Taxes Income Taxes Net Income 7,515,426 7,791,069 7,986,252 8,149,719 8,971,337 9,317,858 9,677,763 10,051,569 10,439,814 10,843,055 1.01% 3.67% 2.51% 2.05% 10.08% 3.86% 3.86% 3.86% 3.86% 3.86% $ 7,515,426 $ 7,791,069 $ 7,986,252 $ 8,149,719 $ 8,971,337 $ 9,317,858 $ 9,677,763 $ 10,051,569 $ 10,439,814 $ 10,843,055 4,060,050 4,215,744 4,363,774 4,448,450 4,922,739 5,073,198 5,269,152 5,472,675 5,684,058 5,903,607 7.36% 3.83% 3.51% 1.94% 10.66% 3.06% 3.86% 3.86% 3.86% 3.86% $ 3,455,376 $ 3,575,325 $ 3,622,478 $ 3,701,269 $ 4,048,598 $ 4,244,659 $ 4,408,611 $ 4,578,895 $ 4,755,756 $ 4,939,448 1,885,492 1,874,829 1,905,929 1,890,925 2,001,351 2,208,851 2,294,169 2,382,782 2,474,817 2,570,408 256,475 76,832 120,597 27,646 3,525 $ 1,313,409 $ 1,623,664 $ 1,595,952 $ 1,782,698 $ 2,043,722 $ 2,035,808 $ 2,114,442 $ 2,196,113 $ 2,280,938 $ 2,369,040 98,282 138,837 144,125 149,374 127,417 154,820 133,091 108,413 82,720 55,968 (104,459) (74,766) (71,043) (138,327) (119,081) (101,535) (101,535) (101,535) (101,535) (101,535) $ 1,110,668 $ 1,410,061 $ 1,380,784 $ 1,494,997 $ 1,797,224 $ 1,779,453 $ 1,879,815 $ 1,986,165 $ 2,096,683 $ 2,211,537 354,131 239,010 234,032 219,584 314,405 311,296 328,853 347,458 366,792 386,885 $ 756,537 $ 1,171,051 $ 1,146,752 $ 1,275,413 $ 1,482,819 $ 1,468,157 $1,550,962 $ 1,638,706 $ 1,729,891 $ 1,824,652 3.93 $ 9.06 EPS (Basic) EPS (Diluted) $ $ 5.73 $ 5.55 : $ 5.63 $ 5.45 $ 6.28 $ 6.09 $ 7.37 $ 7.14 $ 7.29 $ 7.06 $ 7.70 $ 7.46 $ 8.14 $ 7.89 $ 8.59 $ 8.32 $ 3.79 $ 8.78 Shares Outstanding (Basic) (thousands) Shares Outstanding (Diluted) (thousands) 192,367 199,479 204,438 211,033 203,846 210,566 202,970 209,281 201,296 207,808 201,296 207,808 201,296 207,808 201,296 207,808 201,296 207,808 201,296 207,808 Question 1: You and your team are concerned that the model is based on only one sales growth rate and one tax rate. Fill in the table below based on manually changing the key inputs to the model. (Be sure to change the key inputs back to formulas after answering this question). Case Analysis Tax Rate Sales Growth YOY (%) Earnings Per Share Basic 2026E Weak Case 15% 1.5% Steady State 17% 3.5% Strong State 17% 5.5% 2020A Assumptions 2017A 1.) Sales Growth YOY (%) 1.01% 2.) Cost of Sales % of Sales 54% 3.) Selling, Marketing and Administrative % of Sales 25% 4.) Business Realignment Costs 256,475 5.) Other Income (Expense), net (104,459) 6.) Corporate Tax Rate 32% 7.) Shares Outstanding Basic 192,367 8.) Shares Outstanding Diluted 199,479 9.) Accounts Receivable, Prepaid Expenses, Other Assets 1.01% 10.) Inventories 7.36% 11.) Goodwill and Other Intangibles 12.) Property, Plant and Equipment % of Sales 28% 13.) Accounts Payable 7.36% 14.) Accruals and Other Long-Term Liabilities 1.01% 15.) Current Portion of Long-Term Debt 16.) Long-Term Debt 17.) Common Stock Par 18.) Dividends (Payout Ratio) 40% 19.) Share Repurchase as a % of Net Income 20.) Depreciations % of PPE 12% 21.) Other Comprehensive Income on Balance Sheet 22.) Cash and cash equivalents as a % of Sales 5% 23.) Interest rate on Debt is calculated as average estimated interest rate 2018A 3.67% 54% 24% 76,832 (74,766) 17% 204,438 211,033 3.67% 3.83% 821,061 27% 3.83% 3.67% 2019A 2.51% 55% 24% 120,597 (71,043) 17% 203,846 210,566 2.51% 3.51% 1,801,103 27% 3.51% 2.51% 2.05% 55% 23% 27,646 (138,327) 15% 202,970 209,281 2.05% 1.94% 1,985,955 28% 1.94% 2.05% 2021A 10.08% 55% 22% 3,525 (119,081) 17% 201,296 207,808 10.08% 10.66% 1,988,215 29% 10.66% 10.08% Average 3.86% 54.45% 23.71% 0.00 (101,535) 17% 201,296 207,808 3.86% 3.06% Notes Sales growth forecasted at historical year over year growth rate average Cost of Sales forecasted as historical average percent of sales SG&A forecasted as historical average percent of sales Drive to Zero and carry forward Other Income (Expense) forecasted as historical average Corporate Tax Rate is 2021 Estimated Tax Rate Carry forward last year shares outstanding Basic Carry forward last year shares outstanding Diluted Grow at same rate as sales Grow at same rate as Cost of Sales Carry forward last year values Property, Plant and Equipment forecasted as average percent of Sales Grow at same rate as Cost of Sales Grow at same rate as Sales Taken from Maturity Schedule in 2021 10-K Sum of Current Portion of Long-Term Debt and Long-Term Debt stays constant Carry Forward Last Year Dividend Payout estimated at historical average payout ratio Share Repurchase rate estimated at historical share repurchase percent of net i 28% 3.06% 3.86% 48% 53% 50% 46% 47% 14% 14% 13% 12% 13% Carried forward at last year Cash Needed is forecasted at average % of Sales excluding 2020A 8% 6% 14% 4% 6% Income Statement (thousands) 2017A 2018A 2019A 2020A 2021A 2022E 2023E 2024E 2025E 2026E Sales % YOY Growth Net Sales 7,515,426 7,791,069 7,986,252 8,149,719 8,971,337 9,317,858 9,677,763 10,051,569 10,439,814 10,843,055 1.01% 3.67% 2.51% 2.05% 10.08% 3.86% 3.86% 3.86% 3.86% 3.86% $ 7,515,426 $ 7,791,069 $ 7,986,252 $ 8,149,719 $ 8,971,337 $ 9,317,858 $ 9,677,763 $ 10,051,569 $ 10,439,814 $ 10,843,055 Income Statement (thousands) 2017A 2018A 2019A 2020A 2021A 2022E 2023E 2024E 2025E 2026E Sales % YOY Growth Net Sales Cost of Sales % YOY Growth Gross Profit Selling, Marketing and Administrative Expense Business Realignment Costs Operating Profit Interest Expense Other Income (Expense), net Income Before Income Taxes Income Taxes Net Income 7,515,426 7,791,069 7,986,252 8,149,719 8,971,337 9,317,858 9,677,763 10,051,569 10,439,814 10,843,055 1.01% 3.67% 2.51% 2.05% 10.08% 3.86% 3.86% 3.86% 3.86% 3.86% $ 7,515,426 $ 7,791,069 $ 7,986,252 $ 8,149,719 $ 8,971,337 $ 9,317,858 $ 9,677,763 $ 10,051,569 $ 10,439,814 $ 10,843,055 4,060,050 4,215,744 4,363,774 4,448,450 4,922,739 5,073,198 5,269,152 5,472,675 5,684,058 5,903,607 7.36% 3.83% 3.51% 1.94% 10.66% 3.06% 3.86% 3.86% 3.86% 3.86% $ 3,455,376 $ 3,575,325 $ 3,622,478 $ 3,701,269 $ 4,048,598 $ 4,244,659 $ 4,408,611 $ 4,578,895 $ 4,755,756 $ 4,939,448 1,885,492 1,874,829 1,905,929 1,890,925 2,001,351 2,208,851 2,294,169 2,382,782 2,474,817 2,570,408 256,475 76,832 120,597 27,646 3,525 $ 1,313,409 $ 1,623,664 $ 1,595,952 $ 1,782,698 $ 2,043,722 $ 2,035,808 $ 2,114,442 $ 2,196,113 $ 2,280,938 $ 2,369,040 98,282 138,837 144,125 149,374 127,417 154,820 133,091 108,413 82,720 55,968 (104,459) (74,766) (71,043) (138,327) (119,081) (101,535) (101,535) (101,535) (101,535) (101,535) $ 1,110,668 $ 1,410,061 $ 1,380,784 $ 1,494,997 $ 1,797,224 $ 1,779,453 $ 1,879,815 $ 1,986,165 $ 2,096,683 $ 2,211,537 354,131 239,010 234,032 219,584 314,405 311,296 328,853 347,458 366,792 386,885 $ 756,537 $ 1,171,051 $ 1,146,752 $ 1,275,413 $ 1,482,819 $ 1,468,157 $1,550,962 $ 1,638,706 $ 1,729,891 $ 1,824,652 3.93 $ 9.06 EPS (Basic) EPS (Diluted) $ $ 5.73 $ 5.55 : $ 5.63 $ 5.45 $ 6.28 $ 6.09 $ 7.37 $ 7.14 $ 7.29 $ 7.06 $ 7.70 $ 7.46 $ 8.14 $ 7.89 $ 8.59 $ 8.32 $ 3.79 $ 8.78 Shares Outstanding (Basic) (thousands) Shares Outstanding (Diluted) (thousands) 192,367 199,479 204,438 211,033 203,846 210,566 202,970 209,281 201,296 207,808 201,296 207,808 201,296 207,808 201,296 207,808 201,296 207,808 201,296 207,808
