this is a excel project that I need help with, will go up with price and need it done asap......

Your first Excel Problem Set involves taking mixed costs and separating them into their variable and fixed components. You will use the Hi-Low method, a scatter graph, and Regression Analysis, and then compare the results. Complete instructions are given in the following file: A-202 Excel Regression Analysis Assignment Instructions.pdf The data you need to complete this assignment can be found in the following file: A-202 Excel Regression Analysis Assignment Data.xlsx Follow the instructions carefully. I expect your submission to be both accurate and professional. Be sure to spend time properly formatting your file, checking your spelling, etc. Think of our submission as something you'd turn in to your boss. For assistance in performing the regression analysis and creating a scattergraph, please refer back to Chapter 2. Appendix 2A reviews how to use Excel to perform a regression analysis and create a scattergraph plot. You may also find the following link helpful in conducting the regression analysis. http://www.exceleasy.com/examples/regression.html (Links to an external site.) The assignment is due by Monday, October 10 at 11:59pm. I would be happy to answer any specific question regarding the assignment so please, don't leave this until the last minute! Rubric Excel Problem Set #1 - Regression Analysis Assignment Excel Problem Set #1 - Regression Analysis Assignment Criteria Excel: Formulas and Cell Linking Ratings The spreadsheet formula(s) are welldeveloped and will correctly determine the needed information. 10.0 pts The spreadsheet formula(s) will correctly determine the needed information but lacks all of the necessary cell references needed. 8.0 pts The s formu used deter inform 4.0 pt 10.0 pts Excel: Labels and Headi ngs Contain s clearly labeled rows and columns 5.0 pts Rating Descripti on 4.0 pts 5.0 pts Excel and Memo: Content Meets all requirements necessary to communicate and display the requested information for the company. 15.0 pts Includes and makes clear all but one or two minor details that are necessary. 12.0 pts Includes a clear all b four detai necessary 6.0 pts 15.0 pts Excel: Professionalism and Organization Assignment has exceptional formatting and the information is well organized and professional. Documents are visually appealing and easy to read. 10.0 pts Assignment has attractive formatting and is somewhat organized. Data can be read and interpreted, but with some difficulty. 8.0 pts Assignment is somewhat orga Readability nee improvement, is great difficul interpreting the information. 4.0 pts 10.0 pts Excel: Directions All directions were followed accurately and completely. 5.0 pts Most of the directions were followed accurately. 4.0 pts 5.0 pts Total Points: 45.0 Whitewash, Inc. produces and sells whitewash. Since the major component of their product is water, the local water company instituted a billing system whereby the company is charged a fixed fee each month plus a per gallon charge. No one in your company can figure out what the Some o directio followe 3.0 pts fixed fee is because it's not separated on the monthly bill. As the new hire you have been given the unenviable task of figuring out the fixed cost portion of the water bill and coming up with a cost equation that management can use when budgeting. Everyone thinks you're doomed to failure but you graduated from IU East and to you this project is a walk in the park. You know your boss is an Excel addict and so you want to impress him by doing your work neatly in Excel. Here's what you need to do: 1. Using the data provided, calculate the cost equation using the HiLow method. To do this, duplicate the data set in a new worksheet, convert it into a table, sort by largest to smallest water usage, and perform the calculations to arrive at the cost equation based on the HiLow method. Show and explain all your work. You need to use cell referencing, the Excel Min and Max functions, and formulas to receive full credit. Make sure to write out the cost formula that you come up with. Carry the variable cost per unit out to six decimal places and the fixed cost to two decimal places. 2. Graph the data using a scatter graph. Make sure the graph is properly labeled (title, xaxis, yaxis). Insert a trendline, making sure to extend the line so it intersects the yaxis. Insert the equation for the trendline and the R2 value on the chart. 3. Perform a regression analysis on the data. Using the results, determine the exact equation of the line and the R2 value. Carry the variable cost per unit out to six decimal places and the fixed cost to two decimal places. Make sure the data is formatted properly and will print on one sheet of paper. 4. Write a short memo to your boss (Mr. Crane) explaining what you did, which cost equation is more accurate, and why. You will turn in two files, an Excel file with all your work and a Word File with your memo. Your Excel file should have the following worksheet tabs in this order: Your Excel file should be named Regression_YOUR LAST NAME. Your Word file should contain only the memo to your boss. It should be named Regression Memo_YOUR LAST NAME. Submit the assignment through the Canvas Assignments Tool. You will be graded using the following criteria: Excel HiLow: Excel Scatter Graph: Table Formatting Computation Labels Explanation Trendline with yaxis intercept, Cost Equation, and R2 value Formatting Proper cost equation Excel Regression: Word Memo: Formatting Formatting Proper cost equation Professionalism Proper explanation Month January February March April May June July August September October November December Gallons of Water Used 3,683,925 4,226,175 4,893,300 5,087,925 6,367,050 6,839,550 7,006,500 6,923,025 6,283,575 5,783,175 5,087,925 4,287,150 Cost $85,933 102,517 105,966 118,347 147,293 156,640 157,996 158,225 147,745 132,970 130,860 107,040 Excel Easy #1 Excel tutorial on the net Excel Introduction Basics Functions Data Analysis VBA 300 Examples Ask us Regression R Square | Significance F and P-Values | Coefficients | Residuals This example teaches you how to perform a regression analysis in Excel and how to interpret the Summary Output. Below you can find our data. The big question is: is there a relation between Quantity Sold (Output) and Price and Advertising (Input). In other words: can we predict Quantity Sold if we know Price and Advertising? 1. On the Data tab, click Data Analysis. Note: can't find the Data Analysis button? Click here to load the Analysis ToolPak add-in. 2. Select Regression and click OK. 3. Select the Y Range (A1:A8). This is the predictor variable (also called dependent variable). 4. Select the X Range(B1:C8). These are the explanatory variables (also called independent variables). These columns must be adjacent to each other. 5. Check Labels. 6. Select an Output Range. 7. Check Residuals. 8. Click OK. Excel produces the following Summary Output (rounded to 3 decimal places). R Square R Square equals 0.962, which is a very good fit. 96% of the variation in Quantity Sold is explained by the independent variables Price and Advertising. The closer to 1, the better the regression line (read on) fits the data. Significance F and P-values To check if your results are reliable (statistically significant), look at Significance F (0.001). If this value is less than 0.05, you're OK. If Significance F is greater than 0.05, it's probably better to stop using this set of independent variables. Delete a variable with a high P-value (greater than 0.05) and rerun the regression until Significance F drops below 0.05. Most or all P-values should be below below 0.05. In our example this is the case. (0.000, 0.001 and 0.005). Coefficients The regression line is: y = Quantity Sold = 8536.214 -835.722 * Price + 0.592 * Advertising. In other words, for each unit increase in price, Quantity Sold decreases with 835.722 units. For each unit increase in Advertising, Quantity Sold increases with 0.592 units. This is valuable information. You can also use these coefficients to do a forecast. For example, if price equals $4 and Advertising equals $3000, you might be able to achieve a Quantity Sold of 8536.214 -835.722 * 4 + 0.592 * 3000 = 6970. Residuals The residuals show you how far away the actual data points are fom the predicted data points (using the equation). For example, the first data point equals 8500. Using the equation, the predicted data point equals 8536.214 -835.722 * 2 + 0.592 * 2800 = 8523.009, giving a residual of 8500 - 8523.009 = -23.009. You can also create a scatter plot of these residuals. Do you like this free website? Please share this page on Google+ 10/10 Completed! Learn more about the analysis toolpak > Go to Top: Regression | Go to Next Chapter: Create a Macro http://www.excel-easy.com/examples/regression.html Whitewash, Inc. produces and sells whitewash. Since the major component of their product is water, the local water company instituted a billing system whereby the company is charged a fixed fee each month plus a per gallon charge. No one in your company can figure out what the fixed fee is because it's not separated on the monthly bill. As the new hire you have been given the unenviable task of figuring out the fixed cost portion of the water bill and coming up with a cost equation that management can use when budgeting. Everyone thinks you're doomed to failure but you graduated from IU East and to you this project is a walk in the park. You know your boss is an Excel addict and so you want to impress him by doing your work neatly in Excel. Here's what you need to do: 1. Using the data provided, calculate the cost equation using the HiLow method. To do this, duplicate the data set in a new worksheet, convert it into a table, sort by largest to smallest water usage, and perform the calculations to arrive at the cost equation based on the HiLow method. Show and explain all your work. You need to use cell referencing, the Excel Min and Max functions, and formulas to receive full credit. Make sure to write out the cost formula that you come up with. Carry the variable cost per unit out to six decimal places and the fixed cost to two decimal places. 2. Graph the data using a scatter graph. Make sure the graph is properly labeled (title, xaxis, yaxis). Insert a trendline, making sure to extend the line so it intersects the yaxis. Insert the equation for the trendline and the R2 value on the chart. 3. Perform a regression analysis on the data. Using the results, determine the exact equation of the line and the R2 value. Carry the variable cost per unit out to six decimal places and the fixed cost to two decimal places. Make sure the data is formatted properly and will print on one sheet of paper. 4. Write a short memo to your boss (Mr. Crane) explaining what you did, which cost equation is more accurate, and why. You will turn in two files, an Excel file with all your work and a Word File with your memo. Your Excel file should have the following worksheet tabs in this order: Your Excel file should be named Regression_YOUR LAST NAME. Your Word file should contain only the memo to your boss. It should be named Regression Memo_YOUR LAST NAME. Submit the assignment through the Canvas Assignments Tool. You will be graded using the following criteria: Excel Hi-Low: Excel Scatter Graph: Table Formatting Computation Labels Explanation Trendline with yaxis intercept, Cost Equation, and R2 value Formatting Proper cost equation Excel Regression: Word Memo: Formatting Formatting Proper cost equation Professionalism Proper explanation