ASSESSMENT TASK:     Assignment 2

WEIGHTING:                   30%

COMPLETION:                Individual

GENERATIVE AI:           Generative AI tools can be used in this assessment task

In this assessment, you can use generative artificial intelligence (AI) to generate the

specified content in relation to the assessment task. This material must be

acknowledged and recorded in your declaration of AI use.

DUE DATE:                    11:55pm, Monday 8 April, 2024

OVERVIEW

In this assignment, you are tasked with conducting regression analysis on multiple datasets provided in Excel format. The assignment is structured around four key cases, each requiring you to apply regression techniques to predict outcomes based on various independent variables. This exercise aims to assess your proficiency in predictive modelling, data analysis, and the interpretation of results within a business analytics context.

.     In the Decision case, using the "Decision.xlsx" dataset, you will analyse the impact of

experience on decision-making quality among auditors, examining how it correlates with intelligence, thinking styles, and personality traits.

.     The Haircut case requires you to explore the "Haircut.xlsx" database to determine the factors that significantly influence a company's revenue, employing regression analysis to identify these key predictors.

.     For the Audit scenario, with the "Audit.xlsx" dataset, you are to investigate the relationship between audit delay and various descriptive variables, focusing on developing a regression model that can accurately predict delay durations.

.     The Prescription Cost Analysis involves the "Prescription.xlsx" dataset, where you will model and predict drug costs based on a set of independent variables, enhancing your model's accuracy through iterative refinement.

Your submission should demonstrate a thorough understanding of regression analysis as applied to predictive analytics. This includes not only the technical execution of statistical tests but also the ability to interpret and communicate the significance of your findings in a clear, concise manner.

Through this assignment, you will showcase your capability to leverage Excel for predictive modelling and to derive actionable insights from complex datasets.

OBJECTIVES

.      Understand and apply regression analysis techniques.

.     Analyse relationships between dependent and independent variables.

.      Interpret and evaluate regression model outputs.

.     Develop predictive models based on the analysis.

.     Communicate analytical findings effectively.

SUBMISSION REQUIREMENTS

Type your responses in a MS Word document and submit your Word document to Moodle. Cut and paste any relevant output from Excel into your Word document.

You do not need to clean the data and do not delete any data.

Case 1: Audit (5 marks)

A study investigated the relationship between audit delay (the length of time from a company’s fiscal year-end to the date of the auditor’s report) and variables that describe the client and the auditor. Some of the independent variables that were included in this study are presented below.

Using data in “Audit.xlsx”, answer the following questions:

(1.1) Develop scatter charts of the data using each of the independent variables included in the data. (0.5 mark)

(1.2) Develop the estimated regression equation using all of the independent variables included in the data. (0.5 mark)

(1.3) Test for an overall regression relationship at the 0.05 level of significance. Is there a significant regression relationship? (0.5 mark)

(1.4) How much of the variation in the sample values of delay does this estimated regression   equation explain? What other independent variables could you include in this regression model to improve the fit? (1 mark)

(1.5) On the basis of your observations about the relationships between the dependent

variable Delay and the independent variables Quality and Finished, suggest an alternative regression equation to the model developed in your answer to the question (1.2) to explain as much of the variability in Delay as possible. (2.5 marks)

Case 2: Decision (5 marks)

Using the “Decision.xlsx” dataset, analyse differences between experienced and inexperienced participants.

(2.1)    Do the experienced versus the inexperienced auditors differ in the quality of their

decisions (i.e., the Decision variable)?  Cut and paste relevant statistics from Excel and explain the statistics. (2 marks)

(2.2)    Do the experienced versus the inexperienced differ in terms of any intelligence,

thinking style, or personality trait variables? Identify the ones that are different and provide the relevant statistics. Cut and paste relevant statistics from Excel and explain the statistics (only for those that are different). (2 marks)

(2.3)    Without using the language of statistics, what do you conclude about experienced versus inexperienced auditors? (1 mark)

Decision data description

Participants consist of auditors and students. Auditors are considered experienced and students are inexperienced.

Case 3: Haircut (5 marks)

Use the “Haircut.xlsx” database to run regression models that explain the factors that significantly influence revenue at this company.

(3.1)    Report and interpret your best model’s technical details. Cut and paste the relevant statistics from Excel and explain the statistics. (2 marks)

(3.2)    Do you believe that your model is effective for explaining changes in revenue?  Explain and justify your response. (2 marks)

(3.3)    Explain in plain language the meaning of your findings. (1 mark)

Haircut data description

You have been provided an Excel file that contains 4 data items. Each row represents the  data for one haircut at a business that operates in two countries. The business does not take appointments. Customers walk in and wait for a haircut.

Case 4: Prescription Cost Analysis (15 marks)

Assume that you are working for a government agency that is trying to determine the main causes of different drug costs for different patients. You have data (“Prescription.xlsx”) from six months of drug prescriptions. You need to model and predict drug costs. The appendix shows  descriptions of the data.

(4.1) Assume that we are using this model: (3 marks)

GrossDrugCost = B0 + B1 * RiskScore + ε

i.       Interpret the coefficient and the p-value for the RiskScore variable. Provide a practical explanation of the RiskScore variable for senior management. (1 mark)

ii.       Explain what R-squared means in a statistical way and provide a practical explanation of the information to senior management. (1 mark)

iii.       A coworker wants to know what the predicted gross drug costs would be for a new

member. The new member is a 73-year-old man who the government classifies as frail and he has a risk score of 510. Using the model above, what would you predict the gross drug costs will be? (1 mark)

(4.2) Assume we are using this model: (8 marks)

GrossDrugCost = B0 + B1 * Risk Score + B2 * Age + B3 * Gender + ε

iv.      Provide a statistical interpretation of the coefficient and p-value for the gender variable. Provide a practical explanation of the information to senior management. (1 mark)

v.      Provide a statistical interpretation of the coefficient and p-value for the age variable. Provide a practical explanation of the information for senior management. (1 mark)

vi.      Provide a statistical interpretation of this model’s intercept. Provide a practical explanation of the information to senior management. (1 mark)

vii.      Compare the adjusted R-squared values between Models 1 and 2. Are they the same or   different? Why? What could you conclude about the differences (if any) in the adjusted R- squared values? (2 marks)

viii.      Senior management wants to know the expected gross drug costs of the average

customer. That is, for the median value of the RiskScore, age and gender, what would you expect the average gross drug costs to be? (2 marks)

ix.     A coworker wants to know what the predicted gross drug costs would be for a new

member. The new member is a 73-year-old who the government classifies as frail and he has a risk score of 510. Using the model above, what would you predict the gross drug     costs will be if they were a man and if they were a woman? (1 mark)

(4.3) Create a better model (4 marks)

x.      Develop a better regression model to predict gross drug costs. (2 marks)

xi.     What did you learn from this model that previous models did not tell you? (2 marks)