Forecasting by cuiliqing


									Part 1 (25 points total)

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

                     Year               Sales
                     2004              $1,000
                     2005              $1,050
                     2006              $1,780
                     2007              $2,300
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)

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
   Variable Costs
   Fixed Costs                            360,000
   Interest Expense                        50,000
Earnings Before Taxes
Net Income

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
                                                  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:

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

                                            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:

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%
                                       S&P 500       Company A Company B             T-Bill
     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

% to equity investors.
escribed in this problem.

To top