Excel Problem #3: Estimating Costs-Special Topics This problem will ask you to do 6 things. As always, you need to label cells, format your cells and use formulas/cell references where applicable. 1. Enter a data table and a results table 2. Use High-Low to calculate estimation values and estimate the next year's expenses 3. Chart your data points and look for seasonality/outliers (LABEL your chart; check your axis values) 4. Use a dummy variable to accommodate seasonality 5. Use simple regression to calculate estimation values and estimate the next year's expenses 6. Use multiple regression to calculate estimation values and estimate the next year's expenses You will use ONE tab of an Excel workbook for this problem...please delete excess and label your tab "Future Return Cost Estimates" Rubric for Excel Problem #3- Cost Estimation Followed directions on where to enter data Entered data table correctly Entered results table correctly, with links and formulas 1 Correctly performed High-Low estimation Correctly produced well formatted chart Correctly entered dummy variables Correctly performed simple regression Correctly performed multiple regression 1 1 2 2 1 2 2 12 Total ACC 3200 Excel Problem 3--Cost Estimation-Saved to my Mac Review References Mailings View AaBb Ap Aa AufibCeDdte AabC AalbCcDdfe AaBbCcD AaBbCcDdE No Spacing Subtr Normal Heading 1 Heading 2 Title Lexcon, Inc. is a large manutacturer of attordable DVD players. Management recently became aware of rising costs resulting from returns of malfunctioning products. As a starting point for further analysis, Paige Jennings, the controller, wants to test different forecasting methods and then use the best one to forecast quarterly expenses for 2013. The relevant data for the previous three years is as follows: Quarter Return Expenses $17,900 16,500 1 2 18,400 3 4 21,700 17,500 18,300 6 7 18,000 8 23,400 20,400 10 19,600 18,100 23, 900 11 12 Enter the table in Excel, with your first (label) cell in A1 [Hint: reverse the column order]. Enter the following chart in A 15 1. Cost Estimation Under Different Analysis Methods Simple Regression Multiple (Quarter Regression High Low Quarter Coefficient Season Coefficient Intercept Estimated Costs for Next Year by Quarter 13 ed States) Focus MacBook Pro Di FS un ACC 3200 Excel Problem 3-- Cost Estimation-Seved to my Mac Review View ut References Mailings A AaBbCcDr ACcodt AaBb Ac AalbCcDdt AubCcDdfe AultbCcDdie Aa No Specing Nomal Heading 2 Subrte Heading 1 Tler A A Enter the table in Excel, with your first (label) cell in A1 [Hint: reverse the column order). Enter the following chart in A 15. 1. Cost Estimation Under Different Analysis Methods Simple Regression Multiple (Quarter Regression High Low Quarter Coefficient Season Coefficient Intercept Estimated Costs for Next Year by Quarter 13 14 15 16 Use High-Low to develop a cost equation and estimate costs for the next year (Quarters 13, 14, 15, and 16). Use two decimal places. (link or use formulas to fill in the table) 2. Use Excel to graph the data points and check for outliers and seasonality. What do you see? [You need to give me a well formatted line chart to answer this question..pay attention to labels and axis values] Place the upper right corner of the chart in F1. 3. 2 Focus United States) MacBook Pro bo 888 g attention to labels and axis values] Place the upper right corner of the chart in F1. 2 Now, add an X column (needs to be next to your Quarter column) for Seasonality (column C). Assign a "dummy variable" of 1 to any outlier or seasonal high point you find and O to all other data points. Using Excel (Data Data Analysis Regression), a simple regression using the independent variable "Quarter" (use Labels). Place your regression output in cell F25. Calculate costs for the next year (link or use formulas to fill in the table) Use two decimals. 4. run Now, run a multiple regression using both independent variables: the quarter and the season. Place your regression output range to start in cell F43 (use labels again). Estimate costs for the next year (link or use formulas to fill in the table). Use two decimals. (HINT: don't forget which quarter has a seasonality adjustment). 5. Focus tates) MacBook Pro DO F6 FS F4 F3 % $ 7 6 A L Excel Problem #3: Estimating Costs-Special Topics This problem will ask you to do 6 things. As always, you need to label cells, format your cells and use formulas/cell references where applicable. 1. Enter a data table and a results table 2. Use High-Low to calculate estimation values and estimate the next year's expenses 3. Chart your data points and look for seasonality/outliers (LABEL your chart; check your axis values) 4. Use a dummy variable to accommodate seasonality 5. Use simple regression to calculate estimation values and estimate the next year's expenses 6. Use multiple regression to calculate estimation values and estimate the next year's expenses You will use ONE tab of an Excel workbook for this problem...please delete excess and label your tab "Future Return Cost Estimates" Rubric for Excel Problem #3- Cost Estimation Followed directions on where to enter data Entered data table correctly Entered results table correctly, with links and formulas 1 Correctly performed High-Low estimation Correctly produced well formatted chart Correctly entered dummy variables Correctly performed simple regression Correctly performed multiple regression 1 1 2 2 1 2 2 12 Total ACC 3200 Excel Problem 3--Cost Estimation-Saved to my Mac Review References Mailings View AaBb Ap Aa AufibCeDdte AabC AalbCcDdfe AaBbCcD AaBbCcDdE No Spacing Subtr Normal Heading 1 Heading 2 Title Lexcon, Inc. is a large manutacturer of attordable DVD players. Management recently became aware of rising costs resulting from returns of malfunctioning products. As a starting point for further analysis, Paige Jennings, the controller, wants to test different forecasting methods and then use the best one to forecast quarterly expenses for 2013. The relevant data for the previous three years is as follows: Quarter Return Expenses $17,900 16,500 1 2 18,400 3 4 21,700 17,500 18,300 6 7 18,000 8 23,400 20,400 10 19,600 18,100 23, 900 11 12 Enter the table in Excel, with your first (label) cell in A1 [Hint: reverse the column order]. Enter the following chart in A 15 1. Cost Estimation Under Different Analysis Methods Simple Regression Multiple (Quarter Regression High Low Quarter Coefficient Season Coefficient Intercept Estimated Costs for Next Year by Quarter 13 ed States) Focus MacBook Pro Di FS un ACC 3200 Excel Problem 3-- Cost Estimation-Seved to my Mac Review View ut References Mailings A AaBbCcDr ACcodt AaBb Ac AalbCcDdt AubCcDdfe AultbCcDdie Aa No Specing Nomal Heading 2 Subrte Heading 1 Tler A A Enter the table in Excel, with your first (label) cell in A1 [Hint: reverse the column order). Enter the following chart in A 15. 1. Cost Estimation Under Different Analysis Methods Simple Regression Multiple (Quarter Regression High Low Quarter Coefficient Season Coefficient Intercept Estimated Costs for Next Year by Quarter 13 14 15 16 Use High-Low to develop a cost equation and estimate costs for the next year (Quarters 13, 14, 15, and 16). Use two decimal places. (link or use formulas to fill in the table) 2. Use Excel to graph the data points and check for outliers and seasonality. What do you see? [You need to give me a well formatted line chart to answer this question..pay attention to labels and axis values] Place the upper right corner of the chart in F1. 3. 2 Focus United States) MacBook Pro bo 888 g attention to labels and axis values] Place the upper right corner of the chart in F1. 2 Now, add an X column (needs to be next to your Quarter column) for Seasonality (column C). Assign a "dummy variable" of 1 to any outlier or seasonal high point you find and O to all other data points. Using Excel (Data Data Analysis Regression), a simple regression using the independent variable "Quarter" (use Labels). Place your regression output in cell F25. Calculate costs for the next year (link or use formulas to fill in the table) Use two decimals. 4. run Now, run a multiple regression using both independent variables: the quarter and the season. Place your regression output range to start in cell F43 (use labels again). Estimate costs for the next year (link or use formulas to fill in the table). Use two decimals. (HINT: don't forget which quarter has a seasonality adjustment). 5. Focus tates) MacBook Pro DO F6 FS F4 F3 % $ 7 6 A L