Q-Constructions – Building your second future
Q-Constructions has moved forward from the first successful report and you have just returned from a well-deserved break
Subject:EconomicsPrice:78.99 Bought11
Share With
Q-Constructions – Building your second future
Q-Constructions has moved forward from the first successful report and you have just returned from a well-deserved break. Again, they want your expertise and financial insights to support them to strive forward and keep their on-site workers happy. This has encouraged Q-Constructions to expand their services to offer clients renovations and provide insights into preferences on flooring options and informed financial advice on house prices. They are excited about offering these new services; however, they want you to conduct the analysis with your quants knowledge and they believe you are the right person for the job – you are a business number crunching guru now J
Q-Constructions’ next service involves your quantitative and computing skills in building value into people’s homes through renovations. The objective is to determine the number of rooms and their room type to be renovated to maximise the total value in a client’s home. Every client will have requirements and budget limitations, and no doubt change their mind – so you will have to be sensitive. Another question is about building insights for their company and their clients, in relation to flooring preferences across different rooms in a house. Q-Constructions can take advantage of these insights by providing recommendations to their clients and understanding the current customer behaviour market. This will help in successfully managing their product supplies to ensure demands for projects are available.
Q-Constructions have been asked to report on house prices and their location broken down into different regions across South Australia for an upcoming news article in Dream Homes. They want trustworthy and accurate insights to publish and therefore you have been assigned this task. In addition, they want you to report on any relative percentage increases across the regions. So, can an expensive house in your region affect your average house price?
Finally, Q-Constructions want to produce a tool for clients to predict their average house price based on their preferred dwelling size. They do not like the estimation price brackets given by the real estate websites since they don’t take into account personality of a home built by Q-Constructions – a Q home. You have been told to join forces with the analytics team to analyse the latest historical data on recently built projects and provide essential insights to clients, so they can make an informed decision.
Once again you will need to prepare a report for Q-Constructions. They are entrusting you to produce a vibrant and substantially informative report which makes sense in every day non-technical language as well as some business jargon. So, no mind-numbing mathematical calculations in the report body, otherwise you won’t get those great deals at Bunnings for you and your friends to build your very own project J.
The report length needs to be as follows:
Introduction:At most 3 paragraphs where 1 paragraph is 3-5 sentences. You should discuss the analysis that will be presented, on a separate page, and include all four of the report body questions.
Infographic:Exactly 1 page as indicated and with the elements in the presented order
The infographic should start on a separate page after the introduction.
The infographic must contain the elements indicated in the allocated spaces.
DELETE the highlighter text in < > as indicated in the infographic.
You are welcome to customise/decorate the rest of this area as you like J.
Report Body:A maximum of 1.5 pages of writing, excluding the infographic.
Ensure you stay within these limits.
The report body should start on a separate page after the infographic.
Use the questions given to guide your discussion of the results in Appendices 1-4.
Clearly stated results and interpretations will help to earn maximum presentation marks.
Do not include numerical calculations or spreadsheets.
Do quote quantitative (the final answer!) results.
You should have no more than 1.5 pages of writing (the infographic is excluded J)
Conclusion & Recommendations:At most 2 paragraphs where 1 paragraph is 3-5 sentences.You should discuss the analyses that were presented for all four report body questions and provide recommendations.
Should appear on a separate page as indicated in the template
The conclusion summarises the results – do not introduce new information although recommendations are fine J
Font Type: up to you, but keep in mind it should be easy to read – make it easy for us to give you marks!
Font Size: minimum 11pt (it shouldn’t look smaller than this!)
Any text or font size smaller than this sentence will incur 0 marks.
Suggested Assignment Work Timeline:
If you’re not sure how to start the assignment, a suggested work breakdown schedule is below. Feel free to use it (or ignore it) as you like J
Some people like to complete each appendix and then write it up in the report, others prefer to complete all appendices and then write up the report in one go. Do whichever suits you!
The weeks below indicate a suggested work breakdown as well as which week the lecture covers the material. The report can be written at any time (we don’t cover this in lectures J).
Appendix 1 (Week 4 Notes) NOW :HARD
Yu’ll need the Excel booklet, Excel template for LP and the additional resources on the course website.
Attend the Linear Prgramming Workshop in Week 8.
Appendix 2 (Week 5 notes) NOW:Easier
Appendix 3 (Week 6 notes) End of Week 7:Medium
There are instructins in the Excel data file as well for the last part of this appendix.
Appendix 4 (Week 7 notes) End of Week 8: Medium
Yu’ll need the Excel Videos page on the course website.
Report Body: Anytime (Week 8/9)Report body write-up and SUBMIT! YOU ARE DONE! Good times! JHarder
Introduction
(2 marks)
Purpose
Provide a qualitative description of report contents/problems addressed in the report (covering Appendices 1-4) and what insights the analysis will provide.
Write this introduction after you have a clear understanding of the content of your report.
Ensure you have explained what the report will contain. Follow the length guideline (given on the previous page).
Highlight and delete this message before submission
Report Body
(11 Marks) Appendix 1 Discussion Points
Loss of Marks: for using the words ‘decision variables’, ‘shadow prices’, ‘answer report’, ‘sensitivity report’, ‘binding’ and ‘non binding’ (or any variant thereof).
Describe your linear programming solution in terms of the maximum total value and the optimum number of rooms to be renovated of each type (Appendix 1(b)). For the number of rooms, if needed, explain whether you should round up or down and why.
Discuss the potential impact on the maximum total value and the optimal number of each room type if there is an extra $4,000 in the budget (Appendix 1(c)).
Discuss the potential impact on the maximum total value and the optimal number of each room type, if the client takes the deal on the flooring in Living Spaces which increases the value by $20 per sqm (Appendix 1(d)).
Also, discuss the potential impact on the maximum total value and the optimal number of each room type if the client decides to make renovations in the Kitchens optional (Appendix 1(e)).
Based on your analysis, which of these four options would you recommend the clientto undertake and include the total predicted return amount? (Appendix 1(b), (c), (d), (e))
3 + 2 + 2 + 2 + 2 marks
(9 Marks) Appendix 2 Discussion Points
Loss of Marks: if you use the words: statistically independent, statistically dependent, conditional, joint, marginal (or any variant thereof).
With reference to the 100% stacked column chart in the infographic, draw 2 observations including quoting approximate percentages (Appendix 2(a)).
Indicate the percentage of people whom preference is Wood flooring and in a Living Space, compared to someone having Wood as their preferred flooring somewhere in the house. Also, indicate the percentage of someone whom preference is Concrete somewhere in the house as a flooring option. In your explanation indicate clearly the conceptual difference between all three (Appendix 2(b), (c), (d)).
Are people’s preferences of Wood flooringstatistically independent across the various Room Types? Based on your analysis which room types should Q-Constructions recommend for using Wood flooring? (Appendix 2(d)).
3 + 4 + 2 marks
(7 Marks) Appendix 3 Discussion Points
Loss of Marks: if you use the words: mean, median, symmetric, skewed, histogram, boxplot, standard deviation, variance, Q1, Q3, IQR, outliers (or any variant thereof).
For each region, state and interpret the values for the average house price with the appropriate measures of central tendency and dispersion using layman’s terms. A bullet point discussion is OK. (Appendix 3(c)).
Discuss whether there have been any extremely expensive house prices across the different regions (Appendix 3 (b)).
By making reference to the table in the infographic, quote the recommended house price for each region and their comparison to Regional locations (Appendix 3 (d)).
3 + 2 + 2 marks
(10 Marks) Appendix 4 Discussion Points
Loss of Marks: if you use the words: slope, intercept, regression, correlation, coefficient of determination, R-squared, R2, r, interpolation, extrapolation (or any variant of these).
By making reference to the infographic, interpret the scatterplot and the association between explaining the average House Price ($) based on the average Dwelling Size (Appendix 4(a)).
Interpret and discuss the values of the slope and R-squared coefficient of determination in everyday language (Appendix 4(c), (d)).
State the predictions for the average house price if the average dwelling size is 200 sqm and if the dwelling size is 350sqm (Appendix 4(e), (f)). State the trustworthiness and the accuracy of these predictions (Appendix 4 (e), (f)) and include reasons to explain the high variability.
2 + 2 + 6 marks
Conclusions and Recommendation
(2 marks)
Conclusion: Summarise the main findings of your report.
Do not use direct quotes. Indicate whether the report fulfilled the purpose as stated in the introduction.
Recommendation: Base these on your report body discussion. Do not introduce new information in the recommendation. Present options for resolving the issue (purpose) presented in the introduction. Be brief – use dot points.
Highlight and delete this question text before submission
[Click here and insert the text for the Conclusion]
Appendix 1 - [Enter a suitable appendix name]
Highlight and delete the question text below before submission.
Include full details of your working out in this appendix.
Renovate to your dream home!
Before you begin!The template for this question is in Assignment 2 Data.xlsx: Appendix 1.
Beautiful designer homes are popping up in the streets of Adelaide and the word is getting out about Q-Constructions’ stylish homes. Currently, there is a lot of interest in people wanting to renovate their current house to achieve that designer finish. Q-Constructions has decided to take on an innovative approach to help their clients achieve more value in their current house. The clients approach Q-Constructions with a specific budget and the spaces they want renovated or converted to achieve their desired requirements, then you are tasked with the job of informing the clients on the optimal number of various rooms types to be renovated in their house based on a client’s key limitations and requirements with the goal to maximise the new total value of the house.
A new client has arrived! They want to know the optimal number of each type of room for their new house to maximise their total value. There are four main room types: Bedrooms, Bathrooms, Living Spaces (includes any open living space) and Kitchens. On average, the additional value a Bedroom will produce is $8,000 per room, a Bathroom will produce $15,000 per room, a Living Space will produce $13,500 per room and a Kitchen will produce $15,000.
The client’s budget for all their rooms has been determined to be a total of $100,000 – you cannot exceed this number. To produce the new total value of each room, a Bedroom costs $5,000 per room, a Bathroom costs $10,000 per room, a Living Space costs $9,000 per room and a Kitchen costs $10,000 per room. Also, each room has an average size, a Bedroom is 12 sqm per room, a Bathroom is 8 sqm per room, a Living Space is 20 sqm per room and a Kitchen is 8 sqm per room. The total floor space available to renovate for all the rooms must not exceed 200 sqm. In addition, there is a labour allocation for this project of 400 hours to install the flooring, install the windows and put in all the finishes. Each room has an average labour time for a Bedroom it is 30 hours per room, a Bathroom is 50 hours per room, a Living Space is 20 hours per room and a Kitchen is 100 hours per room.
The client has a set of requirements which you need to include in your model based on the number of rooms. The requirements are listed below in the table and an additional requirement is the client requires there is at least one Bathroom for every two Bedrooms.
Requirements
Minimum Number of Rooms
10
Minimum Number of Bedrooms
2
Minimum Number of Bathrooms
1
Minimum Number of Living Spaces
1
Minimum Number of Kitchens
1
(7 marks) Before you begin! Your model for this part will look like the model in the Week 4 notes (Slide 13). Formulate a linear programming model for this problem, filling in the template over the page. Type up the full mathematical model in Word and include it here.
For full marks fill in the template provided below, clearly indicating:
The decision variables. Define them precisely.
The objective. Using your decision variables, formulate the objective function.
The constraints. Using your decision variables, formulate these constraints.
Hint: For the constraint relating to the Bathrooms and Bedrooms. You can interpret it equivalently as “The number of bedrooms is less than or equal todouble the number of bathrooms”.
Decision Variables
Objective and Objective Function
Constraints
(3 marks) Before you begin! Part (a) needs to be completed first before entering the model in Excel or you’ll miss out on 7 marks!
Hint #1:If the solution does not contain whole numbers, this is OK. You will be asked to address this issue in the report write-up.
To complete this question:
The Linear Programming template for this question is in Assignment 2 Data.xlsx in the worksheet Appendix 1(b).
Enter your model from (a) into this template.All EXCEL output will need to bear your e-mail ID. To ensure this, you will need to save your EXCEL file as ‘E-mail ID Assignment xlsx’ BEFORE you run Solver. In addition, your constraint names should end with yourinitials, e.g. Size NFY onlyif your initials are really NF
Show calculations to support the predicted changes to maximum total value (if any). Hint: You may need to re-run solver.
(3 marks)The client notices that renovating a Kitchen costs a lot of money relative to the space. Suppose the client relaxes the condition on the number of Kitchens to be optional (this means zero kitchens are required to be renovated). Calculate the new predicted Total Value($) for their new design layout and discuss whether the solution remains optimal.
Show calculations to support the predicted change to total value ($) (if any). (Re-run Solver and state the new optimal solution in the report body)
Do not discuss the rest of the Answer Report – you will do this in the report body!
TOTAL 22 MARKS
Please provide the requested non-quantitative analysis in the report body.
Highlight and delete the above question text before submission
Appendix 2 - [Enter a suitable appendix name]
Highlight and delete the question text below before submission.
Include full details of your working out in this appendix.
Pick your flooring J
Before you begin!The data for this question are in Assignment 2 Data.xlsxin worksheet Appendix 2.
Recently, there has been a lot of hype about flooring – I know floors! The marketing team at Q-Constructions has collected a random sample of 1,200 preferences from their clients informing what product type they prefer in each room. The goal is to give insights to new clients on what are people’s preferences and determine if the room type, especially in the Bedroom, is dependent on the product.
The Room types are Living Spaces, Bathrooms and Bedrooms and their preference for Wood, Tile or Concrete flooring. Use the data displayed in Table 2 below to perform the requested analyses below.
Room Type
Product Type
Total
Wood
Tile
Concrete
Living
300
125
75
500
Bathroom
50
175
75
300
Bedroom
200
100
100
400
Total
550
400
250
1200
Table 2: Room Type by Product Type
(2 marks) Use EXCEL to obtain a 100% stacked column chart for the data from Table 2, with Room Type labels shown on the horizontal axis.
EXCEL Instructions (3 steps)
Step 1.Refer to Topic 6 in the Excel Booklet for instructions on how to obtain a 100% stacked column chart using data summarised in a table.
Step 2.To place Room Type labels on the horizontal axis see the instructions below.
Note! If you have a Mac AND your Excel version is different to the instructions below, post on the social forum specifying which version of Excel you have and we can help you J
Windows
Select the chart
Go to the Design tab and select ‘Switch Row/Column’:
Mac
Select the chart
Go to the Charts tab and select ‘Switch Row/Column’:
Step 3.Annotate the graph with an appropriate title for the horizontal x-axis (not the y-axis). Ensure that the main title of your chart ends with your network ID e.g. bloggsj001.
For the probability calculations belowinclude the calculation and the appropriate probability statement. Present your answer to 4 significant digits. You do not need to write a sentence summarising the results.
(3 marks) What is the probability a client chooses Wood and in a Living Space?
(3 marks) What is the probability that a client chooses Concrete?
(4 marks) Is the choice of Wood flooringstatistically independent across Room Types? Quote and/or calculate all relevant probabilities.
TOTAL 12 MARKS
Please provide the requested non-quantitative analysis in the report body.
Highlight and delete the above question text before submission
Appendix 3 - [Enter a suitable appendix name]
Highlight and delete the question text below before submission.
Include full details of your working out in this appendix.
Location, Location, Location J
Before you begin!The data for this question is in Assignment 2 Data.xlsxin worksheet Appendix 3.
Q-Constructions are interested in looking at the market value of houses of a similar quality and design in South Australia. The market house prices vary due to many variables such as location, size and the final layouts. They have tasked you to look into the different prices across three main regions of South Australia. In particular, they want to analyse the data with the goal of producing insights and conclusions about the similarities and differences in the three regions. Furthermore, they want to determine if there is surcharge on regions due to location for clients building if they decide to sell their house. They have collected the most recent market data and have given it to you. The data can be found in your spreadsheet for you to start crunching the numbers to answer the company’s questions below.
Note: please do not include a printout of the data J(loss of presentation marks will apply)
Before starting the analysis you will need to add in the Data Analysis toolpack.
Windows Instructions:See the EXCEL booklet, Topics 6 & 7 for parts (a) and (b).
Mac Instructions:check you have the analysis toolpack. Go to Tools > Excel Add-ins you should see a window like the one below pop up (most of you should have it and need to tick the box):
(3 marks)In the EXCEL worksheet, add your initials to columns A, B and C. Now obtain Descriptive Statistics for each Region (Column A-C), including Quartile 1, Quartile 3 and IQR.
(4 marks)Extend the analysis in (a) to include outlier calculations using the 1.5xIQR Rule. Perform these calculations by hand (not in Excel). Use this rule to determine whether there are any outliers present.
(4 marks) By referring to your output from parts (a), (b) and boxplots below, discuss the following features for each Region relating to the House Price ($):
The shape of the distribution (symmetric, skewed left, skewed right) and whether there are outliers present. Support your discussion by using the boxplot and descriptive statistics to refer to the location of the median, and to describe the whisker lengths and presence/lack of outliers.
The single most appropriate measure of central tendency and dispersion for the purposes of making comparisons. Simply state your choice and give a brief reason why. Do not provide an interpretation in this appendix (you will do this in the report body).
For full marks: before submitting your assignment annotate the boxplots below with an appropriate title that includes your network ID, and include lines representing the median on the boxplots, otherwise you will receive 0 marks for the boxplots.
(d) (2 marks) Lastly, by using your answers to parts (a) and (c), fill in the table below to indicate the average house price for each region. In addition, calculate the surcharge on the location in terms of a percentage based off the Regional location being the baseline, if the average building price is the same across the three regions.
Region
Adelaide (Inner)
Adelaide (Outer)
Regional
Average House Price ($ 000’)
Surcharge (%)
0%
TOTAL 13 MARKS
Please provide the requested non-quantitative analysis in the report body.
Highlight and delete the above question text before submission
Appendix 4 - [Enter a suitable appendix name]
Highlight and delete the question text below before submission.
Include full details of your working out in this appendix.
How much do you pay!
Before you begin! To complete this question, you need the data stored in the Excel file ‘Assignment 2 Data.xlsx’ in worksheet Appendix 4,which you can download from the Assignments page.
Q-Constructions is growing in popularity with their analytical approach and they have noticed your talent in the business when dealing with numbers and providing insights. They want to give their clients insights into their House Price ($) value based on the estimated Dwelling Size (sqm). Q-Constructions believe you can provide a more comprehensive insight using your Quants skills to determine a more accurate estimation than the brackets given by real estate websites. This will enable their clients to be well informed of the predicted house value when deciding on the size of their dwelling.
An important part of this process is for you to develop a model which can predict the average House Price ($) based on the Dwelling Size (sqm). The analytics team at Q-Constructions have given you their historical data, capturing the house prices and dwelling size. The spreadsheet is sitting in your data repository and now you need to perform the analyses requested below. Q-Constructions is excited to see your skills in action and if successful, Q-Constructions may reward you in the future with some trade discounts at bunnings...
Please do not include a printout of the data J(loss of presentation marks will apply!)
(4 marks) You are interested to predict the House Price ($) from the Dwelling Size (sqm). As a first step, use EXCEL to draw a scatterplot of House Price ($) vs Dwelling Size ($). Annotate the axes of this plot and include a title that is meaningful and contains your network ID. Now save a copy of this graph and include it in the infographic.
Compute the correlation between these two variables using the CORREL function (Topic 9 in the Excel Booklet). In this Word document, type the Excel calculation you performed to compute the correlation coefficient.
Using EXCEL add a trendline to the scatterplot and display the regression equation and coefficient of determination (R-squared value) (Topic 9 in the Excel Booklet). Include a copy of the scatterplot with the trendline here (the scatterplot without the line will be used in the report as it does not contain mathematics, while the plot with mathematics will appear in this appendix J).
Comment briefly on the scatterplot and correlation coefficient and whether a linear model is appropriate for this data.
(2 marks) State which is the dependent variable and which is the independent variable.
(3 marks) Discuss the value of the intercept and whether it is meaningful. Also, state and interpret the value of the slope in this scenario.
(3 marks) What is the value of the coefficient of determination (R-squared value)? Briefly discuss what the value means and whether the model is a good fit for the data.
(3 marks) Use the simple regression equation from part (a) to predict the House Price ($) required when the Dwelling Size (sqm) is 200 sqm. Comment on the accuracy of the prediction and discuss briefly.
(3 marks) Use the simple regression equation from part (a) to predict the House Price ($) when the Dwelling Size (sqm) is 350 sqm. Comment on the accuracy of the prediction and discuss briefly.
TOTAL 18 MARKS
Please provide the requested non-quantitative analysis in the report body.
Highlight and delete the above question text before submission
Y.
Use EXCEL Solver to obtain a solution to the linear programming model from part (a), together with an Answer Report and a Sensitivity Report. Provide a screenshot of your solved EXCEL spreadsheet and the Answer and Sensitivity reports below.
Do not discuss the output, save this for the report body!
(5 marks)For the solution obtained in part (b), interpret the shadow price for the available “Floor Space” constraint and for the constraint “Budget”. For each interpretation state:
The shadow price,
The range of feasibility and
The impact of the shadow price on the objective function for changes within this range of feasibility.
For the Budget constraint only: calculate the change in Total Value ($) if the Budget available is increased by $4,000. (Re-run Solver and state the new optimal solution in the report body)
Hint #1:Week 4 lecture notes, Slide 45 shows you exactly what you need to do to answer this question J
Hint #2:A negative shadow price is interpreted in the opposite way to a positive shadow price. J
(4 marks)The client has stumbled onto a good deal on flooring for a Living Space which will increase the value by $20 per sqm at the same cost. Calculate the new predicted Total Value ($) and discuss whether the solution remains optimal.
