# use scattergraph method, high low method, and the least square regression 247 Cost-Volume-Profit Relationships EXHIBIT SA-5...

use scattergraph method, high low method, and the least square regression
247 Cost-Volume-Profit Relationships EXHIBIT SA-5 A Scattergraph Plot for Brentine Hospital Using Microsoft Excel 5:2.000 \$10,000 58,000 Maintenance cost 56.000 54.000 52.000 2,000 2000 6.000 4000 Patient Day To prepare a scattergraph plot in Excel, begin by highlighting the data in cells B4 through CIO (as shown in Exhibit 5A-4). From the Charts group within the Insert tab, select the "Scatter" subgroup and then click on the choice that has no lines connect ing the data points. This should produce a scattergraph plot similar to the one shown in Exhibit SA-5. Notice that the number of patient-days is plotted on the X-axis and the maintenance cost is plotted on the Y-axis. As we saw verified earlier in Exhibit SA-1. the data is approximately linear, so it makes sense to proceed with estimating a regres- sion equation that minimizes the sum of the squared errors. To determine the intercept a, the slope b, and the R', begin by right clicking on any data point in the scattergraph plot and selecting "Add Trendline." This should pro- duce the screen that is shown in Exhibit 5A-6. Notice that under "Trend/Regression Type" you should select "Linear." Similarly, under "Trendline Name" you should select "Automatic." Next to the word "Backward you should input the lowest value for the independent variable, which in this example is 5000 patient-days. Taking this particular step instructs Excel to extend your fitted line until it intersects the Y-axis. Finally, you should check the two boxes at the bottom of Exhibit 5A-6 that say "Display Equation on chart" and "Display R-squared value on chart." Once you have established these settings, then click "Close." As shown in Exhibit SA-7, this will automatically insert a line within the scattergraph plot that minimizes the sum of the squared errors. It will also cause the estimated least-squares regression equation and to be inserted into your scattergraph plot. Instead of depicting the results using the form Y = a + bx, Excel uses an equivalent form of the equation depicted as Y = bx + a. In other words, Excel reverses the two terms shown to the right of the equals sign. So, in Exhibit 5A-7. Excel shows a least-squares regression equation of y = 0.7589x + 3,430.9. The slope b in this equation of \$0.7589 represents the estimated variable maintenance cost per patient-day. The intercept a in this equation of \$3,430.90 (or approximately \$3,431) represents the estimated fixed monthly maintenance cost. Note that the R is approximately 0.90, which is quite good and indicates that 90% of the variation in maintenance cost is explained by the variation in patient-days. * To insert labels for the X-axis and Y-axis, go to the Layout tab in Excel. Then, within the Labels group, select Axis Titles.
mi B EXHIBIT 5A-4 The Least-Squares Regression Worksheet for Brentiine Hospital ге d А Maintenance Patient 1 Cost 2 Days y 3 Month Х 4 January 5,600 \$ 7,900 5 February 7,100 \$ 8,500 6 March 5,000 \$ 7,400 7 April 6,500 \$ 8,200 8 May 7,300 \$ 9,100 9 June 8,000 \$ 9,800 10 July 6,200 \$ 7,800 11 Least squares regression

This Homework Help Question: "use scattergraph method, high low method, and the least square regression 247 Cost-Volume-Profit Relationships EXHIBIT SA-5..." No answers yet.

We need 10 more requests to produce the answer to this homework help question. Share with your friends to get the answer faster!

0 /10 have requested the answer to this homework help question.

Once 10 people have made a request, the answer to this question will be available in 1-2 days.
All students who have requested the answer will be notified once they are available.
##### Add Answer to: use scattergraph method, high low method, and the least square regression 247 Cost-Volume-Profit Relationships EXHIBIT SA-5...
Similar Homework Help Questions
• ### 6-2A - High-Low Method, Scattergraph, Least-squares Regression Danny Office Supplies shows the following data related to...

6-2A - High-Low Method, Scattergraph, Least-squares Regression Danny Office Supplies shows the following data related to shipping costs for the first six months of the year: Number of Shipping Packages Cost Shipped January 100 \$1,200 February 120 1,300 March 1,350 April 130 1,500 May 1,400 June 90 1,100 125 1107 - Required: Using the high-low method, estimate the cost formula (write it in y=mx+b format). i. Using your answer from part a.) above, assuming in July the company expects to...

