Part 1 (25 points total) Instructions: Question 1. Draw an X-Y graph plotting the following sales figures for years 2004-2007. (Be sure to title your g Place the graph in the large blue region provided Question 2. Using the TREND function, forecast the company's sales for years 2008-2011 in the blue cells Question 3. Insert a trendline on the graph, and forecast it forward 4 years Question 4. Insert the trendline's equation on the graph Question 5. What is the underlying relationship between the TREND function's output, and the trendline equation Answer: Year Sales 2004 $1,000 2005 $1,050 2006 $1,780 2007 $2,300 2008 2009 2010 2011 ng the following sales figures for years 2004-2007. (Be sure to title your graph and label the axes) (5 points) lue region provided forecast the company's sales for years 2008-2011 in the blue cells (5 points) h, and forecast it forward 4 years (5 points) n on the graph (5 points) onship between the TREND function's output, and the trendline equation on the graph? (5 points) Graph: Part 1 Part 2 Part 3 Part 4 Total 0 Part 2 (25 points total) Part A (5 points) Part C (5 points) Complete the following Income Statement Increase Sales by 10% and calculate the percentage cha Income Statement 2007 Degree of Leverage Sales Variable Costs Fixed Costs 360,000 EBIT Interest Expense 50,000 Earnings Before Taxes Taxes Net Income Assumptions: Unit Sales 150,000 Price per Unit $20.00 Variable Costs as % of Sales 50% Tax Rate 40% Part B (6 points) How many units is the company required to sell in order to break even? ** What dollar Sales must the company reach in order to break even? What unit Sales must the company reach in order to reach an EBIT of $100,000? Do NOT use Goal Seek **Assume "breakeven" means zero EBIT Part D (5 points) Define "Operating Leverage" and explain why it is an important metric for companies to know (hint: what does it say about the riskiness of a company's operations?): Part E (4 points) Based on your observation in Part C, use a SINGLE FORMULA to determine the company's Degree of Operating Le d calculate the percentage change in EBIT: pany's Degree of Operating Leverage Part 3 (25 points total) You intend to set up a business in your home while you attend college. Your parents are willing to loan you 100% of your startup costs (which consists of an equipment purchase) according to the fo Amount: 8,500 Interest Rate 11% You forecast the company's future cash flows to follow the 5-year schedule below. You will be paying $2,500 for an equipment upgrade in Year 3 Part A (5 points) Enter your Year 0 cash flows: Year Cash Flow 0 1 1000 2 5000 3 (1,500) 4 7000 5 8000 Part B (5 points) You decide you will put aside your own money to make the Year 3 equipment upgrade. How much will you have to set aside today in a 5% savings account in order to pay for the Year 3 equipment upgrade? Part C (5 Points) Assume you decide not to invest your own savings in the upgrade. Instead, you will let the business fund itself. What is the present value of your anticipated future cash flows? How much will you net on this project in today's dollars? What will be the resulting average annual return on this investment? Part D (5 points) Assume that you are evaulating an alternate project with the following terms: 1. Same startup costs 2. Same borrowing rate 3. No equipment upgrade 4. Annual cash flows: $3,000 5. Project term (in years): 6 6. Machine salvage value: $1,250 What is the net present value of this alternate project? Hint: calculate the PV of cash flows and subtract the initial investment Part E (5 points) Generally speaking, which of the two projects would you prefer? Why? Explain a situation in which you would prefer the other project despite your answer above: ent purchase) according to the following terms: ear 3 equipment upgrade? usiness fund itself. Part 4 (25 points total) Part A (8 points) Calculate the intrinsic share value of the following two stocks. Assume a constant dividend growth rate as specified: Stock A Stock B Last Dividend $0.95 Expected Dividend, Period 1 $1.09 Required Return 15% 11% Dividend Growth Rate 3% 4% Value per share Part B (10 points) Calculate the value of this company by completing the following chart: Assumptions: Required Return on Equity: 10% All cash flows are paid out in the form of dividends The company bases its value on 5 years' dividends plus a terminal value of $3 million Dividend growth is the same every year Period 0 1 2 3 4 Net Income $375,000 Dividends to shareholders $350,000 Terminal Value Present value of period cash flows Present Value of Company: Part C (3 points) Complete the chart below using the following assumptions: Assumptions: Company A's riskiness is estimated to be twice that of the S&P 500, which typically returns 7% to equity investors. Company B's riskiness is estimated to be one-half that of the S&P 500 The current yield on the 10-year Treasury Bill is 4% Entity S&P 500 Company A Company B T-Bill Beta Expected Return Part D (4 points) Using the data in your above schedule, draw the Security Market Line for the four securities described in this problem. Hint: use an X-Y Scatter Graph Re-size the graph and place it in the blue space below Make sure you title your chart and properly label your axes 5 % to equity investors. escribed in this problem.
Pages to are hidden for
"Forecasting"Please download to view full document