WEBuild: A Case on Cost Estimation Congratulations! You are a new cost analyst on WEBuild, a new home building company that is expanding its market. The company is deciding which market to pursue: Saint Petersburg, FL or Sapphire, NC. Due to global warming, inflation and raising energy costs, both water and electricity costs are concerning to the builder since they want to provide the best possible product to their customers in a way that is cost effective to both the customer and the company. WEBuild's management is trying to determine the reason for the monthly variation on electric and water costs for both locations. As the cost analyst in charge, you have gathered consumption and other related monthly data for both cities for the year 2021 (see the "Statistics" tab on the accompanying Excel Worksheet). Requirements: 1. [ 20 points] Using your experience and applying the concept of economic plausibility, choose the independent variables (or cost drivers) that you believe will explain the variation on the costs of interest. Then, run a regression to determine if there is a statistically significant relationship (or correlation) between the variables you choose and the cost you are trying to explain. (Hint: you may want to plot the relationship between cost and the variables to explore if there is a liner relationship between them). 2. [20 points] Use the regression line (or cost function) you estimated to plot the "best fit" regression line for the data. (Note: you can only plot two variables at a time). a. You may add additional calculations (or transformation of variables) to the statistics page if you think you can come up with variables that will be better in explaining the costs. b. Name this tab: "SCENARIO 1-Base Data." 3. [20 points] Your colleague, Donny Data, found out about your analysis and found some additional data he believes will help in estimating the cost function that will explain energy and water costs. You can find the data in the "Additional Data" tab of the accompanying worksheet. a. Insert this data into the "Statistics" tab. Run another regression using KWH as the driver for electricity costs and gallons of water as the driver for Water costs. Run separate regressions for each of St. Pete and Sapphire. b. Name this tab: "SCENARIO 2-Additional Data." 4. [ 30 points] Write a one-page memo to Bob "The Builder," the company's CEO, explaining what you learned from your regression models and the cost functions you estimated based on their output. Make sure you identify you are referring to as Exhibit \#\#. Also, explain if the additional data explained by Donny Data increased the ability to explain both electricity and water costs? Include in the memo a recommendation about which market should WEBuild expand its operations to. See Rubric Below. INSTRUCTIONS FOR COMPLETION 1. Complete questions 1 through 4 of the requirements. 2. Upload an Excel file with the regression output and a Word document with the memo to the Canvas Assignment. 3. Complete a two (2) question quiz in Canvas based on your analysis. Statistics Additional data Statistics Additional data
