Reference no: EM131592199

Design and Develop Complex Spreadsheets assignment.

Scenario

You are the Administrative Assistant for Ms Millicent McGee, Managing Director of TOURS R US. You have been asked to create a spreadsheet workbooks according to the company's policy and procedures.

Policy and Procedures and Style Guide

TOURS R US

We specialise in escorted tours throughout Europe.

Suite 24, 356 Exhibition Street, Melbourne VIC 3000 (ABN 123 456 789)
Phone: 03 9870 5678
Fax: 03 9870 5679
Email: [email protected]

Corporate Logo - stored in folder COMPANY LOGO
Corporate Colours - Blue and Tan
Corporate Font - Calibri

Footer - 8 font (all worksheets require your name at left, filename and path on right)

File naming conventions:

Folder called INFORMATION is to contain Charges and Bookings worksheets
Folder called INVOICES is to contain template and prepared invoices
Folder called CONSULTANTS is to contain all information relating to Consultants

TASK 1 - Template

Ms McGee requires you to design and prepare a template to use as an invoice to send to all customers who have booked tours with them. The company logo is required to be inserted. The template is to contain basic formulas.

TOURS R US

(Address and contact details)

INVOICE:

Account No:

Customer Name:

Address:

Date:

Travel Date:

Code                                  Description                                         Amount

 Total

Discount for early repayment:

TOTAL AMOUNT DUE:

TASK 2 - INVOICES

You are to prepare invoices for those customers whose details are contained in the file CUSTOMERS. By entering the Account No, you should have the name and address of the customer appear. Each invoice is to be prepared on a separate sheet and the sheet is to be renamed each customer surname.

Prepare invoices using linking and formulas (including VLOOKUP formulas) as follows:

Printouts required:

- Template and formulas
- Invoices and formulas  

TASK 3 - Commissions

You are required to set up a workbook which will record the commissions received by each of the two travel consultants employed by Tours R Us. Each consultant will be on a separate sheet, appropriately named. You are required to show the total quarterly sales and commission of each tour, together with the total sales and total commission received.

SHEET 1

Commissions 200X
Consultant: Angela McKenzie Commission 5.5%

SHEET 2

Commissions 200X
Consultant: Mary Murphy

On a separate sheet you are required to show the total quarterly sales for each consultant together with the total commission they received. Each consultant is expected to bring in over $120,000 worth of sales each quarter. Insert a formula which will give the reading ‘Target Met' or ‘Target Not Met' under each quarter.

Printouts are required of each sheet in the workbook, together with formulas.

TASK 4 - Chart and Macro

You are to prepare a suitable chart showing each consultant's quarterly sales figures. This chart is to be on a separate sheet and needs to be clearly labelled and formatted according to the company's Style Guide.

You are to prepare a macro which prepares the chart for printing and prints the chart.

Attachment:- Design-and-develop-complex-spreadsheets.rar