1. Durham Furniture Store

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 bonus. 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.

(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

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 based 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 (140 + 290+ 150) are 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.

furnitures

gym