Fill This Form To Receive Instant Help
Homework answers / question archive / 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
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:
a) Introduction explaining why you have decided to perform this analysis
b) Methodology utilized
c) Findings of you 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.
d) 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)
X(sq ft) | Y(2x4's) |
2000 | 21,200 |
2000 | 16,915 |
933 | 10,093 |
1067 | 10,880 |
1215 | 9,476 |
1379 | 12,070 |
1379 | 11,939 |
1379 | 11,645 |
1379 | 9,700 |
1379 | 11,262 |
1379 | 10,668 |
1379 | 11,262 |
1379 | 11,220 |
1465 | 10,370 |
1465 | 7,331 |
1547 | 12,835 |
1547 | 12,741 |
1597 | 13,175 |
1608 | 11,371 |
1647 | 13,037 |
1647 | 12,920 |
1707 | 11,305 |
1725 | 12,665 |
1725 | 12,325 |
1767 | 13,600 |
1803 | 14,875 |
1803 | 12,665 |
1832 | 12,367 |
1847 | 13,005 |
1847 | 12,155 |
1847 | 8,425 |
1847 | 11,050 |
1847 | 11,050 |
1850 | 12,750 |
1910 | 14,280 |
1910 | 13,770 |
1985 | 12,320 |
2130 | 18,700 |
2138 | 19,295 |
2281 | 19,507 |
2482 | 10,255 |
2482 | 21,250 |
2482 | 17,845 |
2482 | 17,850 |
2812 | 19,762 |
2812 | 21,080 |
2812 | 20,315 |
2812 | 16,915 |
2812 | 17,170 |
2812 | 22,516 |
2812 | 16,320 |
2812 | 15,483 |
2936 | 23,587 |
2936 | 24,395 |
2571 | 16,801 |
2602 | 24,641 |
2812 | 20,612 |
2812 | 19,295 |
2916 | 23,035 |
2916 | 21,250 |
3052 | 22,695 |
3052 | 21,921 |
3052 | 25,075 |
3093 | 23,077 |
3093 | 26,418 |
3093 | 25,500 |
3333 | 23,205 |
3333 | 20,723 |
3333 | 22,355 |
3333 | 23,375 |
3333 | 20,700 |
3333 | 26,350 |
3333 | 22,799 |
1160 | 10,795 |
1508 | 10,701 |
1508 | 9,820 |
1508 | 9,520 |
1508 | 11,093 |
1657 | 11,220 |
1677 | 6,402 |
1677 | 10,540 |
1852 | 13,591 |
2001 | 10,625 |
2048 | 12,920 |
2048 | 11,772 |
2048 | 11,985 |