Common Business Unit: Sales and Commission Essay

Submitted By igusjohn
Words: 1235
Pages: 5
Open Document
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 an IF and a VLOOKUP statement.) 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.


7. Columns must be wide enough to display data properly.

8. 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.)


10. Calculate the total sales for each month.

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

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

13. Format the spreadsheet so it is clear and easy to read. Use Calibri font in keeping with the organisation’s house style. Format currency columns to two decimal places.

14. Check all that formulae work correctly.

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

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

17. Develop a macro that will sort all sales persons into alphabetical order.

18. Develop a macro that will print only the sales summary information.

19. Assign these two macros to buttons at the top of the worksheet.


Part 2 – Branch Workbooks
1. Using the template created in Part 1 to produce a workbook for each branch using the sales data in
Show More

Related Documents: Common Business Unit: Sales and Commission Essay

Jimmy Takem's Business Model

There are numerous approaches a business will undertake to earn their revenue or establish their business model. Recently, Tommy Takem, owner and operator of Takem’s Appliances and Electronics, LLC, is questioning the business model he has adopted for his business. Takem has received a letter from his customer, Sally Walker, referring to his business as “unconscionable and therefore unenforceable.” Walker then threatens to sue Takem for punitive damages and will write letters to editors of local…

Words 743 - Pages 3

Robinson Patman Act Research Paper

(with criminal sanctions) may arise on sales that involve the following: • discrimination in price; • on at least two consummated sales; • from the same seller; • to two different purchasers; • sales…

Words 675 - Pages 3

Essay on Chapter 2 Study Guide

sheet. In a classified balance sheet, companies classify assets as current assets; long-term investments; property, plant, and equipment; or intangibles. They classify liabilities as either current or long-term. A stockholders’ equity section shows common stock and retained earnings. 2. Identify and compute ratios for analyzing a company’s profitability. Profitability ratios, such as earnings per share (EPS), measure aspects of the operating success of a company for a given period of time. 3. Explain…

Words 3348 - Pages 14

Essay Amazon: Depreciation and Amazon

Inc. Feifei Niu 701857352 Amazon.com, Inc. was incorporated in 1994 in the state of Washington and reincorporated in 1996 in the state of Delaware. The company industries are Retail-Catalog & Mail Order Houses. The number of shares of common stock outstanding as of January 16, 2015 are 464,383,939 with par value $0.01 per share and number of shareholders are 2744. Amazon has authorized 500 million shares of $0.01 par value preferred stock. No preferred stock was outstanding for any period…

Words 1263 - Pages 6

Precise Software Case Analysis Essay

new product? 4. Should we maintain a single sale force or separate sale force dedicated to the new product? What kind of commissions and how much authority should we set up for the sales force regarding to the new product? 5. How can we deliver the value of the new product to meet our customer expectation…

Words 1549 - Pages 7

ACC 561 Final Exam Study Guide Essay

of the following is an advantage of corporations relative to partnerships and sole proprietorships? Harder to transfer ownership. Lower taxes. Most common form of organization. Reduced legal liability for investors. Multiple Choice Question 64 The group of users of accounting information charged with achieving the goals of the business is its creditors. investors. managers. auditors. Multiple Choice Question 110 Which of the following financial statements is concerned with the…

Words 1346 - Pages 6

12 RetailersWholesalers Essay

Chater 12: Retailers-wholesalers Retailers, wholesalers, strategy planning Wholesalers and retailers - links in channel system. Consumers - shop at retail stores. Wholesaling - outside view of most consumers. Retailing: all activities involved in the sale of products to final consumers. Service retailing - retailer is also the producer. Reasons for buying from a retailer - retailer’s whole offering—assortment of goods, advice from salesclerks, convenience, and other factors—is its product. Ie: advice…

Words 1524 - Pages 7

Managing Financial Resources Essay

financial information, the budgeting cycle and short term decision process inside IBM, each on various levels from business unit to geo-level. The main finding is that the financial processes are very well structured within IBM, and lead to correct financial…

Words 5331 - Pages 22

Essay on Unit 1

American InterContinental University Online Financial Management Unit 1 Individual Project October 7, 2012 Abstract The New York Stock Exchange also known as NYSE along with the National Association of Securities Dealers Automated Quotation System that also known as NASDAQ will be compared and contrasted. Due to the stock exchange’s sheer size, laws have been proposed and passed such as the Public Company Accounting and Investor Protection Act of 2002 and the Sarbanes-Oxley Act of 2002…

Words 805 - Pages 4

MKTG 522 Final Exam 10 Essay

success? (Points : 25) A company's brand awareness is one of the most important process that will affect the company development. Consumers cannot buy products that they don’t know exist. Even if the overall brand name is familiar, it won’t help sales of individual products unless consumers know what products are available under that name. The cola market is a particularly strong example of this benefit. Consumers easily recognize different carbonated beverage brands, such as Pepsi, Coke, Mountain…

Words 5006 - Pages 21