Your course project involves the financial analysis and business analysis of Ruby Tuesday (RT) and Chipotle Mexican Grill (CMG), a casual dining chain and a fast casual dining chain, respectively. You will analyze the companies from the point of view of a potential investor. For your individual ratio analysis, you will use Excel to compute the last three years (years ending 5-Jun-07, 6-Jun-06, and 31-May-05 for RT; years ending 31-Dec-06, 31-Dec-05, and 31-Dec-04 for CMG ) of ratios, then provide a brief comment on the time series of each ratio. As an individual you can pick which one of these two restaurants for which to do your individual ratio analysis. As a group, you will need to have both companies' ratios calculated for all three years, and your group will find a compelling reason why *not* to invest in either RT or CMG (you can pick which one to decline investing in) now (given a 5-year expected holding period for your investment), based partly on financial ratio analysis and partly on all other business factors: general management, strategy, competition, the economy, marketing, human resource management, ethics, or other factors. You will need to determine the differences between the strategies of the two chains in order to complete your analysis.
Individual ratios, done in excel, are due WEDNESDAY 2/20, at the beginning of class.
• Getting the financial data into Excel: You can use Mergent Online, Hoover's, or http://finance.yahoo.com. The latter instructions are for http://finance.yahoo.com:
-Once you enter the stock symbol RT or CMG, you will be taken to the RT or CMG information page.
-Scroll down, in the left margin, until you see Financial Data. Click on Balance Sheet.
-Once it is open, make sure you have "Annual" selected. Right-click anywhere in the balance sheet.
-Then click on "Export to Microsoft Excel". This will open Excel with a sheet including the company's balance sheet.
-Go back to the Yahoo page and do the same on Income Statement and Cash flows.
-Then, copy and paste the Income Statement and the Statements of Cash Flow to the same file in which you have the Balance Sheet (put each type of statement in a different tab).
-Add a tab for Ratios, then do your ratios in this worksheet.
• Excel Requirement: You will do your three years of ratio calculations on one of these restaurant chains in an EXCEL worksheet and use cell references to calculate your ratios. You will turn in both the values and the cell formulas.
Values: Print out your worksheet.
Cell Formulas: Change the display in Excel from values to formulas by holding down the CTRL key and pressing ~. The ~key is just below the ESC key on the left side of the keyboard. You may need to adjust the column widths before printing out your cell formulas (you may also have to change to landscape mode in you page setup). Once printed, change the screen back to values by again pressing CTRL~.
To get the lease payments and also to get the number of common shares outstanding, most likely you will need to get RT's or CMG's most recent 10-K report (annual report with supplemental information) and maybe the one just prior to that. You may get these by going to the SEC's EDGAR web site: http://www.sec.gov/edgar/searchedgar/companysearch.html. Using this source, I find RT's 2007 fiscal year report at:
http://www.sec.gov/Archives/edgar/data/68270/000006827007000069/form10-kdraft.htm
You will also want to refer to Ruby Tuesday's or Chipotle's investor web sites and the annual statements there:
Ruby Tuesday:
http://phx.corporate-ir.net/phoenix.zhtml?c=83799&p=irol-IRHome
(see Annual Reports link)
Chipotle:
http://phx.corporate-ir.net/phoenix.zhtml?c=194775&p=irol-irhome
(see Annual Reports link and SEC filings link)
* If the 10-K is in PDF, it will open in Adobe Reader. If the 10-K is in text format or RTF format, open up WORD, and then the downloaded document (or you can use select all, then copy, then paste the text into your WORD document). Do not print it out--it is over 100 pages.
* After opening it in WORD, you can then search for what you need by using CTRL-F, once the 10-K is open in WORD, to find lease amounts (use lease as a search word);
* In the 10-K, if you do not find historic stock prices for your P/E and M/B ratios:
To get the exact price for the day on which the fiscal year ends, try http://finance.yahoo.com/q/hp?s=RT or http://finance.yahoo.com/q/hp?s=CMG (you will have to enter the date range).
* For number of common stock shares outstanding, search the 10-K report (see above).
• For principal payments in the fixed charges coverage ratio, use the balance sheet entry titled "Short-term and Current Long-term Debt", which you will find on your balance sheets.
• If you have any questions on any ratio, please see "Answers to
Frequently-Asked Questions" in the next section of this sheet.
Turn in three years of ratios and a column of evaluation comments, identical to
that shown in Table 2.8, pp. 66-67, near the end of Chapter 2. Do this for
either RT or CMG. You do not need industry
averages for the individual assignment, so don't concern yourself with
cross-sectional or combined analysis until you get going on the group project. Remember to use CTRL and ~ to view
the cell formulas, then print those out. Turn in both a printout of your
values and a printout of your cell formulas at the beginning of class on
Wednesday, 2/20.
Note: If you haven't already, you may get either of the companies' 10-K reports to search for company info using the links above.
1- You can then use CTRL-F, once the 10-K is open in WORD, to find lease amounts (use lease as a search word).
2-
To get the exact price for the day on which the fiscal year ends, try
http://finance.yahoo.com/q/hp?s=RT
or
http://finance.yahoo.com/q/hp?s=CMG
(you will have to enter the date range).
Change the start and end dates to be the same day as the last day in the fiscal
year (e.g., June 7, 2007).
3- For number of common stock shares, search the 10-K report (see above).
2006's or 2007's 10-K should have 2005 and 2006 (or 2007) shares outstanding.
You may also have to go back to the Yahoo
website to get an older 10-K report for the very first year of data (earliest fiscal year).
Or, you may get it from the company's website under the
Investors or under Investor
Relations areas.
4- For principal payments in the fixed charges coverage ratio, use the entry titled "Short-term and Current Long-term Debt", which you should be able to find on your balance sheets.
5- For Common Equity, use Total Stockholder's Equity as long as there is no preferred stock.
6- For your DuPont charts (template passed out in class), your individual expense amounts in the boxes may not add to total expense (but use the income statement total expense on the DuPont chart), and your individual asset amounts in the boxes may not add to total assets (but use total assets from the balance sheet for the total box).
7 - For purchases in the average payment period, just use COGS (since we have no way of knowing what % of COGS purchases represent).
8 – When doing ratios, make sure you are using annual data, not quarterly data.
9 - Yes, “net receivables” is the same as Accounts Receivable, net.
10 - (Group Project) You may not use any industry averages that require someone to pay for them (you may only use those available free to anyone).
11 - (Group Project) No, you do not need a bibliography. However, I do need to know where you got your industry averages, and make sure to footnote reference any outside info you are using in the body of your report.
12 - Should I use "net income applicable to common shares" as my earnings available to common shareholders? Yes. If that number is negative, the only ratio for which one should put "NA" instead of the calculated number would be the P/E ratio (we don't report a P/E ratio if it would be negative, with the idea that price is always positive and we are reporting with P/E the multiple of $1 of earnings one is willing to pay for the stock). When doing EPS, use net income, fully diluted, before extraordinary items or accounting changes.
13 - Yes, "Cost of Revenues" is equivalent to "Cost of Goods Sold".
14 - For the tax rate for each year, calculate the average tax rate as we did in
Chapter 1.
The group presentations/group paper due date is listed on your game plan (Course Schedule
= Wednesday, 3/26 all papers and PPTs due;
some presentations on
Wednesday, 3/26 and the
rest on Friday, 3/28).
Your group will turn in one paper and do a PowerPoint presentation to the class. See below and the other pages of this document for more on that aspect of the project.
The requirements are as follows:
• Type your group project write-up, double-spaced. Spell-check it and have someone proofread for grammar before turning it in to me. I recommend that you finish your paper early and visit the Writing Center. I do take off points for incorrectly spelled words and grammar errors.
• Length of the written part of the paper (not including exhibits such as ratio calculations or the financial reports you download) should be
10-13 pages, not including the one-page Executive Summary at the beginning of
your report. In no case should it be more than 13 pages, not including in-text charts/graphs/tables and end-of-paper exhibits.
• Format: Included each piece of information (such as balance sheets) you used that you got using Internet Explorer as an appendix.
• As one of your exhibits, revise the individual ratios worksheet to add columns
for industry values, another column for cross-sectional evaluation comment, and
another column for combined evaluation (as the example in Chapter 2 shows).
• Make sure your analysis of the financial leverage multiplier (FLM) is
consistent with your financial leverage/debt ratios section, since the FLM is a
debt ratio.
• Use sources beyond your book and notes to help you understand the P/E and M/B
ratios and to help you do your interpretation of them..
You will be doing one group report and presentation. Each student will be speaking for part of your presentation. Based on the work done from the very start of the course project through the oral presentation, each of the group members will be evaluating their own and your contribution to all the work each of you has done, right from the very first group meeting. You and your fellow group members will be evaluated based on the quality and quantity of all group members' contributions.
Presentation: You will have 13 minutes for your group presentation. I will call “stop” at 14 minutes, so do not go over. However, I would not expect a group to use less than 12 minutes, either. Try to divide the presentation up so that each person speaks about 2-3 minutes. Come dressed up in business attire (coat & tie for guys, business attire for gals). The audience (your fellow students serve as the board of directors) and I will ask several questions after your presentation. Each student must be prepared to answer any question that may arise (so each of you should be very familiar with every part of the analysis). Your group and individual grades will relate partly to how well you field questions. Presentations must use PowerPoint. Print out handouts (6 to a page, pure black & white) and bring copies for me and each student, which you will pass out as you begin your oral presentation (or provide it to me a day early and I will get your copies for you; otherwise, your group is responsible for making all necessary copies).
Paper: Your course project paper, done in WORD, and based on ratios done in Excel. It is due at the time of your presentation. It should be no more than 10-13 pages, not including the one-page Executive Summary and the exhibits such as graphs, tables and charts.
Presentation & paper should focus on the following items:
1. (Briefly) Why are investments analysts
so "low" or so “high” on each of these companies? What did you find in your ratio analysis that might support this optimism
or pessimism?
2. If analysts are optimistic, what are the “cracks in the wall” (fault lines) that make this optimism dangerous now? Include management (including business product/market strategy and competitive forces), marketing, production/operations management, human resource management, ethical, as well as financial aspects.
Or, if pessimism rules, what might the company do to engineer a "turnaround"? A SWOT analysis
is essential here
and needs to be used
(use the front-and-back handout given in class from Kerin/Peterson book), or you can try to link everything to the DuPont chart or modified DuPont formula.
Remember, strengths and weaknesses are internal to the organization, and
opportunities and strengths are external trends and factors that will
affect the organization and its prospects.
3. If analysts are optimistic, what are the “cracks in the wall” (fault lines) that make this optimism dangerous for investors who are holding the stock and then selling it 5 years from now? Consider the same aspects detailed in #2.
If pessimism reigns, what are the "hope lines" that can bring a resurgence in
this company's fortunes?
4. OPTIONAL (bonus): As part of #1, #2, and #3, what do the industry standard ratio values tell you? (Don’t limit your analysis to just trend or time-series analysis.)
5. For each company, make sure in your time-series analysis to address all 3 drivers in your DuPont analysis, including how they explain the time-series changes in ROE and the “versus industry” ROE comparison.
There are two aspects to the DuPont drivers analysis: one comparison is a
time series comparison to the company's earlier years (REQUIRED for both
companies), the other comparison is
to the industry values for the drivers (OPTIONAL, bonus).
Remember to use the Statement of Cash Flows diagnostic handout to help on your liquidity analysis
for each company. You must study the Statement of Cash Flows to see which pattern each year follows (e.g., +/+/+). Use the text at the bottom of your diagnostic handout to help you in the write-up of each year's cash flow pattern. Study the operating cash flows for the largest entries (largest dollar amounts), and comment on them. If you can find data on unused short-term borrowing capacity (probably by searching the 10-K report), comment on that as well.
• Calculate all ratios for all three years in an Excel file, calculating the ratios by using cell formulas that reference cells with your numerator and denominator data in them. Include your cell formulas as an appendix to your report.
Do this for each company.
• Analyze each of the five categories of ratios in Chapter 2 of your textbook, providing at least two paragraphs on each category you analyze.
Do this for each company.
• To fill out your profitability analysis, Include a DuPont chart (blank template passed out in class, photocopied) for EACH OF THE THREE YEARS.
Do this for each company. You will include these as appendixes. Also, in your paper, make sure to include the DuPont identity,
with the numbers filled in, for each of the three years. Within the body of your paper, comment on the drivers of ROE performance (NPM or TAT or FLM) and how each of them have changed from one year to the next, thus contributing to the ROE change.
Also compare the company's ROE and drivers for the most recent year with the
industry values for that year.
• OPTIONAL: use industry averages, available online or at the library. You may not use any industry averages that require someone to pay for them. You may use only those available free for anyone.
Again, this is optional.
• No, you do not need a bibliography, but you should footnote any references you are using when gathering information from outside sources. However, I do need to know where you got your industry averages,
if included (OPTIONAL).
• Format: Your report must start with an Executive Summary and end with a Conclusion. Both the Executive Summary and the Conclusion
must highlight major strengths or weaknesses that are supported in your five-category analysis.
ALSO IMPORTANT: Give some specific ratio numbers (values) to support your key findings
within the Executive Summary.
• Format: Add to the impact of your presentation with graphs or charts you construct of key ratios (again, choose those that have a story, which support your Executive Summary).
• Excel Requirement:
You must do your ratio calculations in an EXCEL worksheet
and use cell references to calculate your ratios. You will turn in both the values and the cell formulas.
This is for each company. Values: Print out your worksheet. Cell Formulas: Change the display in Excel from values to formulas by holding down the CTRL key and pressing ~. The ~key is just below the ESC key on the left side of the keyboard. You may need to adjust the column widths before printing out your cell formulas (you may also have to change to landscape mode in you page setup). Once printed change back to values by again pressing CTRL~.
• Format: You may elect to reference the calculated ratio values by saying "See Appendix I". Or, cut and paste your ratio values to small tables within the paper itself.
I highly recommend that you make a trip to the library to look at the Diebold group project done in Fall 2006 and the AIG group project done in Spring 2007 (ask for these at the checkout counter, under Business and then under my last name). They are on closed reserve.