• ### NISE 3-5 High-Low Method; Predicting Cost [L01. L031 ashore Hotel's guest-days of occupancy and custodial supplies...

NISE 3-5 High-Low Method; Predicting Cost [L01. L031 ashore Hotel's guest-days of occupancy and custodial supplies expense over the last seven The Lakeshore Hotel's months were: Month Guest-Days of Occupancy Custodial Supplies Expense March April May ..... June ... July August .. September ..... 4,000 6,500 8,000 10,500 12,000 9,000 \$7,500 \$8,250 \$10,500 \$12,000 \$13,500 \$10,750 \$9,750 7,500 Guest-days is a measure of the overall activity at the hotel. For example, a guest who stays at the hotel for three...

• ### Homework EXERCISE 2-14 High-Low Method; Predicting Cost The Lakeshore Hotel's guest-days of occupancy and custodial supplies...

Homework EXERCISE 2-14 High-Low Method; Predicting Cost The Lakeshore Hotel's guest-days of occupancy and custodial supplies expense over the last seven months were: Month March April May June July August September Guest Days of Occupancy 4,000 6.500 8,000 10,500 12,000 9,000 7,500 Custodial Supplies Expense \$7,500 \$8.250 \$10,500 \$12,000 \$13,500 \$10,750 \$9.750 Guest-days is a measure of the overall activity at the hotel. For example, a guest who stays at the hotel for three days is counted as three guest-days....

• ### 2. 3. LULU with the fleet of trucks. Express the variable and fixed costs in the...

2. 3. LULU with the fleet of trucks. Express the variable and fixed costs in the form Y = a +bX. If a truck were driven 80,000 kilometers during a year, what total oper expect to be incurred? a year, what total operating cost would you EXERCISE 5A-4 High-Low Method; Scattergraph Analysis L05-10 Archer Company is a wholesaler of custom-built air-conditioning unit It gathered the following monthly data relating to units shipped and total shipping m-built air-conditioning units for commercial...

• ### using microsoft excel, plot the absorbance (y-axis) vs. the concentration of each FAS standard standard (in g/ml) Use the method of least squares (linear regression) to determine the slope, intercept,...

using microsoft excel, plot the absorbance (y-axis) vs. the concentration of each FAS standard standard (in g/ml) Use the method of least squares (linear regression) to determine the slope, intercept, and correlation coefficient. Compare your slope with the literature value of 11,100 liter*mol^-1*cm^-1. You will have to convert your slope to have the same units as the literature value. Please do this unit conversion in your notebook. (FW FAS=392.13) Absorbance was read at 508 nm in a 1 cm curvette....

• ### In cell C6, insert a Scatter Chart for the Returns Completed versus Return Price data from...

In cell C6, insert a Scatter Chart for the Returns Completed versus Return Price data from the Data worksheet. You may be used to seeing Price placed on the Y-axis from other economics courses, but in this problem we are using price as the independent variable. Inserting Chart Select the Scatter chart from the provided chart options in the Charts group of the Insert tab of the Ribbon. Selecting Data Series Then choose Select Data in the Design tab on...

• ### Problem 2-22 High-Low and Scattergraph Analysis [LO2-4, L02-5] Pleasant View Hospital of British Columbia has just...

Problem 2-22 High-Low and Scattergraph Analysis [LO2-4, L02-5] Pleasant View Hospital of British Columbia has just hired a new chief administrator who is anxious to employ sound management and planning techniques in the business affairs of the hospital. Accordingly, she has directed her assistant to summarize the cost structure of the various departments so that data will be available for planning purposes. The assistant is unsure how to classify the utilities costs in the Radiology Department because these costs do...

• ### 1. Refer to Exercise 2-14 on page 55. Part 1 of 2. Using the formula format...

1. Refer to Exercise 2-14 on page 55. Part 1 of 2. Using the formula format as shown on page 34 of the text, and using the High/Low Method, estimate a cost formula for custodial supplies expense for the Lakeshore Hotel. 2. Refer to Exercise 2-14, Part 2 of 2 on page 55. Using the cost formula derived in part one , the total amount of custodial supplies expense you would expect Lakeshore Hotel to incur at an occupancy level...

Free Homework App