Preview

Common Business Unit: Sales and Commission

Powerful Essays
Open Document
Open Document
1247 Words
Grammar
Grammar
Plagiarism
Plagiarism
Writing
Writing
Score
Score
Common Business Unit: Sales and Commission
Common Business Unit BSBITU402A Develop and use complex spreadsheets(Excel 2013)
Assessment 2

Instructions
You are required to use Microsoft Excel to create workbooks or templates as instructed below. Please follow instructions and submit all required workbooks, templates and printouts.

Scenario
Breeze Appliances have three branches across Australia: Melbourne, Sydney and Brisbane. Every quarter, each branch manager is required to calculate the sales commission each sales person achieves each month and send this information to head office. At head office the sales information is collated into one spreadsheet for analysis.
A template will be required for the recording and calculation of sales and commission for each branch. Head office also requires a template to analyse 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. (The data for each branch must be recorded in a separate workbook.)

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

2. Import the text file called Commission Rates.txt into a new workbook. Name the worksheet Commission Rates.
3. Create a named range for the commission rates data.

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.
4. Insert a new sheet before the Commission Rates worksheet. Name the new sheet Sales.

5. On the Sales worksheet, calculate the commission each sales person will receive each month.

6. 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. (This can be achieved by combining

You May Also Find These Documents Helpful

  • Good Essays

    the sales in the corresponding cells of the three monthly worksheets. Use 3-D references in the…

    • 369 Words
    • 2 Pages
    Good Essays
  • Good Essays

    BIS 155 Final

    • 2158 Words
    • 9 Pages

    1. (TCO 1) You work for a local construction firm, "DeVry Engineering Group" and your supervisor wants to test your knowledge and skills with Microsoft Excel and has instructed you to develop a spreadsheet to calculate weekly payroll for “15” employees with the following assumptions:…

    • 2158 Words
    • 9 Pages
    Good Essays
  • Satisfactory Essays

    Chid Abuse

    • 829 Words
    • 4 Pages

    In this project, you will modify a workbook for the HSP Computer Superstore to track the sales totals for 2011. You will use functions to calculate total sales, average sales, and median, minimum, and maximum sales values. Additionally, you will create a summary worksheet using values from the other spreadsheets in the workbook.…

    • 829 Words
    • 4 Pages
    Satisfactory Essays
  • Better Essays

    Midterm Comm293

    • 5099 Words
    • 21 Pages

    Required: (a) Determine the amount of revenue and gross profit that Brigante would record in each of the four years using the Completed Contract method. (4 marks) (b) Determine the amount of revenue and gross profit that Brigante would record in each of the four years using the Percentage of Completion method. Please round to the nearest million. (10 marks) Part B Costless Ltd. is a nationwide wholesaler who sells directly to consumers through its warehouse stores. While customers generally pay cash at the time of sale for most purchases, Costless sells some of its more expensive items (e.g., televisions) on “layaway”. Under a layaway arrangement, an individual contractually agrees to make a down payment equal to 20% of the purchase price. The customer then pays the remaining 80% two months later.…

    • 5099 Words
    • 21 Pages
    Better Essays
  • Good Essays

    ops335 lab 2 firewall

    • 695 Words
    • 4 Pages

    8. Optional: include a running total called “Total Book Sales:” which is saved to the file…

    • 695 Words
    • 4 Pages
    Good Essays
  • Satisfactory Essays

    Mktg 301

    • 4364 Words
    • 18 Pages

    7) Data from a small bookstore are shown in the accompanying table. The manager wants to predict Sales from Number of Sales People Working.…

    • 4364 Words
    • 18 Pages
    Satisfactory Essays
  • Powerful Essays

    Unit 1 P5 M2 Assignment11

    • 3712 Words
    • 11 Pages

    Your earlier newspaper article has been well received and the editor has asked you to write a follow up article regarding the impact of different economic environments for a business considering expanding overseas.…

    • 3712 Words
    • 11 Pages
    Powerful Essays
  • Good Essays

    Pre-Paid Legal Services

    • 805 Words
    • 4 Pages

    Q3) Based on the post-1995 commission formula and information in the case on pricing and commission rates,…

    • 805 Words
    • 4 Pages
    Good Essays
  • Powerful Essays

    Lions Insurance Case Study

    • 1450 Words
    • 6 Pages

    So you’ve got the marketing out of the way, and you have customers contacting you. The sales module is an efficient way to track the potential customers who contact your company through creating lists and providing information quickly to your sales representatives. It is the tool that will allow those leads to be followed up on in a timely manner. There is also quote processing included and the ability to add your price list for easy access. When a sales representative provides a quote it will upload the quote into the ERP…

    • 1450 Words
    • 6 Pages
    Powerful Essays
  • Better Essays

    What formula or function does he put in columns B through F in Row 13 that will give him the total expenses for the week in each category?…

    • 1278 Words
    • 5 Pages
    Better Essays
  • Satisfactory Essays

    The MedTech Company has been in business for four years. The sales force consists of 150 salespeople throughout the United States. The company sells patented drugs for skin cancer treatment. The patients will expire in three years. The Company has had good sales; however sales have not met company expectations. The sales strategy includes a 100 percent commission approach, and a limited reimbursement plan. The writer would completely overall the compensation program. The writer’s compensation program would include a salary and commission strategy, a direct reimbursement plan, and a contest. The salary and commission approach would include a yearly salary and a commission incentive for salespeople. The commission incentive would include a bonus…

    • 234 Words
    • 1 Page
    Satisfactory Essays
  • Satisfactory Essays

    Curled Metal Inc.

    • 341 Words
    • 2 Pages

    In quarter we allocated sales peoples salaries, bonuses, quotas and market share basing on the following information.…

    • 341 Words
    • 2 Pages
    Satisfactory Essays
  • Good Essays

    Types of Incentives

    • 421 Words
    • 2 Pages

    A percentage commission program can help motivate personnel to sell expensive items and to up-sell additional products to customers. Percentage commission programs can be used in addition to a standard base salary, or as the sole means of employee compensation.…

    • 421 Words
    • 2 Pages
    Good Essays
  • Satisfactory Essays

    Digitalthink

    • 373 Words
    • 2 Pages

    4. What should the relationship be between the quotas for individual salespeople, the total sales force, and the annual revenue plan for the company?…

    • 373 Words
    • 2 Pages
    Satisfactory Essays
  • Satisfactory Essays

    Vetements Ltee Case

    • 601 Words
    • 3 Pages

    Sales employees are paid a fixed salary plus a commission based on the percentage of…

    • 601 Words
    • 3 Pages
    Satisfactory Essays

Related Topics