Information and Decision Tool

IS 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:

2. Utilize the sample data provided by the instructor (file: Bunch o' 2x4's)
4. Generate a chart (graph) plotting all of the data points in a XY scatter chart.
6. 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.
8. Add the regression line to your chart and represent it as a line (not a set of points)
10. Calculate the upper and lower bounds (20% above and 20% below). Add these to the chart as lines.
12. Use the IF function to establish the relationship of each item relative to the lower and upper lines.
14. Ensure all items are clearly identified (X and Y axis, legend, title, etc.)
16. Generate a report to your supervisor indicating your findings. Include the following:
    2. Introduction explaining why you have decided to perform this analysis.
    4. Methodology utilized.
    6. 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.
    8. Recommendations. Describe what you plan on doing based on your findings (make them up!)
19. Calculate the number of 2x4's you would need to purchase if the house being built was 5,000 square feet.

What to Submit?

2. 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)
4. The spreadsheet file showing all your calculations.