VIEWS: 0 PAGES: 7 CATEGORY: Business POSTED ON: 11/22/2010
Spreadsheet Assignment Guide Spreadsheet assignments provide the opportunity to perform in-depth financial analysis that resembles the work performed by financial professionals. They also provide an essential, hands-on learning opportunity that increases understanding of course materials. Spreadsheet assignments may be completed individually or in groups of up to three members (no larger). Hand in a single paper for the group. Do not simply allocate the work. All members of the group should work together on each assignment to assure all group members have an opportunity to learn course material. Spreadsheet assignments are due at the beginning of class. Assignments received in the middle or end of the class will be penalized one-third of a grade (e.g., A- will become B+), and an additional one-third of a grade for each day late. Spreadsheet assignments require comprehensive application of course material. They can be time-consuming and challenging. Grading will recognize demonstrated effort (even if you don’t get the right answer, you get points for presenting a well thought-out, carefully prepared project). Spreadsheet Assignments must meet the following expectations to receive full credit. Failure to meet these requirements will result in loss of points. Each spreadsheet is graded in five areas as described below. Answers (A): Correct Answers will be the most significant part of the grade for virtually all spreadsheets. No answer key is available for spreadsheets. Formulas and Cell References (F): Do not hard code numbers in your spreadsheet. Hard coding is defined as directly typing numbers on the keyboard. In virtually all spreadsheets the beginning numerical data will already be entered for you. Do not re-enter the numbers provided. (Exception: you may hard code numbers in formulas that are not variables, such as the number 1 in the following formula: Future Value = (1+r)n.) Create formulas with cell references, not numbers. For example, if you are multiplying 3 by 5 do not enter "15", the result, and do not enter "=3*5"; instead, in cell C8, enter 3; in cell C9, enter 5; and in cell C10 enter "=C8*C9". (as mentioned above, in this example cells C8 and C9 will already be prepared for you, you need only create cell C10). Do not use preset formulas in Excel. The only exception to this is that you may use the SUM formula. Narrative(N): One page description of your work (approximately 500 words per page, or the average 12 point font, single spaced, normal margins). The narrative is addressed to the president of the company in question, as if a company analyst wrote it. Do not repeat the questions. Do not # your responses to questions. Each narrative includes three sections: o What you began with (1/4 page max) o The formulas and processes you used to analyze the information provided (1/4 page max) o The conclusions drawn from your analysis (1/2 page minimum) The narrative must quantify your analysis, particularly in the discussion of your conclusions o Not – “Acme, Inc.’s expenses increased significantly.” o Rather – “Acme, Inc.’s expenses increased by 20% (from $1,000 to $1,200) in 2002, reducing its ability to operating at a profit.” Avoid using “I”, “we”, “you”. o Not – “I arranged the financial information into a standard income statement.” Rather – “The financial information was arranged into a standard income statement.” o Not – “I think this analysis shows that the company is profitable.” Rather – “This analysis shows the company is profitable.” BA 303 Spreadsheet Assignment Guide Spring 2006 o Not – “You can see from this analysis.” Rather “It can be seen from this analysis...” Or “This analysis shows...” Refer to a company as “it”, not “they”. o Not – “They operate profitably” (not – The company operate profitably) Rather – “It operates profitably” (rather – The company operates profitably) Diagnostic(D): In addition to the spreadsheet itself, you must print a diagnostic sheet. A diagnostic sheet is a printout of the cell-formulas themselves rather than the results of those formulas. Diagnostic sheets are created as follows: 1. Save the spreadsheet as a separate file. (avoids the problem of switching back) 2. Go to the Tools menu and select Options. 3. Click on the View tab and select Formulas. 4. Widen columns to show entire formulas. 5. Go to the File menu and select Page Setup. 6. Click on the Sheet tab and click in the box “Row and column headings”. Presentation (P): All materials must be neat and well laid out. Section the spreadsheet into logical components that are easy to follow Good separation between pages: if the exhibit is too big to fit on one page, you must assure that: o The page breaks at a logical breaking point, not in the middle of a table. o Row and column headings are repeated on the second page. Each exhibit contains an exhibit number followed by a title, centered at the top of the page. Each exhibit must have a different title. (i.e. Exhibit 1, Acme, Inc. Balance Sheet; Exhibit 2, Acme, Inc. Income Statement). Formatting as follows o Your name in the upper right hand header of each page. o Staple in the upper left hand of pages printed in “portrait”; in the upper right hand corner of pages printed in landscape format o Page numbers at the bottom of each page. Page numbers must be consistent (avoid “Page 1” on all documents). o Consistent formatting: numbers representing like quantities should be in the same font, size, and color, and use the same number of decimal places (maximum of two) o Underline totals and subtotals in the manner of financial statements (Use Excel row borders: Go to Format menu, select Cells, click on the Border tab). Order of documents: 1) Narrative first. 2) Spreadsheet exhibits second 3) Diagnostic sheets last. Note: Important information from spreadsheets should be presented in the Narrative. Do not force the reader to hunt through spreadsheets to find the basis for your discussion. In addition, when referencing the spreadsheet, use the specific exhibit number and title. Page 2 of 7 BA 303 Spreadsheet Assignment Guide Spring 2006 Individual Spreadsheet Assignments Follow the instructions above for all Spreadsheet Assignments. Follow the instructions below for individual Spreadsheet Assignments. Diagnostic Sheets must accompany all Spreadsheet Assignments!! 1. Financial Statements, Cash Flow, Ratios Begin with template on website Arrange the financial information provided into an Income Statement and a Balance Sheet Calculate the following: o Operating Cash Flow o Free Cash Flow o Prove the DuPont Identity Calculate Return on Equity Calculate Profitability, Asset Turnover, and Equity Multiplier and multiply the three numbers Show that the two lines above are equal Your narrative description should be brief (one page max, single spaced, 12 point font). The narrative should indicate the following: a. What you started with. b. Your findings c. Your assessment of Taco Swell’s strengths and weaknesses d. Your recommendation for how Taco Swell can improve its weaknesses Grading (maximum points possible for each part of the spreadsheet): Narrative: 20 Correct Answers: 20 Diagnostic: 20 Formulas and Cell References: 20 Presentation: 20 2. Forecasting Calculate 2005 financial statements and external funds needed for Betsen Boutique by following the directions and answering the questions below in parts one to four. Bentsen’s 2003 and 2004 income statement and balance sheet are provided in Template 2. Begin with the template. Do not hard code numbers in your calculation, simply reference the numbers in the spreadsheet. Part One: Forecast 1. Given these financial statements, use the percent of sales method to develop pro forma statements for 2005. This is accomplished through the following steps: a. Complete the column Means of Forecast. The means of forecast for each line item is described below in items 2-7. Read carefully and record information for each line item in brief, descriptive terms, such as “given”, “constant”, “% of sales”, “% of other line item” (specify exactly which line item), or one of the formulas covered in class that allow you to calculate specific line items. b. Complete the columns for Historical %. Each Historical % is expressed as a percentage (usually as a percentage of sales). c. Complete the column for Forecast %. The Forecast % will either be given below in points 2-7, or it will be the average for the line item in the Historical % Statements for 2003 and 2004. d. Calculate the Forecast numbers for 2005. This is accomplished by referring the Means of Forecast column. If the Means of Forecast is “% of Sales”, multiply the forecast 2005 sales figure by the Forecast % for that line item. 2. Sales for 2005 are projected to be 15% higher than 2004. 3. Fixed expenses, depreciation, interest, short term notes payable, and common stock remain constant (they are a constant number, not a constant percentage). Page 3 of 7 BA 303 Spreadsheet Assignment Guide Spring 2006 4. The following are forecast as a percent of sales: cost of goods sold, selling, G&A expenses, all current assets, accounts payable, and other current liabilities. 5. Net fixed assets change only by depreciation (assume there are no sales or purchases of fixed assets). 6. 2004 Dividends = 4,090; keep dividends constant as a percentage of net income. 7. Project long-term debt as 50% of equity (in the same year). 8. Determine and record External Funds Needed (EFN) for 2005. 9. Print a numerical exhibit and a diagnostic sheet for this page. Diagnostic sheets are not necessary for parts two through four below. Part Two: Internal Growth 10. Using the sustainable growth formula, find the greatest % sales growth Betsen can support in 2005 without additional borrowing or additional equity funding (you may hard code numbers for this equation). 11. Input this % sales growth in your spreadsheet (instead of the original 15% growth). Is EFN = $0? Why or why not? Part Three: Sensitivity Analysis 12. Show sensitivity to changes in sales growth and cost of goods sold in the following scenarios (you do not need to print a pro-forma statement for each scenario, simply record the results in the sensitivity table described in Part Four): Sales Growth (a) Zero growth: change sales growth for 2005 to 0% and record the impact on EFN (b) High growth: change sales growth for 2005 to 20% and record the impact on EFN Cost of Goods Sold (Reset sales growth to the 15% in the original base case forecast (a) Low COGS: change the Forecast % for Cost of Goods Sold to 65% and record the impact on EFN (b) High COGS: change the Forecast % for Cost of Goods Sold to 74% and record the impact on EFN Part Four: Sensitivity Table 13. Summarize your findings regarding EFN in a sensitivity table, similar to the one below: Scenario % Growth COGS as a % of Sales EFN $ Base Case Zero Growth High Growth High COGS Low COGS Numbers in the % Growth and COGS as a % of Sales columns are expressed as a %. Numbers in the EFN Column are expressed in $. You may hard code numbers in your sensitivity table. 14. Study your sensitivity table. Which independent variable (% Growth or COGS as a % of Sales) causes greater variation in EFN (the dependent variable)? Part Five: Narrative Description 15. Describe the data you started with. (2 points) 16. Describe what you did to generate a 2005 forecast for Betsen Boutique. (2 points) 17. Present and explain your findings for Betsen’s internal growth rate. Describe what the internal growth rate means and why it produces an EFN that does or does not equal $0. (4 points) 18. Present your sensitivity table (it must appear in your narrative) and discuss your findings. Should the managers of Betsen Boutique be more concerned about increases in material costs or rapid growth in regards to increased need for external funds? Why? (5 points) 19. Imagine you are the CFO of Betsen Boutique. The VP of Marketing just informed you that the President has approved her request to increase the 2005 Marketing budget by an additional $2,000 (above and beyond the amount in your forecast for 2005). The VP of Marketing/Advertising goes on to say that the CEO was reluctant to approve the additional $2,000, because it would exhaust all existing credit lines. She convinced Page 4 of 7 BA 303 Spreadsheet Assignment Guide Spring 2006 the CEO to approve the increase, however, because it would increase projected sales growth for 2005 to 30% (vs. 15% in the base case). Assume that the new marketing plan would increase sales to 30%. As CFO of Betsen Boutique, what information and/or concerns would you communicate to the President regarding the forecast impact of this plan? You must make a recommendation based on the information available. Recommending the issue for further study is not an option. (7 points) 20. Assume Betsen Boutique wants a Total Debt/Total Equity ration of 1. As CFO, how much should Total Debt and Total Equity be in 2005. Change Common Stock and Long Term Debt to produce a Total Debt/Total Equity ratio of 1 (5 points). 21. Review your work to assure spreadsheet and written explanations are polished and logical. Quantify your narrative with figures from your spreadsheets!! Grading (maximum points for each part of the spreadsheet): Narrative: 25 Correct Answers: 30 Diagnostic: 15 Formulas and Cell References: 15 Presentation: 15 3. Stock and Bond Values Calculate the returns on stocks and bonds. Start with the template provided for Spreadsheet #3. Part One: Bond Analysis Given the following bond, find its value in each half-year up to maturity. Show its expected return in each year, divided into the coupon yield and capital gains yield components. Face value: $1,000 Semiannual coupon: 7% Maturity: 6 years Yield to maturity: 5%, 7%, 9% (3 different scenarios) Hardcode numbers in the information section in the upper left corner. Use formulas and cell references in your calculation of bond value. Definitions: Total Bond Value = PV of Face Value + PVA of Coupons Period Capital Gain $ = Dollar increase/decrease in the bond’s value during the period (Or, if you purchased the bond on the first day of the period and sold it on the last day of the period, what capital gain return would you recognize?) Period Capital Gain Yield % = Percent increase/decrease in the bond’s value during the period Period Coupon Yield $ = Total coupon payment(s) the investor receives during the period (Or, if you bought the bond on the first day of the period and sold it on the last day of the period, how much money would you receive in the form of Coupon payments). Period Coupon Yield % = Percent return on the bond’s value in coupons during the period Period Total Return % = Period Capital Gain % + Period Coupon Yield % Print results for all three scenarios. Print a diagnostic sheet only for the case in which the YTM = 6%. Part Two: Stock Analysis Using the dividend growth model, calculate the price (the intrinsic value) of First American Financial Corporations’ stock (FAF) as of January 1, 2005. For simplicity, assume that all dividends for a given year are paid to the shareholder on record at the end of the day on the last day of the year, December 31. First, using the actual information provided for FAF for 1997 to 2005, calculate the following: Total dividends in each year Annual increase <decrease> in dividends in dollars ($) Annual increase <decrease> in dividends as a percentage (%) Average annual dividend growth (%) Page 5 of 7 BA 303 Spreadsheet Assignment Guide Spring 2006 Next, using the irregular dividend growth model, calculate the intrinsic value of FAF’s stock using the assumptions in the following scenarios. Scenario 1: Use the following assumptions: Annual dividend growth for each of the next five years (2005 – 2009) is equal to the average annual dividend growth from 1997 to 2004 Annual dividend growth for years six and beyond (2009+) of 5% Discount rate is 8% Scenario 2: Imagine that FAF announces that it will increase dividends at an annual rate of 6% over the next five years rather than the historical average you calculated (assume FAF’s announcement is credible). Input this new average annual growth rate in your spreadsheet and record the resulting intrinsic stock value. Scenario 3: Imagine that FAF announces that it will increase dividends at an annual rate of 30% over the next five years rather than the historical average you calculated (assume FAF’s claim is credible). Input this new average annual growth rate in your spreadsheet and record the resulting intrinsic stock value. Print the results for all three scenarios. Print a diagnostic sheet only for Scenario 1. Part Three: Narrative Description (3 points per question) 1. Bonds a. Briefly describe how you analyzed the bond data. b. How did the price of each bond evolve over time? c. How is the price of the bond impacted by a change in yield to maturity? d. How did the investor’s return on the bond change over time? How did it change with a change in yield to maturity? e. What is the greatest unknown in predicting an investor’s return on a bond. 2. Stocks a. Briefly describe how you analyzed the stock data. b. How does the intrinsic value of FAF’s stock change if dividends are projected to increase at 6% per year instead of the historical average? c. How does the intrinsic value of FAF’s stock change if dividends are projected to increase at 30% per year instead of the historical average? d. What is the greatest unknown in predicting an investor’s return on a stock? 3. Comparison a. Which return is easier to predict? The return on a stock or a bond? b. If you were to invest $1,000 today, would you invest in stocks or bonds? Why? Quantify your narrative with figures from your spreadsheets!! Grading (maximum points for each aspect of the spreadsheet): Narrative: 33 Correct Answers: 37 Diagnostic: 10 Formulas and Cell References: 10 Presentation: 10 4. Capital Budgeting This assignment is based on Problem 9-16, Fusion Chips. Using the template provided, do the problem, parts a through f (skip part g). Comment on the results. The spreadsheet that you produce should be labeled as Exhibit 1 under Base Case Conditions, and should be carefully printed on two pages so that the acquisition scenario appears on a page by itself. After doing this base case problem, vary the assumptions in each of the following ways: 1. After acquiring Fusion Chips, TGI is unable to increase sales as hoped, and sales stay the same as projected for Fusion Chips as a stand-alone company. 2. After the acquisition, margins remain at 40% instead of increasing to 45%. Page 6 of 7 BA 303 Spreadsheet Assignment Guide Spring 2006 3. Both of the conditions above in 1) and 2) apply 4. Reverting to the original predictions for sales and margins in the acquired company, now assume that the terminal growth rate of cash flows is only 3%. For each of these alternative scenarios, you should have an exhibit. You don’t need to reprint the part of the exhibit that applies to the stand-alone value of Fusion Chips, but you do need to print the assumptions and the projections and valuation for the value of Fusion Chips as acquired. Don’t forget to print one diagnostic sheet for Exhibit 1. In the verbal section of your report, comment on the effects of your sensitivity analysis in the above 4 cases compared to the base case, and comment on the NPV profile and its effect on your final judgment as to whether TGI should acquire Fusion Chips. Quantify your answers with figures from your spreadsheets!! Grading will be as follows: Narrative: 30 Correct Answers: 40 Diagnostic: 10 Formulas and Cell References: 10 Presentation: 10 Page 7 of 7