Review the Regression Help document found in the Week 6 Discussion intro area

Review the Regression Help document found in the Week 6 Discussion intro area, as well as the explanation in your course text (see textbook beginning page 563). Estimate the revenue, receivables, inventory, or payables of one your portfolio companies selected in the Week 4 discussion. Find their financial data at http://finance.yahoo.com/ calculate a projection for the next 12 months, and then show your source data and calculations to your colleagues. ( believe this was already asked would be willing to pay the tutor joseph.wamwere for the work already done). Thanks!

Solution for Discussion 2
Managers must determine expected costs, expenditures, and revenues associated with a company’s assets in
order to then make decisions about short and long-term uses of capital. Accurate forecasting leads to maximized
returns.
Using regression (see textbook beginning page 563), estimate the revenue, receivables, inventory, or payables of
one your portfolio companies selected in the Week 4 discussion. Find their financial data at
http://finance.yahoo.com/ calculate a projection for the next 12 months, and then show your source data and
calculations to your colleagues. The initial posting is due before Day 3.
Regression trends the relationship between variables. The dependent variable is identified as the Y variable and
the independent is the X variable. For the purposes of this class we will only deal with simple linear regression
(the relationship between two variables – one y and one x – or one dependent and one independent). Dependent
variables are the data item we which to predict. Independent variables are the predictor data. They help us in
determining the value of the dependent variable.
Below is and example using the textbook material on regression on p564 and additional example using Microsoft.
To begin we need to make sure your Excel package has all the add-in packages loaded and ready to use. How to
perform this software loading step depends on which version of Excel you have (2007 or pre-2007). The
instructions below are general in nature to help with both Excel software programs.

Step 1. See if your Excel program has the add-in called Analysis Tool Pak loaded
a. If so, you will see a menu option called Data Analysis under the Data menu
b. If not, click on your help file and search for Data Analysis Tool Pak to determine how to load this.

Step 2: Click on Data Analysis and select Regression.
Step 3: Answer the questiions and fill in the blanks; Excel does the rest.

To complete regression, you need to determine the relationship between a couple of variable in your company.

Start by getting five years of ending balances for each of the balance sheet items above from your company.
Hint: you may have to go into the SEC Form 10k or an annual report on the company website to get this.

Example from our text on page 564
XYExcel Regression output using the X/Y variables.
YearRevenuesInventoryReceivablesSUMMARY OUTPUT
20012058387268
20022534398297Regression Statistics
20032472409304Multiple R0.710962602
20042850415315R Square0.505467822
20053000615375Adjusted R Square0.340623762
Standard Error77.74724334
Observations5

ANOVA
dfSSMSFSignificance F
Regression118534.8984618534.93.0663390.178228
Residual318133.901546044.634
Total436668.8

CoefficientsStandard Errort StatP-valueLower 95%Upper 95%Lower 95.0%Upper 95.0%
Intercept-35.70298444276.5952591-0.129080.905462-915.953844.5466-915.953844.5466
Revenues0.1860395630.1062417471.7510970.178228-0.152070.524148-0.152070.524148

Note the coefficient for Revenues of .186 and the Intercept of$-35.70. This is the same as is listed in the book.
We chose Inventory as the Y value because we wanted to be able to determine what inventory level would be required given a valid assumption of revenues
If we assume of new level of revenue at $3300 then the projected inventory balance will be:
Intercept +((coefficient of Revenue)*(Sales Estimate))
or
$-35.7 +(.186*3300) = $578m

Now lets try with a public company example

I choose Microsoft (therefore, you cannot use this company)
Symbol MSFT (Reported in millions)

YearRevenuesInventoryReceivablesPayables
20033218764051961573
20043683542158901717
20053978849188815768
2006442821478112569521
2007511221127132376612

Assume we need to know the level of revenues based on our expected inventory balance (opposite from the above example).
Y value would now be Revenues and X value would be inventory
Here is the regression table from Excel based on that data.
SUMMARY OUTPUT

Regression Statistics
Multiple R0.677994
R Square0.459676
Adjusted R Square0.279568
Standard Error6143.978
Observations5

ANOVA
dfSSMSFSignificance F
Regression196342601963426012.552225880.208426
Residual31.13E+0837748462
Total42.1E+08

CoefficientsStandard Errort StatP-valueLower 95%Upper 95%Lower 95.0%Upper 95.0%
Intercept31865.986254.8745.09458330.0146205211960.1851771.7773111960.1758151771.78
Inventory10.797246.7585431.59756870.20842582-10.711532.30593932-10.7114638732.30594

$31866 + (10.80 * inventory est) = Expected sales

Lets assume we know our inventory balance for next year will be 1500
Plug that into the above formula and you get
$31866 + (10.80 *1500) = Expected sales$ 48,066

Our Essay Format
  • Times New Roman, 12 pt
  • 1 Inch Margins
  • Double/ Single Spacing
  • 275/ 550 Words Per Page
  • MLA/ APA/ Turabian/ Chicago style, etc

A standard double-spaced page contains 275 words

Free Features
  • Hiring a preferred expert
  • Bibliography & cover page
  • Revisions within 14-30 days
  • 24/7 customer support
payments

Team of Professional Essay Writers

With our essay service, you'll find an essay writer for any task. Their rating is based on previous customer reviews and successful orders. Before you hire a writer, you can familiarize yourself with their track record in detail.