Professor Crocker H. Liu Revised: March 3, 2004 Financial Management Percentage of Sales Forecasting Method Objective: The objective of this assignment is to forecast the income statement and balance sheet and future financing requirements using an Excel spreadsheet. The percentage of sales method, which relates various (but not all) financial statement line items as a percentage of net sales, is the technique used. Company: Williams-Sonoma, Inc. (ticker WSM) is a nationwide specialty retailer of high quality products for the home. These products, representing seven distinct merchandise strategies, Williams-Sonoma, Pottery Barn, Pottery Barn Kids, PBTeen, Hold Everything, West Elm and Chambers, are marketed through 523 stores, eight mail order catalogs and five e- commerce web sites (see for example http://www.williams- sonoma.com). Their business concepts are designed to cover every conceivable room in the house from the kitchen to the living room, bedroom, home office, and even the hall closet. The company was founded b homebuilder, who in 1947 arrived in and owned a hardware store th cookware shop. On January 8, 2004, WSM guidance for the 52 weeks endin (Fiscal Year 2003)1. Net revenues in the range of $2.742 billion to represents revenue growth in the 16.9% versus fiscal year 2002. percentage of net revenues is projected to be in the range o unchanged from previous guidance. Selling, general and administr percentage of net revenues are projected to be in the range Depreciation and amortization expense for fiscal year 2003 approximately $100 million. Diluted earnings per share for fiscal yea to be in the range of $1.26 to $1.31. Competitors (Peer Group): Bed Bath and Beyond (BBBY), Bom Plus (CPWM), and Pier 1 Imports (PIR). 1 Source: http://biz.yahoo.com/bw/040108/85263_1.html 1 y Chuck Williams, a Sonoma, California en started a small announced revised g February 1, 2004 are projected to be $2.760 billion which range of 16.1% to Gross margin as a f 40.2% to 40.3%, ative expenses as a of 31.0% to 31.1%. is projected to be r 2003 are projected bay Co (BBA), Cost Assignment: Download the spreadsheet for Williams-Sonoma (WSM) and use the data in the workbook together with the assumptions at the end of this mini-case to answer the following questions. 1. Using the Peer Group Financial Stmt worksheet and the financial statements provided for the competitors of WSM, construct Peer Group aggregates for various line items in income statement and balance sheet by filling in the area highlighted in yellow. (10 points) 2. Using the results that you obtained in the preceding question and the Margin Analysis worksheet, perform a margin analysis e.g., calculate the appropriate ratios for the years given by filling in the area highlighted in yellow for Williams-Sonoma and the Peer Group. (5 points) 3. Calculate the return on invested capital for WSM and the Peer Group using the ROIC Margin Analysis worksheet. Discuss whether WSM’s management is doing a good job in terms of efficiently and effectively managing their income statement and balance sheet relative to its peers. Since you may want to use this case in the interviewing process, make sure to write the way that analysts write. For a sample of their writing style, please visit Investext by logging on to our NYU Intranet at http://www.nyu.edu/library/bobst/vbl/. (10 points) 4. Go to the PWC Edgarscan (http://edgarscan.pwcglobal.com/servlets/edgarscan) website (http://www.wsrn.com) and access the information page for WSM by entering the company name williams sonoma and then clicking on the SEARCH button. After you have accessed the WSM information page, click on 10-K 2003-02-02 under the Annual Filings (10-K's by Filing Period): section of the webpage. Go to the 10K for WSM and print out the page in the 10K that contains the Aggregate Contractual Obligations (maturity of debt for WSM). The information should match that in your “WSM Debt Maturity Schedule” worksheet. (5 Points) 5. Using the worksheet labeled "2. Forecast FinStmt (Template)", please complete the following a. Fill in the numbers for 2/2/2003 and also the assumptions (see the last page of this handout for the forecasting assumptions). The area to be filled in is highlighted in yellow. Next, forecast the income statement and balance sheet for 2/2/2004 and 2/2/2005 using the assumptions given in conjunction with the numbers for 2/2/2003 and also the information contained in the “WSM Debt Maturity Schedule” worksheet. In setting up your worksheet, assume that WSM will maintain a 2.5% debt-to-total capital ratio (assume that this is their target capital structure). (10 points) 2 b. After you have finished forecasting net income, calculate the basic and diluted earnings per share (EPS) for WSM. How close are your diluted EPS estimates to those of Wall Street analysts using the “Analyst EPS” worksheet provided? Is your diluted EPS for FY2003 (2/2/2004) within the range that the company is expecting, namely between $1.26 to $1.31? (50 points) c. Given your forecasted FY2003 and 2004 diluted EPS, use the Data Table command in Excel to perform EPS sensitivity analysis based on changes in the growth rate in sales. Please round your answer to two decimal places e.g., $1.03. (Warning: If you are still in the Manual Calculation mode in Excel, you will need to use the F9 key in updating your calculations) (10 points) Please hand in a hardcopy of your answers together with a disk containing all of your calculations. This is an individual assignment. Anyone caught cheating will be given an automatic F on this project. 3 Assumptions Used in Forecasting Financial Statements: Line Item Assumption Sales Wall Street analysts expect sales for WSM to grow at 16.5% in 2/2/2004 (fiscal year 2003) and at 14.5% in 2/2/2005 (fiscal year 2004). Cost of Goods Sold (COGs) (excluding Depreciation and Amortization) According to WSM's revised guidance, gross margin (Note: WSM includes depreciation and amortization in their COGS and hence their expected gross margin) is projected to be around 40.2%. Selling, General & Admin (SGA) According to WSM’s revised guidance, selling, general and administrative expenses as a percentage of net revenues are projected to be in the range of 31.0% to 31.1%. As such, set SGA/Sales = 31%. Depreciation and Amortization Use the Depreciation & Amortization/Net Sales ratio for 2/2/2003 for WSM from the “Margin Analysis” worksheet. Interest rate on existing debt WSM's unsecured senior notes are due in August 2005 with interest payable semi-annually at 7.2% per annum (Source: WSM 10K FY2002). Total debt = Notes Payable + Current LT Debt + Current Portion of Capital Leases + Mortgages + Convertible Debt + LT Debt + LT Capital Leases. Interest rate on new debt As of 3/1/2004 using the yield on a 10 year Treasury Bond (3.99%) + default spread on AA+ (0.41%), WSM pre-tax cost of debt is 4.3% using Altman EMS Score model Interest rate on Cash and Marketable Securities According WSM's 10K for FY2003, "Our policy is to invest in high-quality, short-term instruments to achieve maximum yield while maintaining a level of liquidity consistent with our needs. We will assume that the interest rate is equal to a 3-month yield on Treasuries. As of 3/1/2004, the 3-month Tbill yield was .95% Marginal tax rate Use the Tax Expense/Income before Taxes ratio for 2/2/2003 for WSM from the “Margin Analysis” worksheet. Debt Maturing in fiscal year 2003 and in fiscal year 2004 ($dollar amount in 000s) Please refer to the “WSM Debt Maturity Schedule” worksheet for the aggregate maturities of long-term debt for Williams Sonoma 4 Line Item Assumption Total Debt/(Total Debt + Shareholder Equity) WSM's Debt to Total capital ratio has decreased by 67.5% from the previous year. If this trend continues, WSM's ratio should equal that of its Peer Group (at approximately 2.5%). This means that Debt = (.025/.975)*Equity.2 Cash and marketable securities Is the "Plug" e.g. the balance sheet item that "closes" the model. In other words, it makes Assets = Liabilities + Equity. To obtain the amount of cash and equivalents (the plug), since Total Assets = Total Liabilities + Equity, it follows that Cash & Marketable Securities = Total Liabilities + Equity - Current Assets (excluding cash & marketable securities) - Net Property, Plant & Equipment - Intangibles - Other Long Term Assets. Current Assets (excluding cash and marketable securities) Use the 2/2/2003 Current Assets excluding cash & marketable securities/Net Sales ratio from the “PCT of Sales (Disclosure)” worksheet for WSM. Net Property, Plant & Equipment Use the 2/2/2003 Net PP&E/Net Sales ratio from the “PCT of Sales (Disclosure)” worksheet.3 Intangibles Remains constant at the 2/2/2003 level (see WSM 10K) in forecast period. Other Long Term Assets Remains constant at the 2/2/2003 level (see WSM 10K) in forecast period. Includes Investments & Advances to Subsidiaries, Other Non-Current Assets, Deferred Charges (that are Assets), as well as Deposits & Other Assets. Current Liabilities Use the 2/2/2003 Total Current Liabilities (excluding ST Debt)/Net Sales ratio from the “Margin Analysis” worksheet. Short term (ST) debt = Notes Payable + Current LT Debt + Current Portion of Capital Leases 2 Debt = .025*(Debt + Equity) = .025*Debt + .025*Equity ⇒ Debt - .025*Debt = .025*Equity ⇒ .975*Debt = .025*Equity ⇒ Debt = (.025/.975)*Equity = .0256*Equity 3In actual practice, there may be a lag between Net PP&E and Sales depending on the nature of the industry. One way to detect this is by looking at the ratio of contemporaneous and lagged Net PP&E in relation to Sales ratio. 5 6 Line Item Assumption Other Long Term Liabilities Remains constant at the 2/2/2003 level (see WSM 10K) in forecast period. Includes Deferred Charges (in Liabilities section), Other LT Liabilities, and Minority Interest (if any). Dividends Use the 2/2/2003 Dividend/Net Income ratio from the “Margin Analysis” worksheet. This ratio is also known as the Payout ratio = dividends/net income. What isn't paid out as dividends is added to retained earnings. Note: If there is a NA in a particular cell of your data spreadsheet, set it equal to zero e.g., NA = 0.