Assignment: Automating Sales Compensation Calculations In Ex

Assignment: Automating Sales Compensation Calculations in Excel

The purpose of this assignment is to give you practice on designing a spreadsheet and using important Excel functions. This is the third of four Excel assignments this semester and this assignment is intended to apply what you practiced on the first two assignments. This assignment will require you to think about creating a spreadsheet so that a routine clerical process can be automated and completed easily in Excel. Similar issues come up in real life frequently. You may complete the assignment on your own or with one partner. Other than your assignment partner (if you choose to work with one), you may not use the help of other classmates, friends, parents, siblings, relatives, etc.

Any copying or obtaining help from anyone other than your partner (if applicable) will be treated as an Honor Code violation. You have a lot of leeway in how you put the spreadsheet together so my expectation is that no two submissions will look alike. This assignment is worth 12 points and will be graded based on how well the file accomplishes the task, as well as completeness, neatness, apparent effort, and the ease of understanding your work. Please submit your completed assignment through the Assignments tab on Isidore prior to 9:00 am on November 7th. Be sure to read and follow all instructions! Not following instructions will significantly lower your score.

Assignment Overview

Assume that you work in the Accounting department for a small company that sells three products. The company has four salespersons who are compensated monthly based entirely on commissions and bonuses. Previously, the monthly sales compensation was calculated manually by a clerical employee. A new Controller has tasked you with creating an Excel spreadsheet to automate these calculations.

The salespersons are paid based on the following policy:

  • 5% commission on the selling price of Product H (highest-margin product)
  • 4% commission on the selling price of Product M (second-highest-margin product)
  • 3% commission on the selling price of Product L (lowest-margin product)
  • A bonus of $100 for each new customer to whom they make a sale

The data for this assignment comes from a downloaded sales report for September, listing each sale by date, salesperson, product, selling price, and order number in the customer’s history.

Task Details

Your task is to create a dynamic and automated Excel file that calculates each salesperson’s compensation for the month. The solution should include:

  • One worksheet per salesperson that contains all sales made by that individual during the month
  • A summary section at the top of each worksheet showing total sales revenue by product, commission earned per product, total commission, and bonuses for first-time customers
  • Formulas that reference data rather than manually entered numbers
  • The ability to copy and update the workbook for future months with minimal manual effort
  • Use of macros to automate the process of creating the individual worksheets and populating them based on the raw data
  • Clear labeling and professional formatting for ease of understanding and auditability

Specifically, you should:

  • Plan out the steps to create a worksheet for each salesperson before recording macros.
  • Create a macro (or macros) that automates the creation of individual sheets and populates data accordingly.
  • Ensure each individual sheet contains relevant data filtered by salesperson, with calculations based on the given policies.
  • Include a summary section that aggregates monthly compensation details, including sales by product, commissions, and bonuses.
  • Format worksheets to be professional and uniform.
  • Test your macro(s) by deleting the dynamically generated sheets and regenerating them to verify accuracy.
  • Save the workbook as a macro-enabled file (.xlsm).

Additional Guidelines

Begin your project with a "dry run": manually create one or more sample sheets based on the raw data, document your steps, then record macros to replicate the process automatically. Your spreadsheets should handle future data with minimal adjustments, assuming the same number of sales transactions per user, although the actual data will differ month to month. The macro(s) should be well-defined with shortcut keys and descriptive comments. Submission must be a macro-enabled workbook demonstrating your automation process and correct calculations.

References

  • Bowen, L. (2020). Excel VBA Programming For Dummies. Wiley.
  • Hahsler, M. (2019). Professional Excel Development: The Definitive Guide to Developing Applications Using Microsoft Excel & VBA. Addison-Wesley.
  • Walkenbach, J. (2018). Excel VBA Programming For Dummies. Wiley.
  • Harvey, J. (2021). Excel Data Analysis for Dummies. Wiley.
  • Gaskin, J. (2022). Mastering Excel VBA. Packt Publishing.
  • Jelen, B. (2020). Microsoft Excel Data Analysis and Business Modeling. Microsoft Press.
  • Collins, H. (2020). Automating Excel with VBA. McGraw-Hill Education.
  • O’Connor, P. (2021). Effective Data Management in Excel. Routledge.
  • Gardner, C. (2022). Practical VBA for Excel. Packt Publishing.
  • McFedries, P. (2019). Excel Formulas and Functions For Dummies. Wiley.

Related Assignments