Solutions

Project1

Project 1	Please use this file as your template to complete project 1 using the Data worksheet to develop responses and generate regression tables and scattergraphs.
Name: TYPE in your name here	Save your file using your first initial, last name, and name of Project.
	Upload your solution by clicking on the project 1 assignment link. Make sure you type your name in cell A2
	You should always verify that your project has been loaded correctly by either going back to the assignment and clicking on Ok and you should
	see the file you uploaded and you can also go to view grades and click on the exclamation to see the file you have loaded.  Remember it is your
	responsibility to take the time to verify that your file has uploaded correctly.  If there are problems, please contact me immediately.
	You will be graded on the accuracy of your answer and the usage of excel.
	You must use excel cell referencing in all of your calculations and use the data analysis tool in development of your regression tables.
	The project 1 solution is worth a total of 30 pts.
	I have an extensive regression demonstration in the Module 15 folder in the course resources area.
	I do not pregrade projects, so if you have questions please make sure you ask a specific question.
Project 1- Regression Analysis
1. Develop scattergraphs, simple regression output tables, and multiple output table.
2. Develop cost estimation based on your regression analysis.
3. Analyze results and recommend the best approach for the cost estimation.
Project 1-Part 2 Grading Rubric:
2 Simple Regression Tables-type out equation of line	8
2 Scattergraphs-provide the trendline and the equation of line and R-square	8
Multiple Regression-provide equation of line. Also identify what X2 and X3 variables are.	4
3 Cost Estimations-using cell referencing	6
Comments	4
Total points possible	30

DATA

Project 1
You have just been hired as a consultant to a large manufacturing company of sodas, and have decided to use your new MBA skills to make a recommendation
to management on what should be the cost driver(s) for estimating the monthly overhead cost for one of the plants that manufactures this drink outside the United States.
Month	Volume of liquid produced hectoliters	Water used in hectoliters	Number of Batches	Total Overhead costs for the plant
January	890	6,005	54	$57,266
February	980	6,588	58	61,020
March	1,094	7,336	65	64,622
April	1,212	8,002	73	68,630
May	1,262	8,435	75	70,653
June	1,494	9,940	89	79,927
July	1,557	10,420	95	82,867
August	1,528	10,326	94	81,748
September	1,215	8,284	72	68,819
October	1,145	7,746	69	66,375
November	1,072	7,168	64	63,767
December	1,032	6,933	62	62,545
January	872	5,902	50	56,837
February	1,006	6,759	60	61,298
March	1,041	6,990	62	63,791
Do not retype the information above. You should use this worksheet as your data sheet and your solution should be on separate
sheets.
Required:
1. Prepare 3 scattergraphs and 3 simple regression analyses***** to estimate the overhead costs using each of the
cost drivers above. Make sure you show(insert) the equation of the line and R-squared on each of your scattergraphs.
The output for your simple regression table should be on a separate sheet and you should type out the equation of the line below the table output.
I have prepared the volume of liquid produced scattergraph and regression for you, therefore, you actually only have to do 2.
You may want to redo this scattergraph and regression analysis just to make sure you understand the process.
If you have done your scattergraph and simple regression correctly the equation of the line and the R-squared should match the respective output.
To make a scattergraph with data from nonadjacent columns highlight the X data (i.e. independent variable-cost driver) first and then hold down the
control (ctrl) key and highlight the Y data (i.e. the dependent variable-cost).
2. Prepare a multiple regression table using all three of the activities as the independent variable.
Using the output from the multiple regression, give the equation of the line to estimate factory cost in terms of the
3 cost drivers below the table output.
Also, make sure you identify which cost driver relates to X1, X2, and X3--I have done the first one for you.(excel reads left to right)
Identify the 2 other X variables(I have identified the X1 variable for you, which will be the first column highlighted):
X1	Volume of liquid produced
X2	Water used
X3	Number of batches
3. Assuming the following level of cost-driver volume for the month, what is the estimated
cost using simple regression for each of the cost drivers and using multiple regression.
You must use cell references on the output tables you generated on separate worksheets from the regression tables to estimate the costs. You will not earn credit if you type in your numbers or do not reference the regression worksheets.
You should have 4 computations for this answer.**** I am preparing the answer using volume of liquid produced to estimate the cost, therefore, you
only have to prepare 3 computations.
		Volume of liquid produced	1050
		Water used	7000
		Number of batches	68
	Volume of liquid produced to estimate overhead cost		63,189.88 Cindy Nye: Guidance: Remember the equation of the line is based on Y being the dependent variable--which is overhead cost and we are trying to estimate that cost based on usage of the cost driver and in this example it is volume of liquid produced.
	I used the simple regression output to cell reference
	and used the intercept as the fixed cost
	and the X variable as the variable cost.
	Water used		62,868.19
	Number of Batches		66,466.87
	Multiple regression		63,760.00
4. Using the simple and multiple regression analyses above, what independent variable(s) would you recommend
to estimate the Overhead Costs? Why? Your response should be at least 75 words and discuss the R-Squared.

The R-squared for all the simple and multiple regression are all high, so all the independent variables are showing a good cause and effect relationship. With the ability to track cost drivers, I would go with multiple regression since it has the highest R-squared and it takes into account all the variables that relate to the operating room overhead costs. There are costs incurred to track these variables, but with the advancement of technology this should not be an issue. I have never had a scenario like this which was so correlated. I usually do not have results like this and almost started over, but decided it definitely showed how a company could have multiple drivers that are related to overhead costs.

