Problem 6

This problem uses data in the file problem6.xlsx.

The file contains data on Sales Orders at an online office supplies retailer in Canada.

You need to establish a regression model with Profit ($) as the dependent variable. As independent variables use all the other variables in file.

The variables are as follows,

Order_ID

A running serial number for the orders

Profit

The profit (in $) associated with an order

Sales

The amount of Sales (in $) for the order

Discount

The customer discount (in %) given for that order [e.g. 4.00 = 4%].

Unit Price

The unit price of the order in $.

Shipping Cost

Shipping Cost in $

Ship Mode

The mode of shipping [Regular Air, Delivery Truck, Express Air]

Order Priority

The priority attached to the order [ Low, High]

Customer Segment

The customer segment [Consumer, Corporate, Home Office, Small Business]

Product Category

[Furniture, Office Supplies, Technology]

Please estimate a regression model with Profit as the dependent variable and all other variables as independent variables.

(a) Report the R-square of the estimated model

(b) All else being equal, which "Ship Mode" has the maximum positive impact on profits? Briefly explain your answer.

(c) Your Boss claims that for every additional dollar increase in the order [Sales], the profit goes up by 35 cents. Does your data support this claim?

Give reasons for your answer.

(d) The company believes that unit price increase per order of a dollar can be absorbed (i.e. it will not have any detrimental impact on profits) if it is accompanied by a Sales increase per order of 5 dollars. Use your data and regression to test this belief.

Please briefly show your working and relevant calculations.