Excel Applications for Accounting Principles

Business

Quiz 28 :
Statistics and Linear Regression Stats

Quiz 28 :
Statistics and Linear Regression Stats

arrow
PROBLEM DATA A full year analysis of maintenance costs at Riverglen Manufacturing is as follows: img REQUIREMENT The controller of Riverglen has asked you to calculate some statistics on this data. She is also interested in estimating the fixed and variable elements of the maintenance costs using linear regression. Review the worksheet STATS that follows these requirements.
Free
Essay
Answer:

Answer:

Open and review the worksheet indicated. In this problem you will calculate statistics on the data given for a company. You will use several functions in the worksheet to calculate this data. The unedited file will look like this:
img

arrow
PROBLEM DATA A full year analysis of maintenance costs at Riverglen Manufacturing is as follows: img REQUIREMENT Open the file STATS from the website for this book at cengagebrain.com. Enter the formulas where indicated on the worksheet. January is the next month and Riverglen is predicting 30,000 units produced. Hint: Most of formulas are statistical functions in Excel. See Appendix A of Excel Quick for a discussion of these functions. Enter your name in cell A1. Print the worksheet when done. Also print your formulas. Save your completed file as STATS2. Check figure: Cost prediction for January (cell F38), $84,713.
Free
Essay
Answer:

Answer:

Open the file listed. Enter the formulas where indicated on the worksheet using cell references so that if any data is modified, the figures will automatically update. Most of the formulas can be found in the statistics section. Be sure to include any additional data in the problem. When you have entered your formulas, your worksheet should look like this:
img

arrow
WHAT-IF ANALYSIS Use the worksheet to complete the following table of other possible outcomes for January: img Does your answer at zero units of production look familiar It should. Why
Free
Essay
Answer:

Answer:

The company wants to determine the cost prediction for the next month if the estimated volume for the month of January change. What will be the cost prediction for 25,000, 20,000, and 0 units for January
When the data for January's estimated volume is changed, the results are the following:
img The cost prediction for January at zero estimated units is the same amount as the fixed cost per month, which is the intercept.

arrow
CHART ANALYSIS There are many aspects and complexities to regression analysis, but the heart of the matter in this case is finding the fixed and variable costs buried in these data. Click the Chart sheet tab. The chart on the screen is an XY (scatter) chart based on the data you entered on the worksheet. Linear regression analysis mathematically computes a single "best-fit" straight line through these data points. This regression line can then be used to determine the fixed and variable components of total costs. To have the computer fit the regression line through the plotted points, follow these steps: a. Unprotect the Chart sheet. b. Click on one of the data points on the chart to select it. c. Right-click and select the Add Trendline option. d. For Type, select Linear. For Options, enter 18000 for Backward and then click OK. Click Close in Excel 2007 and Excel 2010. e. Print the chart and then click cell A1. (1) From the diagram, what is the estimated fixed cost per month (2) From the diagram, the actual costs for two or more months appear to be very close to the predicted costs. Which months are they (3) From the diagram, which months appear to be most efficient Why (4) What patterns are apparent from the months you identified in (2) and (3)
Essay
Answer: