Information and Decision Tool

IST 6090 - Fall 2023

Spreadsheets

Introduction:

The objective of this assignment is to provide the opportunity to learn and utilize new functionality in the development of a spreadsheet workbook and to learn about structuring the information to make it easier to maintain and assist in the decision-making process. Grading will be based on these principles.

Overview:

You are the foreman responsible for delivery of 2x4's to construction sites. One of the difficulties in your job is determining the appropriate amount to be delivered to each job site. You have collected data from several of the last projects and would like to see if there is any relationship in the data that could help you with the estimating process. In addition, your supervisor has been commenting on the excessive construction costs.

Requirements:

To get a better understanding of the situation you will perform the following:

1. Utilize the sample data provided by the instructor (file: Bunch o' 2x4's)
2. Generate a chart (graph) plotting all of the data points in a XY scatter chart.
3. Calculate the best-fit line of the data by manually creating a linear regression. To be provided and demonstrated by the instructor do not use the linear regression function in Excel.
4. Add the regression line to your chart and represent it as a line (not a set of points)
5. Calculate the upper and lower bounds (20% above and 20% below). Add these to the chart as lines.
6. Use the IF function to establish the relationship of each item relative to the lower and upper lines.
7. Ensure all items are clearly identified (X and Y axis, legend, title, etc.)
8. Generate a report to your supervisor indicating your findings. Include the following:
   1. Introduction explaining why you have decided to perform this analysis.
   2. Methodology utilized.
   3. Findings of your analysis. Discuss, as a minimum, the number of items that fall within the upper and lower bounds, number of items above the limit, and the number below the limit. Insert your chart into this section. Feel free to discuss any other conclusions.
   4. Recommendations. Describe what you plan on doing based on your findings (make them up!)
9. Calculate the number of 2x4's you would need to purchase if the house being built was 5,000 square feet.

What to Submit?

1. Your memo file to your supervisor with charts embedded into the text to support your findings. The file name must contain your last name (e.g., Mosher Tool2)
2. The spreadsheet file showing all your calculations (showing: all calculation, slope, intercept, and the approximation for the 5,000 square foot home)

Linear Regression

6090 Supplement to Decision Tool

The following is an example similar to the data in the exercise and provides an explanation that will assist in performing the analysis. In this example, a sample of data is collected to forecast the expected amount that would be paid for a house of 2,500 square feet using a simple linear regression. For further reference, see the Excel spreadsheet title "Linear Regression - Example".

Create the following table:

A	B		C		D	E		F
		Slope (m) =	142.65
		Intercept (b) =	-48,812.38		0.8	1.2
X(sq ft)	Y (Sale Price)		Y (Reg Line)		Lower	Upper
2,000	320,000		236,495.82		189,196.66	283,794.99		Over
2,000	199,000		236,495.82		189,196.66	283,794.99		Between
933	118,750		84,283.90		67,427.12	101,140.68		Over
1,067	128,000		103,399.55		82,719.64	124,079.46		Over
1,215	99,723		124,512.35		99,609.88	149,414.82		Between
1,379	142,000		147,907.63		118,326.10	177,489.15		Between
1,629	140,463		183,571.15		146,856.92	220,285.38		Under
1,379	137,000		147,907.63		118,326.10	177,489.15		Between
1,149	110,000		115,097.18		92,077.75	138,116.62		Between
1,378	132,500		147,764.97		118,211.98	177,317.97		Between

The X and Y values are the Square Footage and Sale Price from the raw data. This data represents the size of a house purchased (in square feet) and the amount paid.

Finding the regression line

In order to calculate the regression line you need to first calculate the slope (m) and intercept (b) (from algebra the slope intercept form: y = mx + b). This can be accomplished by using the Excel functions:

• =SLOPE(known_y's, known_x's)
• =INTERCEPT(known_y's, known_x's)

Where: known_y's is the range of cells for the Y values (column B), and

known_x's is the range of cells for the X values (column A)

Now we have the equation for the regression line shown in column C in the form y = mx + b

Y = 142.65* X + (- 48,812.38)

Using this equation, find the Y values for the regression line by plugging in each of the X values. For the first row: Y = 142.65 * 2,000 + (-48,812.38) = 236,495.82

Finding the Upper and Lower limits

Suppose you are willing to pay 20% over or under the forecasted price from the regression. To do this, calculation the lower and upper columns by multiplying the regression column by .8 and 1.2 respectively. That is:

Lower =236,495.82 * .8 = 189,196.66

Upper = 236,495.82 * 1.2 = 283,794.99

The last thing to do is compare the actual sale price (Y value in column B) to the Lower (D) and Upper (E) values. This tells us whether to falls in range or if it outside the limits. This can be done by using a nested IF statement (results shown in column F):

=IF(Sale Price > Lower, IF(Sale Price < Upper, "Between", "Over"), "Under")

Substitute the appropriate cell reference for each of the values Sale Price, Lower, and Upper.

Chart the data

Create a XY scatter chart with the information from the table. From the example, the chart should look like:

The chart should contain the actual data (original X and Y values), regression line and the lower and upper limit lines. In order to show the regression, lower and upper data as a line, you will need to modify the data properties. Modify the properties to remove the markers and make it a continuous line by right clicking any of the associated values and changing the parameters (Format Data Series...) This will be demonstrated by the instructor in class.

You should be able to identify each of the points from the table in the chart. The comparison of lower (D) and upper (E) will provide another way to validate the calculations are correct.

The estimate for a 2,500 sq ft home m*2,500 - b 142.65* 2,500 + (- 48,812.38) = ~307,823