Business Analysis Using Financial Statements (ACCT 503b) Fall 2022 Assignment #1 Projections Please submit this assignment to Canvas. Each team must submit a 1-page memo outlining their income/cash projection assumptions. Please also attach spreadsheet(s) that summarize your projection model and record the names of all your group members on the first page of the assignment. Please be concise and organized in your answers. Recommended Steps: 1. Download the DCF valuation Excel spreadsheet from Canvas (filename = "Kroger_DCF"). This spreadsheet contains discounted FCFE template with data for Kroger. Paste the historical data for your firm into this spreadsheet to begin your analysis. 2. To obtain the historical data for your company, go to wrds-www.wharton.upenn.edu and log in. The username and Password are on Canvas. Step 1: Choose your date range. - Select "Data Year - Fiscal" - Select 2017-01 to 2022-09. You might have to go into 2022 to get the latest fiscal year. After completing the process, check your data to ensure it includes a row for "fyear" 2021. If you download six years of data, use only the latest five years when you paste your data into the Kroger spreadsheet; otherwise, the spreadsheet will give incorrect results. Step 2: Apply your company codes. Choose search by TIC. Enter your company's ticker symbol (e.g., MSFT for Microsoft) in the box above, Please enter company codes separated by a space. Step 3: Choose variable types. - Under Select Variable Types, select Data Items (It should already be selected with a blue/white check next to it.) - Under Search All 0/974 (click the tab), select o Company Name (CONM) o Ticker Symbol (TIC) 3. Click on Home in the upper-left corner. Under Subscriptions click >> Compustat - Capital IQ. On the screen's left side, under "Compustat," select >> North America. Then click on >>Fundamentals Annual. 4. On the Compustat Daily Updates - Fundamentals Annual page, please do the following: 1 o SIC -- Standard Industry Classification Code (SIC) - Using the slide bar under the blue tabs, scroll over to Balance Sheet Items 0/382, scroll down and select the circles by the following items: ACT -- Current Assets Total (ACT) AT -- Assets Total (AT) CHE -- Cash and Short-Term Investments (CHE) DLC -- Debt in Current Liabilities Total (DLC) DLTT -- Long-Term Debt Total (DLTT) IVAO -- Investments and Advances Other (IVAO) LCT -- Current Liabilities Total (LCT) LT -- Liabilities Total (LT) SEQ -- Stockholders' Equity Total (SEQ) - Under Income Statement Items 0/328 tab, scroll down and select the check box by the following items: IB = Income Before Extraordinary Items (IB) IDIT = Interest and Related Income Total (IDIT) NI = Net Income (Loss) (NI) PI = Pretax Income (PI) SALE = Sales/Turnover (Net) (SALE) TXT = Income Taxes Total (TXT) XINT = Interest and Related Expense Total (XINT) - Under Miscellaneous items 0/114, scroll down and select the check box by the following items: CSHO -- Common Shares Outstanding (CSHO) Step 4: Select query output. - select "comma-delimited text ( .csv)" under Output Format. Please double-check that all and only the listed items are chosen. Otherwise, the spreadsheet will place numbers in incorrect places. 5. Scroll down and CLICK on the "Submit Form" rectangle. The data will take about 30 seconds to download. Left click on the output file, a series of characters followed by ".csv" (e.g., ~/wrds_query_output/vhnf1c4bmo9rqgn8.csv). Left-click on the output file, and when it appears in the lower-left-hand corner of the Chrome browser, click on the downward arrow and choose open. On some browsers, right-click on the output file, and when the menu appears, move the cursor down to "Save Target As..." and left-click. Save in a "Microsoft Excel Worksheet" file. 6. Open this data spreadsheet and copy that financial statement data for your company from it to the Excel Spreadsheet template listed in Step 1 (above). 2 7. Ensure that the dates and data item names match those in the Kroger template spreadsheet and paste the data spreadsheet into the Kroger template spreadsheet. (While you have all the data items selected, go back to Step 2 and substitute the SIC code for your company to pull down data for the last five years for all companies in your firm's industry. You should see the 4-digit SIC code in the last column of your company's data.) 8. You are now ready to calculate FCFEs for your company using historical data as your guide! GO TO THE NEXT PAGE FOR QUESTIONS. 3 Questions (Key projected concepts are in bold: spend your time on these) This assignment asks for initial answers to these questions. The objective is to get your group started on thinking about these issues. The rest of the semester, you will collect information, read, and think so that you can provide answers with greater depth and understanding of the company, its industry, the future opportunities and problems that are likely to arise, and whether the company can add value. I hope in a month, your answers will be more insightful. 1) Using historical trends for your firm and its industry, project sales for the next five years (years 1-5), then years 6-10 (possibly using a growth rate that gradually declines to the long-term sales trend), and then the long-term sales trend in years 11 and beyond. a. Start with simply projecting the current year's numbers. b. Do these (historical) numbers contain unusual items? How do they compare with other prior years? How do they compare with the industry? c. In projecting sales, comment on the size of the potential market (e.g., Do demographic trends suggest it will grow? What is the company's current market share? Is any data available from industry associations that can aid this analysis?) d. If current growth is high, you might have to extend the forecast horizon to 15-20 years before sales can be expected to normalize (i.e., grow at the rate of GNP, 2.5%). e. Discuss the assumed sales growth in perpetuity. What rate is appropriate? 2) Again, using historical trends for your firm and its industry, project the NOPAT margins your firm will generate on its sales. a. Start with simply projecting the current year's numbers. b. Do current-year numbers contain unusual items? How do they compare with other prior years? Why do they differ? How do they compare with the industry? Does your company have a differentiated product or service? What is the source of this advantage and higher margin? Will this advantage persist? c. Think about specifics such as COGS and SG&A issues. d. Consider issues related to economies of scale, operational bottlenecks, technological change, future expected depreciation, competition, etc. 3) Perform a similar analysis for asset turnover assumptions. a. Think about the company's working-capital needs. Will they grow proportionately to sales? Will there be economies of scale or efficiency increases? b. Will the company require similar levels of fixed assets as sales grow? c. How do working-capital and net-long-term asset turnovers compare with prior years? How do they compare with the industry? Is your company more efficient? Less? Why? In thinking about 1), 2), and 3), ask yourself, "What is my company good at?" The advantages over competitors in sales growth, margins, or asset turnovers, should be consistent with your answer to this question. This assignment is only a start. Its objective is to prompt your group to begin discussing and thinking about the answer to these 4 questions as precisely as possible and translating this answer into 1), 2), and 3). This process is not easy. Learn more about your company, its industry, and how it operates. 4) Leverage should generally be held constant over the forecast horizon so that the current cost of debt and equity numbers can be applied. Does the calculated effective interest rate make sense, given the cost of equity and the company's default risk? Is it positive? 5) Compare your present value to today's price. 5