Nolan Banks is an auditor for the Public Service Commission for the state of Georgia. The Public Service Commission is a government agency responsible for ensuring that utility companies throughout...

1 answer below »






Nolan Banks is an auditor for the Public Service Commission for the state of Georgia. The Public Service Commission is a government agency responsible for ensuring that utility companies throughout the state manage their operations efficiently so that they can provide quality services to the public at fair prices.



Georgia is the largest state east of the Mississippi River, and various communities and regions throughout the state have different companies that provide water, power, and phone service. These companies have a monopoly in the areas they serve and, therefore, could take unfair advantage of the public. One of Nolan’s jobs is to visit the companies and audit their financial records to detect whether or not any abuse is occurring.



A major problem Nolan faces in his job is determining whether the expenses reported by the utility companies are reasonable. For example, when he reviews a financial report for a local phone company, he might see cable line maintenance costs of $1,345,948, and he needs to determine if this amount is reasonable. This determination is complicated by the fact that the companies differ in size—so he cannot compare the costs of one company directly to another. Similarly, he cannot come up with a simple ratio to determine costs (such as 2% for the ratio of line maintenance costs to total revenue) because a single ratio might not be appropriate for companies of different sizes.



To help solve this problem, Nolan wants you to build a regression model to estimate what level of line maintenance expense would be expected for companies of different sizes. One measure of size for a phone company is the number of customers it has. Nolan collected the data in the file PhoneService.xlsx representing the number of customers and line maintenance expenses of 12 companies he audited in the past year and determined were being run in a reasonably efficient manner.













  1. Create a scatter diagram of the data.





  2. Use regression to estimate the parameters for the following linear equation for the data:



    What is the estimated regression equation?





  3. Interpret the value for obtained using the equation from
    question 2
    .





  4. According to the equation in
    question 2
    , what level of line maintenance expense would be expected for a phone company with 75,000 customers? Show how you arrive at this value.





  5. Suppose that a phone company with 75,000 customers reports a line maintenance expense of $1,500,000. Based on the results of the linear model, should Nolan view this amount as reasonable or excessive?





  6. In your spreadsheet, calculate the estimated line maintenance expense that would be predicted by the regression function for each company in the sample. Plot the predicted values you calculate on your graph (connected with a line) along with the original data. Does it appear that a linear regression model is appropriate?





  7. Use regression to estimate the parameters for the following quadratic equation for the data:



    To do this, you must insert a new column in your spreadsheet next to the original X values. In this new column, calculate the values . What is the new estimated regression equation for this model?





  8. Interpret the value for obtained using the equation in
    question 7
    .





  9. What is the value for the adjusted- statistic? What does this statistic tell you?















  1. What level of line maintenance expense would be expected for a phone company with 75,000 customers according to this new estimated regression function? Show how you arrive at this value.





  2. In your spreadsheet, calculate the estimated line maintenance expense that would be predicted by the quadratic regression function for each company in the sample. Plot these values on your graph (connected with a line) along with the original data and the original regression line.





  3. Suppose that a phone company with 75,000 customers reports a line maintenance expense of $1,500,000. Based on the results of the quadratic model, should Nolan view this amount as reasonable or excessive?





  4. Which of the two regression functions would you suggest Nolan use for prediction purposes?







Answered 1 days AfterSep 21, 2022

Answer To: Nolan Banks is an auditor for the Public Service Commission for the state of Georgia. The Public...

Prince answered on Sep 22 2022
51 Votes
Sheet1
    Given Data
    Customers (in 1000s)    Line Maint. Expense (in $1000s)
                Xi    Yi    Xi- x̅    (Xi- x̅)2    Yi-Ȳ    (Xi- x̅)*(Yi-Ȳ
)
    25.3    484.6        25.30    484.60    -45.37    2058.13    413.93    -18778.78
    36.4    672.3        36.40    672.30    -34.27    1174.20    601.63    -20615.97
    37.9    839.4        37.90    839.40    -32.77    1073.65    768.73    -25188.83
    45.9    694.9        45.90    694.90    -24.77    613.39    624.23    -15460.18
    53.4    836.4        53.40    836.40    -17.27    298.14    765.73    -13221.66
    66.8    681.9        66.80    681.90    -3.87    14.95    611.23    -2363.44
    78.4    1,037.0        78.40    1037.00    7.73    59.80    966.33    7472.98
    82.6    1,095.6        82.60    1095.60    11.93    142.40    1024.93    12230.87
    93.8    1,563.1        93.80    1563.10    23.13    535.15    1492.43    34524.96
    97.5    1,377.9        97.50    1377.90    26.83    720.03    1307.23    35077.43
    105.7    1,711.7        105.70    1711.70    35.03    1227.33    1641.03    57490.87
    124.3    2,138.6        124.30    2138.60    53.63    2876.53    2067.93    110910.16
            Sum    848.00    13133.40    -0.00    10793.73    12285.40    -0.00
             Average    70.67    1094.45    -0.00    899.48    1023.78    13506.53
        β1        15.02                    β2        0.15
        β0        33.32                    β1        -7.39
        Linear Regression Equation         Yi = 33.32 + 15.02(Xi)                    β0        707.47
        Yi = β0 + β1Xi                            Quadratic Regression Equation         Yi = 707.47 - 7.39 Xi + 0.15Xi2
                                    Yi = β0 + β1Xi + β2Xi2
        Predicting line maintainance...
SOLUTION.PDF

Answer To This Question Is Available To Download

Related Questions & Answers

More Questions »

Submit New Assignment

Copy and Paste Your Assignment Here