Faculty of Business and Information Technology
Assignment 1
Value: 10%, Due: Sept. 29th @ 11:59 PM
This assignment should be completed individually. We take academic integrity seriously. Any
student found to be involved in plagiarism or cheating will be penalized in accordance to the
Ontario Tech University Calendar, Section 5.15.
To assert that you have not given or received, or used unauthorized assistance, write the following
pledge in the comment textbox when you submit your files through Canvas system.
"I have not given, received, or used any unauthorized assistance."
1. Durham Furniture Store (50 Points) Data File needed for this exercise:
Furnitures.xlsx
Problem: You have recently been hired as the sales manager at a local furniture store called Durham
Furniture. You have been asked to analyze the Excel workbook detailing last month’s sales to determine
total sales, total profit, average sales price, and average profit. In addition to an hourly wage, sales
employees earn a commission on the furniture they sell, and a periodic bonus based on total sales. You
have also been asked to determine the commission for each sale and which pieces furniture sat on the
show floor the longest before being sold. You need to total the sales for each salesperson and calculate the
onus. Lastly, you have been asked to combine the first and last names of the salesperson columns for
paycheck purposes.
Enter Your Name and Student ID
(a) Open the workbook named Furniture.xlsx and save it as Durham Furnitures.xlsx.
(b) In the Documentation worksheet, in cell A1, enter the title Durham Furnitures. In cell
B3 enter your name. In cell B4 enter your student number. In cell B5 enter the
completion date of your assignment and in cell B6 enter a sentence to describe the
purpose of the workbook.
(c) Save your workbook.
Preliminary Calculations
(a) Navigate to the Sales Data worksheet and Insert three new rows above row 1.
(b) In cell A1, enter the title “Durham Furnitures”.
(c) Insert a new column between Last Name and Date Purchased and add a label “Full
Name”. Use a function to combine the salespersons’ first and last names. Use a function
to ensure proper capitalization. AutoFill the formula for the entire column.
(d) In cells I5:L5, enter appropriate formulas and/or functions to compute the Days on the
Floor, Profit and the Commission Amounts according to the following rule.
• Days on the Floor is calculated based on Date Purchased and Date Sold columns
• Profit is calculated based on Cost and Sales price columns.
• Commission Amount is calculated based on the Sales Price and Profit columns. If the
profit is greater than $400, the salesperson receives 5% commission on the Sales Price; if not,
the salesperson receives 3% of the sales Price.
(e) Use the fill handle to copy the formulas down to row 31.
(f) Save your workbook.
Summarize Data
(a) In cells B35:B39 enter appropriate functions to calculate the Total Sales, Total Profit, Average
Sales Price, Average Profit and Total Commission.
(b) In cells G35:K38, enter appropriate functions to calculate the, # of Items Sold, Total
Revenue, Total Commission, Bonus Amount, and Total Earning for each salesperson.
The Bonus Amount is calculated based on the Bonus Amounts listed in Table 1.
Table 1: Bonus Amount
Total Revenue Bonus Amount
$500 $0.00
$500 - $1500 $25.00
$1500 - $2500 $50.00
$2500 - $3500 $75.00
$3500 - $4500 $100.00
$4500 - $5500 $125.00
5500 $150.00
(c) Save your workbook.
Charts and Finalize the Workbook
(a) Create a bar chart based the information computed in cells H35:H38.
(b) Create a pie chart based the information computed in cells K35:K38.
(c) Add appropriate title, labels, and axis titles to both charts.
(d) Format your workbook as desired (Headings, Font, Fill color, Number format…etc.).
(e) Create a footer with your name on the left side, the sheet name code in the center, and the
file name on the right side.
(f) Save and close the workbook.
2. Durham Gym (50 Points) Data File needed for this exercise: Gym.xlsx
Problem: As the newly hired manager at a local gym, Durham Gym, you have decided to chart
various trends over the past year to review growth in a number of class offerings versus attendance,
and types of memberships to analyze how the gym is doing.
Enter Your Name and Student ID
(a) Open the workbook named Gym.xlsx and save it as Durham Gym.xlsx.
(b) In the Documentation worksheet, in cell A1, enter the title Durham Gym.
(c) In cell B3 enter your name. In cell B4 enter your student number. In cell B5 enter the
completion date of your assignment and in cell B6 enter a sentence to describe the
purpose of the workbook.
(d) Save your workbook.
Preliminary Calculations
(a) Navigate to the Membership worksheet and enter a function in cell N3 to calculate the annual
average class spot offered for the year 2020. AutoFill the formula for the entire column.
(b) In rows 9 and 10, enter a function/formula to calculate the total number of gym members
and % of new users for each month. The total number of gym members is calculated
ased on the annual, monthly, and pay-per-use membership. Note that the number of new
users is included in the membership numbers. For example, for the month of January, 143
out of 580 members XXXXXXXXXXare new users.
(c) Save your workbook.
Charts and Finalize the Workbook
(a) Create a line chart for the number of Class Spots Offered and Class Total Attendance over the 12-
month period. Add a Linear trend line for Class Total Attendance.
(b) Create a 3-D pie chart for the Yearly Averages for Annual, Monthly, and Pay-Per-Use
Membership types. Place the chart to the right of the existing chart.
(c) Create a Clustered Column – Line on Secondary Axis combo chart for Total Gym
Members and New Users over the 12-month period. The Total number of Gym Members
should be the primary axis and the number of New Users as the secondary axis. Place the
chart below the existing line chart.
(d) Add appropriate chart title and axis title to the three charts.
(e) Insert a new column to the left of January column and add a column label, “Trend”. In cell B3,
insert a Line Sparkline over the 12-month period in the Sparkline column for the Class Spots
Offered row. Do not include Yearly Averages. Fill the sparkline down the column.
(f) Format your workbook as desired (Headings, Font, Fill color, Number format…etc.).
(g) Save and close the workbook.
Submission Guideline:
Using the Canvas system, please submit your solution files by no later than Sept. 29th, 2020 at
11:59PM.