Details of this Paper

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

Description

solution


Question

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;X Y Excel Regression output using the X/Y variables.;Year Revenues Inventory Receivables SUMMARY OUTPUT;2001 2058 387 268;2002 2534 398 297 Regression Statistics;2003 2472 409 304 Multiple R 0.710962602;2004 2850 415 315 R Square 0.505467822;2005 3000 615 375 Adjusted R Square 0.340623762;Standard Error 77.74724334;Observations 5;ANOVA;df SS MS F Significance F;Regression 1 18534.89846 18534.9 3.066339 0.178228;Residual 3 18133.90154 6044.634;Total 4 36668.8;Coefficients Standard Error t Stat P-value Lower 95% Upper 95% Lower 95.0% Upper 95.0%;Intercept -35.70298444 276.5952591 -0.12908 0.905462 -915.953 844.5466 -915.953 844.5466;Revenues 0.186039563 0.106241747 1.751097 0.178228 -0.15207 0.524148 -0.15207 0.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);Year Revenues Inventory Receivables Payables;2003 32187 640 5196 1573;2004 36835 421 5890 1717;2005 39788 491 8881 5768;2006 44282 1478 11256 9521;2007 51122 1127 13237 6612;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 R 0.677994;R Square 0.459676;Adjusted R Square 0.279568;Standard Error 6143.978;Observations 5;ANOVA;df SS MS F Significance F;Regression 1 96342601 96342601 2.55222588 0.208426;Residual 3 1.13E+08 37748462;Total 4 2.1E+08;Coefficients Standard Error t Stat P-value Lower 95% Upper 95% Lower 95.0% Upper 95.0%;Intercept 31865.98 6254.874 5.0945833 0.01462052 11960.18 51771.77731 11960.17581 51771.78;Inventory 10.79724 6.758543 1.5975687 0.20842582 -10.7115 32.30593932 -10.71146387 32.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

 

Paper#30820 | Written in 18-Jul-2015

Price : $22
SiteLock