Anonymous
timer Asked: May 7th, 2020

Question Description

This project is based on the WOK YOW IMPORTS, INC. case found in your textbook under Chapter 14. Please read the case carefully. An excel spreadsheet is provided that has the solution to the case along with suggested answers to the case questions. Please go over the excel sheet first and make sure that you can follow the calculations for the discounted cash flow valuation method (enterprise valuation method).



All the formulas are built into the excel file so when you go over a cell, you can track how the calculation for each number on the sheet is done. Pay special attention to the financial ratios, growth assumptions, calculation of the free cash flows for the foreseeable future (5 years) and the calculation of the terminal value. These future free cash flows are then discounted back to time zero using the discount rate (WACC).



Once you feel comfortable on the model and the solutions to the case, I want you to answer the questions to complete your project assignment.



All attachments: instruction, financial model, and excel is included below.

Unformatted Attachment Preview

MINI CASE: WOK YOW IMPORTS, INC. Wok Yow Imports, Inc., is a rapidly growing, closely held corporation that imports and sells oriental style furniture and accessories at several retail outlets. The equity owners are considering selling the venture and want to estimate the enterprise or entity value and then determine the value of the venture’s equity. Following is last year’s income statement (2013) and projected income statements for the next four years (2014-2017). Sales are expected to grow at an annual 6 percent rate beginning in 2018 and thereafter. [$ Thousands] Net Sales Cost of Goods Sold Gross Profit SG&A Expenses Depreciation EBIT Interest EBT Taxes (40% rate) Net Income Actual 2013 $150.0 -75.0 75.0 -30.0 -7.5 37.5 -3.5 34.0 -13.6 20.4 Projected -----------------------------------------------2014 2015 2016 2017 $200.0 $250.0 $300.0 $350.0 -100.0 -125.0 -150.0 -175.0 100.0 125.0 150.0 175.0 -40.0 -50.0 -60.0 -70.0 -10.0 -12.5 -15.0 -17.5 50.0 62.5 75.0 87.5 -3.5 -3.5 -3.5 -3.5 46.5 59.0 71.5 84.0 -18.6 -23.6 -28.6 -33.6 27.9 35.4 42.9 50.4 Selected balance sheet accounts at the end of 2013 are as follows: Required cash, accounts receivable, and inventories accounts totaled $50,000, net fixed assets were $50,000, and accounts payable and accruals totaled $25,000. Each of these balance sheet accounts was expected to grow with sales over time. Long-term debt was $30,000 and there were 10,000 shares of common stock outstanding at the end of 2013. Data have been gathered for a “comparable” publicly traded firm, Fine Furniture Products, in Wok Yow’s industry. Fine Furniture’s risk index is judged to be 2.00 compared to a risk index of 1.00 for firms of average riskiness. Management believes that a 2.00 adjustment factor should be multiplied times the expected market risk premium for average firms to reflect Wok Yow’s (and Fine Furniture’s) relatively greater riskiness. Wok Yow’s long-term debt to long-term capital (long-term debt plus equity) ratio was 40 percent at the end of 2013. The interest rate on long-term U.S. government bonds is 7 percent, Wok Yow could issue new long-term debt at a 12 percent rate, and the average expected market risk premium (common stocks over government bonds) is 7.5 percent for average firms. A. Project Wok Yow’s net operating profit after-tax (NOPAT) statements for 20142018. The NOPAT results are shown below in the spreadsheet solution. NOPAT amounts were: 30.0 (2014), 37.5 (2015), 45.0 (2016), 52.5 (2017), and 55.7 (2018). B. Determine the annual increases in required net working capital and capital expenditures (CAPEX) for Wok Yow for the years 2014 through 2018. Results are shown below in the spreadsheet solution. The increases in RNWC were: 8.3 (2014), 8.3 (2015), 8.3 (2016), 8.3 (2017), and 3.5 (2018). The increases in CAPEX were: 26.7 (2014), 29.2 (2015), 31.7 (2016), 34.2 (2017), and 25.5 (2018). C. Project annual operating free cash flows to the entity for the years 2014 through 2018. Results are shown below in the spreadsheet solution. Operating free cash flows were: 5.0 (2014), 12.4 (2015), 20.0 (2016) and 27.5 (2017). An operating free cash flow of 45.2 was estimated for 2018. D. Management initially thought that an 18 percent discount rate was reasonable. Estimating an appropriate required rate of return on an equity investment is critical to the valuation effort. For very early stage ventures, various VC “rules-of thumb” might be used ranging from development stage discount rates of 50% down to 20% discount rates for relatively mature, but small ventures. The alternative is to estimate an appropriate discount rate using the security market line approach described in Chapter 7. A venture that employs some interestbearing debt will have a weighted average cost of capital WACC that is less than the cost of equity capital. Management has made a rough estimate of the firm’s WACC or enterprise (entity) discount rate to be 18%. E. Use the information from Part D above to estimate Wok Yow’s terminal value cash flow at the end of 2017. The terminal value cash flow is estimated to be 376.3 and is calculated by dividing the estimated operating free cash flow for 2017 of 45.2 by .12 which is the difference between the discount rate (r = .18) and the perpetuity growth rate (g = .06). See the spreadsheet solution presented below. In greater detail, the calculation is: 45.152/(.18 - .06) = 376.267. F. Estimate the firm’s enterprise or entity value at the end of 2013. The enterprise or entity value at the end of 2013 is estimated to be 233.6 (thousands of dollars). See the spreadsheet solution presented below. G. Adjust the enterprise value to determine Wok Yow’s equity value in dollars and on a per share basis at the end of 2013. The enterprise value of $233.6, minus the long-term debt value of $20, results in an equity value of $203.6. On a per share basis, the value is $20.36. See the spreadsheet solution presented below. H. Now, estimate Wok Yow’s after-tax cost of long-term debt. Use the risk free rate, the expected market risk premium, and the risk index for the Fine Furniture Company to estimate Wok Yow’s cost of equity capital. Determine Wok Yow’s weighted average cost of capital (WACC). Cost of common equity capital = 7% + (7.5%)2.00 = 7% + 15% = 22% After-tax cost of debt = 12%(1 - .40) = 7.2% WACC = 7.2%(.40) + 22%(.60) = 2.88% + 13.20% = 16.08% or 16.1% rounded. See the second spreadsheet solution presented below for answers to Parts H and I. I. Re-estimate Wok Yow’s enterprise value using the WACC calculated in Part H. Then, adjust the enterprise value to determine Wok Yow’s equity value in dollars and on a per share basis at the end of 2013. Revised terminal value = $447.1 [i.e., actually $45.152/(.161 - .06)] Revised enterprise value = $270.1 Revised equity value = $240.1 (i.e., $270.1 - $30.0) Revised per share value = $24.01 See the second spreadsheet solution presented below for answers to Parts H and I. WOK YOW IMPORTS, INC. Enterprise (Entity) Valuation Solution [Parts A through G]: Chapter 14 Mini Case Percent Change in Net Sales % of 33.3% 25.0% 20.0% 16.7% 6.0% [Thousands of Dollars] 2013 Actual Pro forma -------------------------------------------NOPAT Statements Sales 2013 2014 2015 2016 2017 2018 Net Sales 100.0% 150.0 200.0 250.0 300.0 350.0 371.0 Cost of Goods Sold -50.0% -75.0 -100.0 -125.0 -150.0 -175.0 -185.5 Gross Profit 50.0% 75.0 100.0 125.0 150.0 175.0 185.5 SG&A Expenses -20.0% -30.0 -40.0 -50.0 -60.0 -70.0 -74.2 Depreciation -5.0% -7.5 -10.0 -12.5 -15.0 -17.5 -18.6 EBIT 25.0% 37.5 50.0 62.5 75.0 87.5 92.8 Interest 0.0% 0.0 0.0 0.0 0.0 0.0 0.0 EBT 25.0% 37.5 50.0 62.5 75.0 87.5 92.8 Taxes (40% rate) -10.0% -15.0 -20.0 -25.0 -30.0 -35.0 -37.1 NOPAT 15.0% 22.5 30.0 37.5 45.0 52.5 55.7 Required Net Working Capital: Req Cash+Receivables+Inventories Minus: Payables+Accruals Req Net Working Capital (RNWC) Increase in RNWC Fixed Assets Schedule: Net Fixed Assets (NFA) Increase in NFA Plus: Depreciation CAPEX 33.3% -16.7% 50.0 -25.0 25.0 66.7 -33.3 33.3 8.3 83.3 -41.7 41.7 8.3 100.0 -50.0 50.0 8.3 116.7 -58.3 58.3 8.3 123.7 -61.8 61.8 3.5 33.3% 50.0 66.6 16.7 10.0 26.7 83.3 16.7 12.5 29.2 100.0 16.7 15.0 31.7 116.7 16.7 17.5 34.2 123.7 7.0 18.6 25.5 30.0 10.0 -26.7 -8.3 5.0 37.5 12.5 -29.2 -8.3 12.4 45.0 15.0 -31.7 -8.3 20.0 52.5 17.5 -34.2 -8.3 27.5 55.7 18.6 -25.5 -3.5 45.2 5.0 12.4 20.0 376.3 403.8 Free Cash Flows to Entity: NOPAT Plus: Depreciation Minus: CAPEX Minus: Increase in NWC Operating Free Cash Flows Terminal Value CF (r =.18, g =.06) Total Free Cash Flows (TFCF) PV of TFCF (18% Discount Rate) Less: LTD Value Equity Value Value Per Share (10,000 shares) $233.6 $30.0 $203.6 $20.36 WOK YOW IMPORTS, INC. Enterprise (Entity) Valuation Solution [Parts H and I]: [Thousands of Dollars] NOPAT Statements Net Sales Cost of Goods Sold Gross Profit SG&A Expenses Depreciation EBIT Interest EBT Taxes (40% rate) NOPAT Required Net Working Capital: Req Cash+Receivables+Inventories Minus: Payables+Accruals Req Net Working Capital (RNWC) Increase in RNWC Fixed Assets Schedule: Net Fixed Assets (NFA) Increase in NFA Plus: Depreciation CAPEX % of 2013 Sales 100.0% -50.0% 50.0% -20.0% -5.0% 25.0% 0.0% 25.0% -10.0% 15.0% Actual 2013 150.0 -75.0 75.0 -30.0 -7.5 37.5 0.0 37.5 -15.0 22.5 Cost of Capital Worksheet: "Comparable Firm" Risk Index Cost of Equity Capital A-T Cost of LTD Capital WACC 6.0% 2018 371.0 -185.5 185.5 -74.2 -18.6 92.8 0.0 92.8 -37.1 55.7 33.3% -16.7% 50.0 -25.0 25.0 66.7 -33.3 33.3 8.3 83.3 -41.7 41.7 8.3 100.0 -50.0 50.0 8.3 116.7 -58.3 58.3 8.3 123.7 -61.8 61.8 3.5 33.3% 50.0 66.6 16.7 10.0 26.7 83.3 16.7 12.5 29.2 100.0 16.7 15.0 31.7 116.7 16.7 17.5 34.2 123.7 7.0 18.6 25.5 30.0 10.0 -26.7 -8.3 5.0 37.5 12.5 -29.2 -8.3 12.4 45.0 15.0 -31.7 -8.3 20.0 52.5 17.5 -34.2 -8.3 27.5 55.7 18.6 -25.5 -3.5 45.2 5.0 12.4 20.0 447.1 474.6 Free Cash Flows to Entity: NOPAT Plus: Depreciation Minus: CAPEX Minus: Increase in NWC Operating Free Cash Flows Terminal Value CF (r = .161, g = .06) Total Free Cash Flows (TFCF) PV of TFCF (16.1% Discount Rate) Less: LTD Value Equity Value Value Per Share (10,000 shares) Percent Change in Net Sales 33.3% 25.0% 20.0% 16.7% Pro forma -------------------------------------------2014 2015 2016 2017 200.0 250.0 300.0 350.0 -100.0 -125.0 -150.0 -175.0 100.0 125.0 150.0 175.0 -40.0 -50.0 -60.0 -70.0 -10.0 -12.5 -15.0 -17.5 50.0 62.5 75.0 87.5 0.0 0.0 0.0 0.0 50.0 62.5 75.0 87.5 -20.0 -25.0 -30.0 -35.0 30.0 37.5 45.0 52.5 $270.1 $30.0 $240.1 $24.01 2.00 0.220 0.072 0.161 WOK YOW IMPORTS, INC. Enterprise (Entity) Valuation Solution [Parts A through G]: Chapter 14 Mini Case Percent Change in Net Sales % of 33,3% 25,0% 20,0% 16,7% 6,0% [Thousands of Dollars] 2016 Actual Pro forma -------------------------------------------NOPAT Statements Sales 2016 2017 2018 2019 2020 2021 Net Sales 100,0% 150,0 200,0 250,0 300,0 350,0 371,0 Cost of Goods Sold -50,0% -75,0 -100,0 -125,0 -150,0 -175,0 -185,5 Gross Profit 50,0% 75,0 100,0 125,0 150,0 175,0 185,5 SG&A Expenses -20,0% -30,0 -40,0 -50,0 -60,0 -70,0 -74,2 Depreciation -5,0% -7,5 -10,0 -12,5 -15,0 -17,5 -18,6 EBIT 25,0% 37,5 50,0 62,5 75,0 87,5 92,8 Interest 0,0% 0,0 0,0 0,0 0,0 0,0 0,0 EBT 25,0% 37,5 50,0 62,5 75,0 87,5 92,8 Taxes (40% rate) -10,0% -15,0 -20,0 -25,0 -30,0 -35,0 -37,1 NOPAT 15,0% 22,5 30,0 37,5 45,0 52,5 55,7 Required Net Working Capital: Req Cash+Receivables+Inventories Minus: Payables+Accruals Req Net Working Capital (RNWC) Increase in RNWC Fixed Assets Schedule: Net Fixed Assets (NFA) Increase in NFA Plus: Depreciation CAPEX 33,3% -16,7% 50,0 -25,0 25,0 66,7 -33,3 33,3 8,3 83,3 -41,7 41,7 8,3 100,0 -50,0 50,0 8,3 116,7 -58,3 58,3 8,3 123,7 -61,8 61,8 3,5 33,3% 50,0 66,6 16,7 10,0 26,7 83,3 16,7 12,5 29,2 100,0 16,7 15,0 31,7 116,7 16,7 17,5 34,2 123,7 7,0 18,6 25,5 30,0 10,0 -26,7 -8,3 5,0 37,5 12,5 -29,2 -8,3 12,4 45,0 15,0 -31,7 -8,3 20,0 52,5 17,5 -34,2 -8,3 27,5 55,7 18,6 -25,5 -3,5 45,2 5,0 12,4 20,0 376,3 403,8 Free Cash Flows to Entity: NOPAT Plus: Depreciation Minus: CAPEX Minus: Increase in NWC Operating Free Cash Flows Terminal Value CF (r =.18, g =.06) Total Free Cash Flows (TFCF) PV of TFCF (18% Discount Rate) Less: LTD Value Equity Value Value Per Share (10,000 shares) $233,6 $30,0 $203,6 $20,36 Percent Change in Net Sales 33,3% 25,0% 20,0% 16,7% Percent of Net Sales 2016 2017 2018 2019 2020 100,0% 100,0% 100,0% 100,0% 100,0% -50,0% -50,0% -50,0% -50,0% -50,0% 50,0% 50,0% 50,0% 50,0% 50,0% -20,0% -20,0% -20,0% -20,0% -20,0% -5,0% -5,0% -5,0% -5,0% -5,0% 25,0% 25,0% 25,0% 25,0% 25,0% 0,0% 0,0% 0,0% 0,0% 0,0% 25,0% 25,0% 25,0% 25,0% 25,0% -10,0% -10,0% -10,0% -10,0% -10,0% 15,0% 15,0% 15,0% 15,0% 15,0% WOK YOW IMPORTS, INC. Enterprise (Entity) Valuation Solution [Parts H and I]: [Thousands of Dollars] NOPAT Statements Net Sales Cost of Goods Sold Gross Profit SG&A Expenses Depreciation EBIT Interest EBT Taxes (40% rate) NOPAT Required Net Working Capital: Req Cash+Receivables+Inventories Minus: Payables+Accruals Req Net Working Capital (RNWC) Increase in RNWC Fixed Assets Schedule: Net Fixed Assets (NFA) Increase in NFA Plus: Depreciation CAPEX % of 2016 Sales 100,0% -50,0% 50,0% -20,0% -5,0% 25,0% 0,0% 25,0% -10,0% 15,0% Actual 2016 150,0 -75,0 75,0 -30,0 -7,5 37,5 0,0 37,5 -15,0 22,5 33,3% -16,7% 50,0 -25,0 25,0 66,7 -33,3 33,3 8,3 83,3 -41,7 41,7 8,3 100,0 -50,0 50,0 8,3 116,7 -58,3 58,3 8,3 33,3% 50,0 66,6 16,7 10,0 26,7 83,3 16,7 12,5 29,2 100,0 16,7 15,0 31,7 116,7 16,7 17,5 34,2 30,0 10,0 -26,7 -8,3 5,0 37,5 12,5 -29,2 -8,3 12,4 45,0 15,0 -31,7 -8,3 20,0 52,5 17,5 -34,2 -8,3 27,5 5,0 12,4 20,0 447,1 474,6 Free Cash Flows to Entity: NOPAT Plus: Depreciation Minus: CAPEX Minus: Increase in NWC Operating Free Cash Flows Terminal Value CF (r = .161, g = .06) Total Free Cash Flows (TFCF) PV of TFCF (16.1% Discount Rate) Less: LTD Value Equity Value Value Per Share (10,000 shares) Cost of Capital Worksheet: "Comparable Firm" Risk Index Cost of Equity Capital A-T Cost of LTD Capital WACC Percent Change in Net Sales 33,3% 25,0% 20,0% 16,7% Pro forma -------------------------------------------2017 2018 2019 2020 200,0 250,0 300,0 350,0 -100,0 -125,0 -150,0 -175,0 100,0 125,0 150,0 175,0 -40,0 -50,0 -60,0 -70,0 -10,0 -12,5 -15,0 -17,5 50,0 62,5 75,0 87,5 0,0 0,0 0,0 0,0 50,0 62,5 75,0 87,5 -20,0 -25,0 -30,0 -35,0 30,0 37,5 45,0 52,5 $270,1 $30,0 $240,1 $24,01 2,00 0,220 0,072 0,161 6,0% ---------------------2021 371,0 -185,5 185,5 -74,2 -18,6 92,8 0,0 92,8 -37,1 55,7 123,7 -61,8 61,8 3,5 123,7 7,0 18,6 25,5 55,7 18,6 -25,5 -3,5 45,2 Percent Change in Net Sales 33,3% 25,0% Percent of Net Sales 2016 2017 2018 100,0% 100,0% 100,0% -50,0% -50,0% -50,0% 50,0% 50,0% 50,0% -20,0% -20,0% -20,0% -5,0% -5,0% -5,0% 25,0% 25,0% 25,0% 0,0% 0,0% 0,0% 25,0% 25,0% 25,0% -10,0% -10,0% -10,0% 15,0% 15,0% 15,0% 20,0% 16,7% 2019 100,0% -50,0% 50,0% -20,0% -5,0% 25,0% 0,0% 25,0% -10,0% 15,0% 2020 100,0% -50,0% 50,0% -20,0% -5,0% 25,0% 0,0% 25,0% -10,0% 15,0% Instructions for FIN 4305 Project: This is a group project but you have the option to work alone if you choose to. I recommend group work since students learn from each other as they collaborate and the quality of the work tends to be better as a result. You can form groups of up to 3 people. The “project discussion forum” under Blackboard can be used to form groups with other students. Once you form your group, you need to sign up as a group under "groups" section under "course tools" in Blackboard. Even if you are planning to do the project alone, you still need to do this because the project submission link will only be available to students who sign up. This project is based on the WOK YOW IMPORTS, INC. case found in your textbook under Chapter 14. Please read the case carefully. An excel spreadsheet is provided that has the solution to the case along with suggested answers to the case questions. Please go over the excel sheet first and make sure that you can follow the calculations for the discounted cash flow valuation method (enterprise valuation method). All the formulas are built into the excel file so when you go over a cell, you can track how the calculation for each number on the sheet is done. Pay special attention to the financial ratios, growth assumptions, calculation of the free cash flows for the foreseeable future (5 years) and the calculation of the terminal value. These future free cash flows are then discounted back to time zero using the discount rate (WACC). Once you feel comfortable on the model and the solutions to the case, I want you to answer the following questions to complete your project assignment: Using the provided financial statements as a starting point: 1. The DCF valuation and pro forma financials with five years of forecasted growth rates are provided in the original model. Please modify the model to consider a more successful scenario where Wok Yow’s sales grow at a more aggressive pace of 40% for five years and then flatten to a more sustainable growth rate of 7%. What would the stock value per share be under the new scenario? What kind of strategic changes you would make in the business model to justify the growth assumption? How would you do things differently? You can use fictional events to justify your assumptions. 2. Prepare and present DCF valuations and pro forma financial statements (five-year explicit period) that justify a $31 and a $56 share price. You can play with the model assumptions to get to these valuations. Propose two different business plans that would be targeting these two different outcomes. Make sure the ratios embedded in your projections conform to reasonable operating ratio assumptions in the models. Also remember that higher risk business strategies come with higher expected returns. 3. Discuss the $31 and $56 IPO prices for Wok Yow within the context of comparable firms and their multiples. You can use some outside reference materials from companies in similar industries for comparison purposes. Then take a position on whether you would recommend the $31 or $56 IPO. Which one is more feasible? Take a position on which of the two business plans you would invest in as an investor, which financial instrument of the company you would want to invest in and what kind of a return you would expect on your money. 4. Prepare an executive summary discussing the events and decisions leading to its current situation, the options it currently has moving forward, and your recommendations for Wok Yow’s near future. The events in the summary will be fictional. Guidelines: • Please copy and paste the original excel sheet into a new worksheet and make your changes on the new one for each of the models you are working on. I want to be able to follow your calculations relating to the answers to each question on a different excel worksheet in the same file. Highlight the changes that you make on the model so that your changes can be traced easily. • You will need to make changes on the company’s operations in order to increase growth rate to 50%, and in order to justify the $31 and $56 stock valuations. In all cases be sure to explain your modeling assumptions on operating and financing of the venture and provide a summary of the four scenarios. • Your grade on the project will be based on the accuracy of your calculations and more importantly, on the coherent nature of the assumptions of different strategies and their likely effect on the future projections of the financial model. For example, a more efficient inventory management system would decrease the company’s NOWC and would free up cash flow, increasing the value of the firm. I would want to see that the “improved inventory management” is mentioned in the business strategy and also the required NOWC amounts lowered in the financial model, leading to a greater company valuation. If a strategy is mentioned but its financial implications cannot be traced to the financial model, you will loose points. • The work can be completed in a single excel file with multiple worksheets. Your answers to the 4 questions can be on one worksheet and you can reference your answers to each question the corresponding worksheet that has the relevant model. This would make it easier for me to track your answers and your financial models. ...
Student has agreed that all tutoring, explanations, and answers provided by the tutor will be used to help in the learning process and in accordance with Studypool's honor code & terms of service.

This question has not been answered.

Create a free account to get help with this and any other question!

Brown University





1271 Tutors

California Institute of Technology




2131 Tutors

Carnegie Mellon University




982 Tutors

Columbia University





1256 Tutors

Dartmouth University





2113 Tutors

Emory University





2279 Tutors

Harvard University





599 Tutors

Massachusetts Institute of Technology



2319 Tutors

New York University





1645 Tutors

Notre Dam University





1911 Tutors

Oklahoma University





2122 Tutors

Pennsylvania State University





932 Tutors

Princeton University





1211 Tutors

Stanford University





983 Tutors

University of California





1282 Tutors

Oxford University





123 Tutors

Yale University





2325 Tutors