Least Squares Regression Analysis

Bogota Corporation has gathered data on its overhead activites and associated costs for the past 12 months. Josh Hopper, from the accounting department, has convinced management that overhead costs can be better estimated and controlled if the fixed and variable components of each overhead activity are known. One such activity is the purchasing department (receiving and reviewing purchase recquisitions, issuing purchase orders, and managing vendor relationships), which he believes is driven by the number of purchase orders issued. Twelve months of data have been gathered for the purchasing activity and are as follows:

Month	Purchase Orders Issued	Purchasing Cost
1	1,050	$23,738
2	750	16,788
3	1,550	35,113
4	1,250	27,263
5	1,350	27,463
6	1,150	27,988
7	1,650	34,388
8	1,450	29,213
9	1,750	38,663
10	950	21,938
11	1,250	25,688
12	1,500	34,200

a. Run a regression using Excel or a similar computer spreadsheet program. Provide the intercept, slope, and R² for the regression.

Round Intercept and Slope to two decimal places. Round R² to three decimal places.

Intercept $Answer

Slope $Answer

R² Answer

b. Using the results from requirement a, prepare a cost formula for the purchasing activity.

Total cost = $Answer

+ ($Answer

x #purchase orders issued)

c. Using the formula from requirement b, what is the predicted cost of purchasing for a month in which 1,375 purchase orders are issued? (Note: Round your answer to the nearest dollar.)

$Answer

Least Squares Regression Analysis Bogota Corporation has gathered data on its overhead activites and associated costs for the past 12 months. Josh Hopper, from the accounting department, has convinced management that overhead costs can be better estimated and controlled if the fixed and variable components of each overhead activity are known. One such activity is the purchasing department (receiving and reviewing purchase recquisitions, issuing purchase orders, and managing vendor relationships), which he believes is driven by the number of purchase orders issued. Twelve months of data have been gathered for the purchasing activity and are as follows: Month 1 2 3 4 5 Purchase Orders Purchasing Issued Cost 1,050 $23,738 750 16,788 1,550 35,113 1,250 27,263 1,350 27,463 1,150 27,988 1,650 34,388 1,450 29,213 1,750 38,663 950 21,938 1,250 25,688 1,500 34,200 6 7 8 9 10 11 12 a. Run a regression using Excel or a similar computer spreadsheet program. Provide the intercept, slope, and R2 for the regression. Round Intercept Slope to two decir places. und R2 to three decimal places. Intercept $ 0 x Slope $ 0 X R20 X b. Using the results from requirement a, prepare a cost formula for the purchasing activity. Total cost = $ 0 X + ($ 0 X x #purchase orders issued) c. Using the formula from requirement b, what is the predicted cost of purchasing for a month in which 1,375 purchase orders are issued? (Note: Round your answer to the nearest dollar.) $ 0 X