Description

Instructions

Predictive Analytics: ASSIGNMENT

DIBA – Predictive Analytics Page 1 of 2
Total Marks: 50
Due date: Refer Blackboard Assignment Link Submission: Online through Blackboard
Submission format: Word or PDF file incorporating all Excel outputs (for questions 1 to 4)

and Excel file for question 5.

INSTRUCTIONS:
• This assignment must be done in pairs (2 members per group)
• Names of both group members must be indicated on the first page of your submission.
• Excel outputs must be provided for all questions.
• For Questions 1 – 4, you can copy and paste Excel outputs to your answer.
• For Question 5, you must submit the Excel file.
• Each question is worth 10 marks. Total 50 marks for this assignment.

1) Sales of a product is generally related to the advertising cost. The Excel file “Sales” contains data
on the amount of sales and the advertising cost of a product. You need to find out if sales of this
product can be predicted using the advertising cost.
a) By choosing sales (y) as the dependent variable and advertising cost (x) as the independent
variable, construct a scatter plot. (2 marks)
b) Use the Excel Trendline Tool to find the best fitting functions to this data. (2 marks)
c) Write down the simple regression model. Would you use this model to make predictions of
sales based on the advertising cost? Justify your answer. (2 marks)
d) Check for outliers in the data. If there are any, delete them and update the regression
model. (2 marks)
e) Use the regression model obtained in part (d) above to estimate the sales when the
advertising cost is $400. (2 marks)
2) A company would like to measure the effectiveness of different types of advertising media in the
promotion of its products. Specifically, the company is interested in the effectiveness of radio
advertising, newspaper advertising, TV advertising and Internet advertising. During a one-month
test period, data were collected from a sample of 30 cities. The sales of the product and the
amount of media expenditure is recorded. The excel file “Advertise” contains data collected
from these 30 cities. You have to develop a multiple regression model using sales as the
dependent variable and all other variables as independent variables.
a) Provide the excel output and the multiple regression equation. (2 marks)
b) Interpret the meaning of the regression coefficients. (2 marks)
c) Calculate the correlation matrix and check for multicollinearity. (1 mark)
d) Check the significance of the entire model. (1 mark)
e) Check the significance of individual regression coefficients. (2 marks)
f) Suggest a revised regression model if required. (2 marks)

Predictive Analytics: ASSIGNMENT

DIBA – Predictive Analytics Page 2 of 2
3) The excel file “Temperatures”, contains data on monthly minimum air temperature for the years
2018 to 2022.
a) Plot the time series for this data. (2 marks)
b) Compute a 2-period and a 3-period moving average forecast. (4 marks)
c) Compute an exponential smoothing forecast with smoothing constant  = 0.3. (2 marks)
d) Do you recommend using simple regression to forecast the monthly minimum air
temperature? Explain your answer. (2 marks)
4) Consider the transactions data in the Excel file “Groceries”. You are required to develop a
market basket analysis.
a) Calculate the support, confidence and lift of the rules: (4 marks)
“If Broccoli and Onions then Tomatoes.”
“If Onions and Tomatoes then Broccoli.”
b) Find all association rules with 2 items X and Y of the form “If X then Y” with minimum
support 0.5 and minimum confidence 0.9. (6 marks)
5) A firm is the only producer of two products, A and B. The sales of these products are not
independent of each other. The relationships between the quantity sold (N) and prices (P) of
each product are given by the formulas:
Product A: NA = 1300 − PA + PB
Product B: NB = 2600 + PA − 4 PB
The total cost (TC) for the firm is given by the formula
TC = 50000 + 1400NA + 700NB
The firm sells the two products at prices PA = $2000 and PB = $800.
The total revenue (TR) and profit (PR) are given by the formulae:
TR = PA NA + PB NB
PR = TR − TC
a) Develop a spreadsheet model to calculate the total revenue (TR), total cost (TC), and profit (PR).
(5 marks)
b) Create a two-way data table to estimate the profit with input variables the price of Product A and
price of Product B. Use the following values for price of A and price of B: (3 marks)
Price of A $1500 $1600 $1700 $1800 $1900 $2000
Price of B $850 $900 $925 $950 $975 $1000
c) Suppose the price of product A is fixed at $2000. Copy your spreadsheet model to a new worksheet,
and use Goal seek to find the price of Product B that will result in a profit of $200,000. (2 marks)

– END of Assignment –