DPBS1110/BMGT1310
Evidence-Based Problem Solving
Assessment: Excel Training Program 1
Instructions:
1. Use Zoom or other recording software to record a step-by-step Excel video based on Excel questions below.
2. Start by introducing yourself (fullname and zid).
3. Record your screen when answering the Excel questions below along with a brief commentary. You need to turn on your camera at all time.
4. Estimated length of time for this video is 10 – 15 minutes. Your video should not exceed 20 minutes in length,
Question 1
Using the dataset labelled “Furniture Sales”
Step 1: Change the “Unit Price” column to display a dollar sign ($) using either "Accounting Number" or "Currency" format.
Step 2: Ensure both the “Delivery Date” and “Sale Date” columns are in “dd/mm/yyyy” format.
Step 3: Create a new column named “GST” (Goods and Services Tax), calculated as 10% multiplied by “Unit Price”.
Step 4: Create a new column called “Total Sales Price”, calculated as the “Unit Price” + “GST”
Step 5: Create a new column named “Delivery Time” to represent the number of days taken for delivery after ordering. Calculate this by subtracting the “Sales Date” from the “Delivery Date”. Format the result as an integer.
Question 2
Using the dataset labelled “Delivery Time”
Step 1: Select the entire “Delivery Time (days)” column, then navigate to the "Data" tab, click on "Data Analysis", and choose "Descriptive Statistics". This process will generate a table with key descriptive statistics such as:
• Mean
• Median
• Standard Deviation
• Minimum
• Maximum
• Range (Max-Min).
Step 2: Create a histogram in Excel to visually represent the distribution of delivery times. Hint: Select the entire "Delivery Time (days)" column, clicking “Insert”, and choosing “Histogram”. Set the Bin Width to 1 (X-axis --> Format Axis --> Axis Options --> Bins). Don’t forget the label your axes and chart title.
Step 3: Create a PivotTable in Excel to calculate the average delivery time for each freight carrier by delivery month.
Hint: Select the entire Excel data, click "Insert", and then "Pivot Table". Choose where you want to put your Pivot table. Next, inside the PivotTable fields, drag "Freight Carrier" in the "Columns" area, "Delivery Month" in the "Rows" area. Then, drag "Delivery Time (days)" in the "Values" area, and choose "Average" to display the average delivery time.
Question 3
Using the dataset labelled “Voucher Allocation”
Step 1: Create 2 new columns named “$25 Discount Voucher” and “$20 Discount Voucher” to determine whether each customer is entitled to receive a voucher based on the criteria listed below:
$25 Discount Voucher: Awarded to customers having more than 5 purchases and total spending exceeding $1000
$20 Discount Voucher: Allocated to customers identified as “Environmentally Conscious” or “Senior Citizen” .
Hint: Use a combination of the “IF” and “AND” functions for the $25 Discount Voucher and “IF” and “OR” functions for the $20 Discount Voucher.
Step 2: Create a new column named "Promotional Voucher" to determine the total voucher amount allocated to each customer. Use the values from the “$25 Discount Voucher” and “$20 Discount Voucher” .
Step 3: PivotTable is a highly effective visual tool that allows us to display key Excel data based on our requirements. It is commonly used by managers in today's workplace. For this reason, we will create a PivotTable to help you visualize all the vouchers you have created.
Select the entire Excel data and insert a PivotTable. In the PivotTable Field List, drag "Customer Type" into "Rows." Then, drag "Promotional Voucher" into "Values" and select "Sum." This PivotTable will display the total voucher value for each customer type.