Excel Assignment (Commerce 1DA3)

Please make sure to review the guidelines on this page before reviewing the questions. The deadline to submit the Excel file is 11:59PM, on Tuesday April 08.

Before you start the assignment

1.   Problem 2 of the assignment uses Data Analysis Toolpak. Please download the instructions to load it in MS Excel here. Note: If you do not have access to MS Excel, you can use MS Excel Online on Office Online. Download and review the instructions to access Office Onlinehere.

Submission Guidelines:

1.   Download the Excel file (template) for the assignment from the Avenue to Learn folder.

2. For Problem 1, to customize the data for each student, please enter the last 4 digits of your student ID in the Cells F2, F3, F4 and F5 before starting the analysis and creating the PivotTable. You mark for this question will be based on this step! For example, if your student ID is 400568015, you need to enter 8015 in each one of the 4 cells (F2, F3, F4, F5) before starting the analysis. If last 4 digit of student number start with 0s, the excel will display only the significant digits. For example, 0002 will display only as 2, and that is ok.

3. For Problem  1, make sure that the pivot table you created (for any of the questions) is included in the submission file. For each question, copy the data generated by PivotTable, paste it into the sheet named “Problem 1 – Visualizations” and create the visualization in that sheet. Do this for each of the three questions asked in Problem 1. This means, the data generated by the PivotTable and the visualization attached to it for all three questions can be found in the sheet “Problem 1 – Visualizations”.

4. For Problem 1, make sure that the data, based on which you create the visualization is there and the visualization is live and attached to the data. Please do not paste screenshots of the visualizations.

5. For Problem 2, to customize the data for each student, please enter the last 4 digits of your student ID in the Cells A2, A3, A4 and A5 before conducting the test. You mark for this question will be based on this step! For example, if your student ID is 400560218, you need to enter 218 in each one of the 4 cells (A2, A3, A4, A5) before starting the analysis. If last 4 digit of student number start with 0s, the excel will display only the significant digits. For example, 0002 will display only as 2, and that is ok.

6.   For Problem 2, make sure the report is placed in Cell D15. Also, provide your answers in the textbox that is already placed in the Excel sheet. If for any reason you cannot add your answer to the textbox, add a new textbox and enter your answers in that.

7.   Please note that this assignment is an individual component, and no sharing of files and results is allowed.

8.   You  can  find  the  submission  folder  on  Avenue  to  Learn  under Assessments > Assignments.

9.   Make sure your submission to Avenue is a single MS Excel file (extension .xls or .xlsx). We will not be able to open files in other formats. It is your responsibility to ensure that the file you upload is in the correct format, and can be opened without any issues. You may want to download the file from Avenue to Learn after uploading it, and make sure it can be opened, and it contains your answers.

Problem 1

As the marketing manager of a fast fashion online retailer, you need to prepare key data points for an upcoming visit from the Chief Marketing Officer (CMO). The CMO is particularly interested in analyzing the company's spending patterns. You have been provided with the purchase data in an excel file for the period January 1, 2025, to January 31, 2025. The database includes six data variables:  PO  Number,  Vendor,  Purchasing  Department,  Contracted  Vendor,  Emergency Purchase, and PO Amount. Their descriptions are as follows:

• PO Number: Each time the company makes a purchase, the relevant department must issue a purchase order (PO). A PO number is a unique identifier assigned to each purchase order (PO) for tracking purposes.

• Vendor: The supplier from which the purchase is made.

• Purchasing Department: The department responsible for initiating the PO and procuring goods or services from the vendor.

• Contracted Vendor: A categorical variable indicating whether the vendor is contracted or non-contracted.

o Contracted vendors have pre-negotiated agreements with the company, ensuring standardized terms, pricing, and reliability.

o Non-contracted vendors do not have such agreements, leading to potentially higher costs and variable terms.

• Emergency Purchase: A categorical variable indicating whether the purchase was made under urgent circumstances.

o Emergency purchases occur when there is an immediate need for goods or

services due to unforeseen events, bypassing standard procurement procedures.

• PO Amount: The total amount payable to the vendor upon receipt of the goods or services by the purchasing department.

For this problem, you need to create three visualizations. Each visualization will be created based on data generated by the Pivot Tables in Excel. Using the PivotTable tool, generate a visualization for each of the following questions. You do not need to add axis labels or a plot title.:

a)   Create a bar graph to display the sum of PO Amount for each purchasing department. Make sure the variable Purchasing Department is placed on the horizontal axis.

b)  Create a bar graph to display the average of PO Amount for each vendor. Make sure the variable Vendor is placed on the horizontal axis.

c)   Create a pie chart to display the sum of PO Amount for each category of emergency procurement, i.e., the total PO Amount for orders where Emergency Procurement was equal to Yes and total PO Amount for orders, where the Emergency Procurement was equal to No.

Problem 2 (requires data analysis toolpak)

As the district manager for a retail clothing chain, you manage two different stores in your district. You want to determine if the average daily sales differ between two of the stores: Store X, located in a shopping mall, and Store Y, situated in a downtown area. Using the data provided in the Excel sheet named “Problem 2 – data”, conduct a two-sample t-test and provide your conclusion at the 5% significance level. Make sure to provide the test result and the answer to the questions directly in the Excel sheet. For the two-sample t-test, assume unequal variance.

Note: MS Excel uses the scientific exponential format to show very small and very large numbers. For example,  1.23E-12 means  1.23 × 10一12.  Similarly,  1.23E+12 means  1.23 × 1012.  The p- value may be represented using this format.