MQBS1030 - Decision Making for Business Assessment 2

PART A: DATA EFFICIENCY

QUESTION 1 

• For the provided dataset, create Named Ranges and convert data into a Table.

• Add a row displaying averages for each of the quantitative variables. Provide a screenshot that shows the averages you have calculated.

• Insert Slicers for Region and Product Category.

• Personalise your Table and Slicer designs.

• Filter your Table and Slicers to display only your assigned Region and Product Category, as shown in the table below.

• Provide a screenshot that shows your Table and Slicers. Include your student number in the screenshot.

PART B: DESCRIPTIVE STATISTICS

QUESTION 2

• MACQUARIE University

• Remove any filters applied to Region. For Revenue within your assigned Product Category, calculate and report: Mean, Median, Mode, Minimum, Maximum, Range, Standard Deviation. 

PART C: LOOKUP AND LOGIC FUNCTIONS

QUESTION 3 

Create a 'Target Customer Group' column using a lookup function by categorising Customer Age using a lookup function as follows:

1. Youth (if Age <30>

2. Adult (if Age 30-50)

3. Senior (if Age >50)

Then create a column using a formula to label customers as Loyal Customer (Adult and Products Sold > 5) or Standard Customer.

State both your formulas and provide a screenshot that captures the first 10 rows of data, complete with these new columns.

PART D: PIVOTTABLES AND PIVOTCHARTS

QUESTION 4

Create a PivotTable showing Total Revenue by Region and corresponding PivotChart (Column Chart). Provide a screenshot of both.

QUESTION 5 

Create another PivotTable showing Average Discount by Product Category. Insert a Slicer for Customer Age Group. Select the 30-50 age group and provide a screenshot of your slicer and PivotTable.

PART F: REGRESSION

QUESTION 6 

• Run a simple linear regression with Revenue as the dependent variable and Discount as the independent variable. Provide the appropriate scatterplot and regression output.

• State the regression equation and interpret the slope coefficient (including its statistical significance). Comment on the R-squared value. Finally, comment on the overall regression output. (100 words).