Scattergraph-batches

Total Overhead costs for the plant 54 58 65 73 75 89 95 94 72 69 64 62 50 60 62 57266 61020 64622 68630 70653 79927 82867 81748 68819 66375 63767 62545 56837 61298 63791

Scattergraph-water

Total Overhead costs for the plant 6005 6588 7336 8002 8435 9940 10420 10326 8284 7746 7168 6933 5902 6759 6990 57266 61020 64622 68630 70653 79927 82867 81748 68819 66375 63767 62545 56837 61298 63791

Multiple Regression

SUMMARY OUTPUT
Regression Statistics
Multiple R	0.9984339737
R Square	0.9968703998
Adjusted R Square	0.9960168725
Standard Error	524.4803763003
Observations	15
ANOVA
	df	SS	MS	F	Significance F
Regression	3	963831267.016968	321277089.005656	1167.9419809554	0
Residual	11	3025876.31636565	275079.66512415
Total	14	966857143.333333
	Coefficients	Standard Error	t Stat	P-value	Lower 95%	Upper 95%	Lower 95.0%	Upper 95.0%
Intercept	23861.2274151079	969.8382325328	24.6033066286	0.0000000001	21726.6278589421	25995.8269712736	21726.6278589421	25995.8269712736
X Variable 1	23.8747294151	17.016735517	1.4030146611	0.1882031323	-13.5788529078	61.328311738	-13.5788529078	61.328311738
X Variable 2	1.0004572412	2.2725741971	0.4402308371	0.6682952231	-4.0014448388	6.0023593211	-4.0014448388	6.0023593211
X Variable 3	115.1044871731	159.6150014912	0.7211382771	0.4858692205	-236.2057622184	466.4147365646	-236.2057622184	466.4147365646
Y=23861.23+23.87X1+1X2+115.10X3
X1 = Volume of liquid
X2 = Water used (hectoliters)
X3 = Number of batches

Simple-Batches

SUMMARY OUTPUT
Regression Statistics
Multiple R	0.9971220549
R Square	0.9942523923
Adjusted R Square	0.9938102687
Standard Error	653.8122247472
Observations	15
ANOVA
	df	SS	MS	F	Significance F
Regression	1	961300027.805359	961300027.805359	2248.8106102094	5.94806136506362E-16
Residual	13	5557115.52797499	427470.425228845
Total	14	966857143.333333
	Coefficients	Standard Error	t Stat	P-value	Lower 95%	Upper 95%	Lower 95.0%	Upper 95.0%
Intercept	25784.4334508266	892.501943169	28.8900586135	0	23856.3002307333	27712.5666709199	23856.3002307333	27712.5666709199
X Variable 1	598.2711115524	12.6159974324	47.421625976	5.94806136506362E-16	571.0159061827	625.5263169221	571.0159061827	625.5263169221
Y=25784.43+598.27X
X=# of batches

Simple-Water used

SUMMARY OUTPUT
Regression Statistics
Multiple R	0.9978008348
R Square	0.995606506
Adjusted R Square	0.9952685449
Standard Error	571.6293228316
Observations	15
ANOVA
	df	SS	MS	F	Significance F
Regression	1	962609262.257962	962609262.257962	2945.9206101383	1.03685602388178E-16
Residual	13	4247881.0753716	326760.082720892
Total	14	966857143.333333
	Coefficients	Standard Error	t Stat	P-value	Lower 95%	Upper 95%	Lower 95.0%	Upper 95.0%
Intercept	23152.5786521902	827.4687375442	27.9800040796	0	21364.941130884	24940.2161734964	21364.941130884	24940.2161734964
X Variable 1	5.6736593818	0.1045328377	54.2763356366	1.03685602388178E-16	5.4478299162	5.8994888474	5.4478299162	5.8994888474
Y=23,152.58+5.67X
X=Water used

Simple-Volume

SUMMARY OUTPUT
Regression Statistics
Multiple R	0.9983183626
R Square	0.9966395531
Adjusted R Square	0.9963810572
Standard Error	499.9286177511
Observations	15
ANOVA
	df	SS	MS	F	Significance F
Regression	1	963608071.236328	963608071.236328	3855.5330728484	1.81484216386645E-17
Residual	13	3249072.09700512	249928.622846547
Total	14	966857143.333333
	Coefficients	Standard Error	t Stat	P-value	Lower 95%	Upper 95%	Lower 95.0%	Upper 95.0%
Intercept	23533.7726066979	717.274621464	32.8099892321	0	21984.1949994336	25083.3502139622	21984.1949994336	25083.3502139622
X Variable 1	37.7677247643	0.6082450819	62.0929389935	1.81484216386645E-17	36.4536911565	39.0817583722	36.4536911565	39.0817583722
Y=23,533.77+37.77X
X=Volume of liquid produced

Scattergraph-volume

Total Overhead costs for the plant 890 980 1094 1212 1262 1494 1557 1528 1215 1145 1072 1032 872 1006 1041 57266 61020 64622 68630 70653 79927 82867 81748 68819 66375 63767 62545 56837 61298 63791