Excel Expert

Activity 3: Workbooks and templates For this activity you will need to complete the following tasks (a) to (d). For the consistency of design and layout you must: • Ensure Columns are wide enough to display data properly and are evenly spaced. • Include a header containing the file name of the workbook. • 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.) • 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. • All headings are to be bold Task (a) Branch Template Based on the data above, you will need to create a template file to structure the style, format and in-built formulas needed for Breeze Appliances sales calculations. From this template, you will later be able to record and calculate the sales data for each branch, shown in the tables above. (The data for each branch must be recorded in a separate workbook as instructed in task b.) • Spend some time planning and designing your template and make notes about the style and planning of the template. • You will need to submit your notes as part of this activity to demonstrate how you: o evaluate the task, o determine methods to improve efficiency and o apply your knowledge of functions and features of the computer applications you are using To complete the task, you will need complete the following steps: • Import the text file called Commission [login to view URL] into a new workbook. Name the worksheet Commission Rates. • 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. • 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. (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 considered 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. • 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. • 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. • Develop a macro that will print only the sales summary information. • Assign these two macros to buttons at the top of the worksheet.