Common Business Unit: Sales and Commission

A template will be required for the recording and calculation of sales and commission for each branch. Head office also requires a template to analyses the sales data received from each branch.

Requirements

Part 1

Branch Template A workbook template will be created that will record and calculate the sales data for each branch shown in appendix 1.

Completed 1

Spend some time planning and designing your template. You may wish to Jot down the plan on a piece of paper.

• Create named range for the commission rates tat.

The Commission Rates data is used to look up the % commission each sales person will receive. This percentage will then be used to calculate the commission for each month, based on monthly sales.

• Insert a new sheet before the Commission Rates worksheet. Name the new sheet Sales.
•  On the Sales worksheet, calculate the commission each sales person will receive each month.
• Each month if a sales person equals or exceeds a 12% commission target, they receive an additional bonus of $1 ,250. Insert a column for each month to determine if the sales person will receive the bonus.

The commission target and the amount of bonus on offer will vary every quarter. Ensure that this is taken into account when designing the spreadsheet by positioning the bonus amount and the % commission target in separate cells at the top of the worksheet data. You will need to think of using absolute referencing in your formula as well.

• Columns must be wide enough to display data properly.
• Include a header containing the file name of the workbook. 9. Today’s date and a Branch name area must appear on the worksheet. The branch name will be filled in when the template is used.
• Calculate the total sales for each month.
• Save the workbook file as a template with an appropriate file name in the correct templates folder. Note that macros are required in the template.
• A summary of the sales for each of the three months recorded must appear on the worksheet. This summary must include average, maximum and minimum calculations for each month. All figures must be rounded to the nearest $100.
• Format the spreadsheet so it is clear and easy to read. Use Caliber font in keeping with the organization’s house style.

Format currency columns to two decimal places.

•  Check all that formulae work correctly.
• Ensure that the workbook template is set up to automate all calculations so the user only needs to enter the sales data for each month.
• Ensure users cannot enter data in formula cells by protecting these cells. Please use ‘password’ for your password when protecting the sheet as the marker needs to be able to access the formulae.
• Develop a macro that will sort all sales persons into alphabetical order.
• Assign these two macros to buttons at the top of the worksheet.

Part 2 – Branch Workbooks

Using the template created in Part 1 to produce a workbook for each branch using the sales data in appendix

1. for all three branches.
2. Ensure all data is checked to ensure accurate input.
3. Each workbook should be saved with an appropriate file name in your working folder. 0
4. Ensure the sales person data is sorted into alphabetical order and the summary information is printed for each workbook. Scan the reports into a PDF file to submit.

Part 3

Head Office Template Create a workbook template that will display the total sales for each month from each ranch to be used at Head Office. . Columns must be wide enough to display data properly.

1. Include a header containing the date (to the right) and a footer with the file name in the lower right corner.
2. Use Caliber font in keeping with the organization’s house style.
3. Save the workbook file as a template with an appropriate file name in the correct templates folder.
4. Total each column and row to determine the total sales for each month and each branch.
5. Create a chart that displays the sales for each branch over the three month period. Use an appropriate chart type and add a title.