1. For the Income Statement, please calculate all the ratios in the black box below Income Sta 2. For the Balance Sheet, please calculate all

image text in transcribed

1. For the Income Statement, please calculate all the ratios in the black box below Income Sta 2. For the Balance Sheet, please calculate all the ratios in the black box below the Balance Sh 3. For the DCF (Discounted Cash Flow or Free Cash Flow) Analysis, please make your own assumptions based on all the ratios you calculated for Question 1 and 2 and input your assumed numbers in the ASSUMPTION box. Also, please explain the reason why you make such assumptions in the REASON box . 4. Compose the Income Statement in the DCF Analysis Spreadsheet based on your assumptions. And using your given WACC and terminal growth rate to find the fair stock price at the bottom of the template. Note: Your calculation for all ratios have to be in the right format in order to get full creidt: % with one decimal. Instruction Income Statement Balance Sheet DCF Analysis + Consolidated Income Statements 2014 2015 2016 2017 2018 2019 2020 Year Ended May 31 2013 (In millions except per share data) Revenues 158,217.9 164,986.9 171,864.7 189,509.0 117,418.3 201,340.2 232,102.9 Cost of Goods Sold 61.955.1 69.791.5 76,920.5 84.014.4 90,424.8 94.807.6 97,520.6 Gross profit 96,262.8 95,195.4 94,944.2 105,494.7 26,993.4 106,532.5 134,582.3 Selling and administrative 21,252.5 23,290.8 25.681.0 28.120.2 30.399.5 32,084.6 33,241.2 Operating income 75,010.3 71,904.6 69,263.2 77,374.5 3,406.1 74,447.9 101,341.1 Depreciation 1,450.1 1,084.2 1,372.7 1,959.0 2,631.4 2,559.2 2,177.9 Other expense, net 27.474.6 31,045.1 34.099.5 37.010.1 39,875.7 41.648.0 43,119.1 Income before income taxe 46,085.6 39,775.2 33,791.0 38,405.4 -45,913.2 30,240.8 56,044.1 Income taxes 14434. 15910.1 17516.4 19362. 1517 21634.71 24096.3114 26 Net income 31,651.4 23,865.1 16,274.6 19,043.2 -67,547.9 6,144.5 29,626.4 260,865.0 99,593.4 161,271.6 34,117.6 127,153.9 1,827.8 44.499.9 80,826.2 28330.4932 52,495.7 PLEASE CALCULATE THE FOLLOWING RATIOS FOR EACH YEAR Growth (%) Revenue Growth NA Operating income Growth NA Net income Growth NA Margins (%) Gross margin Operating margin Net margin COGS/Sales (%) S&A/Sales(%) Good Thing Furniture Consolidated Balance Sheets 2014 2015 2016 2017 2018 2019 2020 As of (In millions) Assets Current Assets: Accounts receivable Inventories Total current assets Property, plant and equipment, net Total assets 47,200.0 52.455.8 56,924.2 62,640.9 71,443.5 33.343.0 36,614.2 39.990.8 43,042.2 45.128.4 80,542.9 89,069.9 96,915.0 105,683.1 116,571.9 73,954.2 46.419.8 120,374.0 77.441.3 47,406.5 124,847.8 3,920.1 5,202.6 7.155.3 9,310.3 7.440.0 84,463.0 94,272.5 104,070.3 114,993.4 124,012.0 5.202.8 125,576.9 3.641.4 128,489.2 Liabilities and shareholders' Current Liabilities: Accounts payable 11,363.5 12,538.0 13,694.3 14,739.2 15,453.6 Accrued liabilities 1.682.3 1,878.7 2.056.7 2.225.9 2.348.9 Total current liabilities 13,045.8 14,416.7 15,751.1 16,965.1 17,802.6 Debt 6,790.0 5,890.0 5,978.0 6,120.0 6,379.0 Equity 65,627.2 74.965.8 84,341.3 93,908.3 98,830.4 Total liabilities and shareholders' 85,463.0 95,272.5 106,070.3 116,993.4 123,012.0 15,895.9 2.436.4 18,332.2 16,233.7 2.504.6 18,738.4 6,123.0 102.121.6 126,576.9 6,509.0 104,241.8 129,489.2 PLEASE CALCULATE THE FOLLOWING RATIOS FOR EACH YEAR Current assets/Sales (%) Current liabilities/Sales (%) Discounted Free Cash Flow Analysis 2022 2029 2030 2031 2023 2024 2025 2026 2027 2028 ASSUMPTIONS REASONS Revenue growth (2) COGS/Sales (%) S&A/Sales (2) Tax rate (2) Current assets/Sales (7) Current liabilities/Sales (%) Yearly depreciation and capex equal each o cost of capital (WACC) Terminal value growth rate - - 10.00% 3.00% 7 8 lo 9 10 You Are Advised NOT to Edit Any Number EXCEPT the Cells In Yellow Discounted Cash Flox (In Mi Discount 2 3 5 6 Revenue Cost of Goods Sold Gross profit Selling and administrative Operating Income Taxes NOPAT 0.0 0.0 0.0 0.0 0.0 0.0 Capes net of depreciation NWC 0.0 0.0 0.0 0.0 0.0 0.0 Change in NWC 200.00 0.0 0.0 0.0 0.0 0.0 Free cash floy (FCF) -200.0 0.0 0.0 0.0 0.0 0.0 Terminal Value Total flows -200.0 0.0 0.0 0.0 0.0 0.0 Present Value of flows 0.0 0.0 0.0 0.0 OOO I OOO I OOO 0.0 0.0 0.0 0.0 0.0 0.0 do OOO 0.0 0.0 0.0 0.0 0.0 0.0 do I OC 0.0 0.0 0.0 0.0 Enterprise Value less: current outstanding debt Equity Value current shares outstanding Equity Value Per Share 0.00 6509.00 -6509.00 104241.81 -0.06 1. For the Income Statement, please calculate all the ratios in the black box below Income Sta 2. For the Balance Sheet, please calculate all the ratios in the black box below the Balance Sh 3. For the DCF (Discounted Cash Flow or Free Cash Flow) Analysis, please make your own assumptions based on all the ratios you calculated for Question 1 and 2 and input your assumed numbers in the ASSUMPTION box. Also, please explain the reason why you make such assumptions in the REASON box . 4. Compose the Income Statement in the DCF Analysis Spreadsheet based on your assumptions. And using your given WACC and terminal growth rate to find the fair stock price at the bottom of the template. Note: Your calculation for all ratios have to be in the right format in order to get full creidt: % with one decimal. Instruction Income Statement Balance Sheet DCF Analysis + Consolidated Income Statements 2014 2015 2016 2017 2018 2019 2020 Year Ended May 31 2013 (In millions except per share data) Revenues 158,217.9 164,986.9 171,864.7 189,509.0 117,418.3 201,340.2 232,102.9 Cost of Goods Sold 61.955.1 69.791.5 76,920.5 84.014.4 90,424.8 94.807.6 97,520.6 Gross profit 96,262.8 95,195.4 94,944.2 105,494.7 26,993.4 106,532.5 134,582.3 Selling and administrative 21,252.5 23,290.8 25.681.0 28.120.2 30.399.5 32,084.6 33,241.2 Operating income 75,010.3 71,904.6 69,263.2 77,374.5 3,406.1 74,447.9 101,341.1 Depreciation 1,450.1 1,084.2 1,372.7 1,959.0 2,631.4 2,559.2 2,177.9 Other expense, net 27.474.6 31,045.1 34.099.5 37.010.1 39,875.7 41.648.0 43,119.1 Income before income taxe 46,085.6 39,775.2 33,791.0 38,405.4 -45,913.2 30,240.8 56,044.1 Income taxes 14434. 15910.1 17516.4 19362. 1517 21634.71 24096.3114 26 Net income 31,651.4 23,865.1 16,274.6 19,043.2 -67,547.9 6,144.5 29,626.4 260,865.0 99,593.4 161,271.6 34,117.6 127,153.9 1,827.8 44.499.9 80,826.2 28330.4932 52,495.7 PLEASE CALCULATE THE FOLLOWING RATIOS FOR EACH YEAR Growth (%) Revenue Growth NA Operating income Growth NA Net income Growth NA Margins (%) Gross margin Operating margin Net margin COGS/Sales (%) S&A/Sales(%) Good Thing Furniture Consolidated Balance Sheets 2014 2015 2016 2017 2018 2019 2020 As of (In millions) Assets Current Assets: Accounts receivable Inventories Total current assets Property, plant and equipment, net Total assets 47,200.0 52.455.8 56,924.2 62,640.9 71,443.5 33.343.0 36,614.2 39.990.8 43,042.2 45.128.4 80,542.9 89,069.9 96,915.0 105,683.1 116,571.9 73,954.2 46.419.8 120,374.0 77.441.3 47,406.5 124,847.8 3,920.1 5,202.6 7.155.3 9,310.3 7.440.0 84,463.0 94,272.5 104,070.3 114,993.4 124,012.0 5.202.8 125,576.9 3.641.4 128,489.2 Liabilities and shareholders' Current Liabilities: Accounts payable 11,363.5 12,538.0 13,694.3 14,739.2 15,453.6 Accrued liabilities 1.682.3 1,878.7 2.056.7 2.225.9 2.348.9 Total current liabilities 13,045.8 14,416.7 15,751.1 16,965.1 17,802.6 Debt 6,790.0 5,890.0 5,978.0 6,120.0 6,379.0 Equity 65,627.2 74.965.8 84,341.3 93,908.3 98,830.4 Total liabilities and shareholders' 85,463.0 95,272.5 106,070.3 116,993.4 123,012.0 15,895.9 2.436.4 18,332.2 16,233.7 2.504.6 18,738.4 6,123.0 102.121.6 126,576.9 6,509.0 104,241.8 129,489.2 PLEASE CALCULATE THE FOLLOWING RATIOS FOR EACH YEAR Current assets/Sales (%) Current liabilities/Sales (%) Discounted Free Cash Flow Analysis 2022 2029 2030 2031 2023 2024 2025 2026 2027 2028 ASSUMPTIONS REASONS Revenue growth (2) COGS/Sales (%) S&A/Sales (2) Tax rate (2) Current assets/Sales (7) Current liabilities/Sales (%) Yearly depreciation and capex equal each o cost of capital (WACC) Terminal value growth rate - - 10.00% 3.00% 7 8 lo 9 10 You Are Advised NOT to Edit Any Number EXCEPT the Cells In Yellow Discounted Cash Flox (In Mi Discount 2 3 5 6 Revenue Cost of Goods Sold Gross profit Selling and administrative Operating Income Taxes NOPAT 0.0 0.0 0.0 0.0 0.0 0.0 Capes net of depreciation NWC 0.0 0.0 0.0 0.0 0.0 0.0 Change in NWC 200.00 0.0 0.0 0.0 0.0 0.0 Free cash floy (FCF) -200.0 0.0 0.0 0.0 0.0 0.0 Terminal Value Total flows -200.0 0.0 0.0 0.0 0.0 0.0 Present Value of flows 0.0 0.0 0.0 0.0 OOO I OOO I OOO 0.0 0.0 0.0 0.0 0.0 0.0 do OOO 0.0 0.0 0.0 0.0 0.0 0.0 do I OC 0.0 0.0 0.0 0.0 Enterprise Value less: current outstanding debt Equity Value current shares outstanding Equity Value Per Share 0.00 6509.00 -6509.00 104241.81 -0.06