Question

In: Operations Management

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

Nolan Banks is an auditor for the Public Service Commission for the state of Georgia. The PublicService 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 it to visit the companies and audit their financial records to detect whether or not any abuse is occuring. 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 responsible. 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 ratioo 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 numeber of customers and line maintenance of 12 companies he audited in the past year and determined being run in a reasonably efficient manner.

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.

(Y= b0+b1x1+b2x2/1)

X Y XY X sq Forecast Y
Customers (in 1000s) Line Maint. Expense (in $1000s)
25.3 484.6 12260.38 640.09 413.00
36.4 672.3 24471.72 1324.96 579.80
37.9 839.4 31813.26 1436.41 602.30
45.9 694.9 31895.91 2106.81 722.50
53.4 836.4 44663.76 2851.56 835.10
66.8 681.9 45550.92 4462.24 1036.40
78.4 1,037.0 81300.8 6146.56 1210.60
82.6 1,095.6 90496.56 6822.76 1273.70
93.8 1,563.1 146618.78 8798.44 1441.90
97.5 1,377.9 134345.25 9506.25 1497.50
105.7 1,711.7 180926.69 11172.49 1620.70
124.3 2,138.6 265827.98 15450.49 1900.00

Solutions

Expert Solution

Solution-

In order to do quadratic regression in excel, you have to calculate X^2 values side by side to X values and then enter both the columns in the X column value in the regression field as shown below-

We get the following result from Regression-

From this output, we get the equation (looking at the value of Coefficients) as follows-

Y = 0.154 X^2 - 7.39 X + 707.4747

If you put X = 75, you get Y = 1019.475

However If we use linear regression, the equation fits better than quadratic equation.

Linear regression equation comes out to be-

y = 14.94x + 11.793

For x = 75, Y comes out to be 1132.29 which is more accurate.


Related Solutions

Nolan Banks is an auditor for the Public Service Commission for the state of Georgia. The...
Nolan Banks is an auditor for the Public Service Commission for the state of Georgia. The PublicService 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...
Ethical Case Nwankwo is a senior public servant who has worked in two state public service...
Ethical Case Nwankwo is a senior public servant who has worked in two state public service departments over a twenty-year period. Prior to this he was employed as a chartered accountant. In the course of performing his duties, involving primarily monetary and budgeting issues, Nwankwo becomes aware that public revenue is being used inappropriately. While he is not directly responsible for this aspect of the budget, he raised his concerns about the channelling of funds from one part of the...
State tax allocation problem Beach Hut is based in Georgia and has stores in Florida. Georgia...
State tax allocation problem Beach Hut is based in Georgia and has stores in Florida. Georgia has a 3.8% corporate tax rate, Florida is 17%. Because it is a seasonal operation, Beach Hut sells all merchandise at end of season for 40% of manufacturer’s list price to a discount jobber. There are no opening or closing inventories. All merchandise is ordered by the Georgia office. Financial reports show the following activity for 2017. Purchases 14,000,000 Sales 21,000,000 (16,000,000in Georgia, 5,000,000...
State and discuss the qualities of an Auditor of a Company.
State and discuss the qualities of an Auditor of a Company.
The purchases of electric plug-in hybrid cars are on the rise in the state of Georgia....
The purchases of electric plug-in hybrid cars are on the rise in the state of Georgia. The Chevrolet Volt is selling well, despite being sold at a price that doubles that of similar gasoline powered cars made by Chevy. Other automobile makers like Ford are adding plug-in hybrids to their lines at an incredible pace. What is the reason for the rising sales of plug-in hybrids? Is this an increase in demand or an increase in quantity demanded? What will...
A state insurance commission estimates that 20% of all motorists in its state are uninsured. Find...
A state insurance commission estimates that 20% of all motorists in its state are uninsured. Find the probability that in a random sample of 50 motorists, at least 8 will be uninsured.
Post a brief description of the practice agreements for NPs in Georgia state and the two...
Post a brief description of the practice agreements for NPs in Georgia state and the two physician collaboration issues that you identified. Explain what you think are the barriers to NPs practicing independently in your state. Finally, outline a plan for how you might address NP practice issues in your state.
Create a new plan that will bring in more revenue to the state of Georgia from...
Create a new plan that will bring in more revenue to the state of Georgia from the public in a way that they will support for paying taxes. it must increase taxation, but will not anger the public. try to innovate new ways to gain additional revenue
Why is public trust in banks essential? What would be the ultimate consequence if the public...
Why is public trust in banks essential? What would be the ultimate consequence if the public loses trust in banks? Discuss
Georgia state. Does your state have an income tax? What percentage of your income do you...
Georgia state. Does your state have an income tax? What percentage of your income do you have to pay in state income tax? What about property taxes and sales taxes? How do these taxes in your area compare to those in three other states and communities of your choice?
ADVERTISEMENT
ADVERTISEMENT
ADVERTISEMENT