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: toursrus@bigpond.com
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:
|
Customer
|
Departure Date
|
Tour No
|
Description
|
Amount
|
Early Payment Discount
|
|
12000
|
14 April
|
LRL130
|
5 days England Highlights
|
$3000
|
12 months
|
|
13000
|
4 August
|
SE400
|
20 days Highlights of Europe
|
$4000
|
6 months
|
|
14000
|
1 September
|
EA300
|
15 days Italian Splendour
|
$5000
|
6 months
|
|
15000
|
4 February
|
LRL140
|
35 days European Discovery
|
$6000
|
10 months
|
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%
|
Tour
|
Qtr 1 Sales
|
Qtr 2 Sales
|
Qtr3 Sales
|
Qtr4 Sales
|
|
EG200
|
15 450
|
10 650
|
6 780
|
12 300
|
|
AE250
|
24 900
|
28 600
|
13 000
|
9 800
|
|
LRL130
|
17 500
|
34 200
|
6 800
|
26 750
|
|
EA300
|
32 200
|
25 000
|
18 000
|
7 340
|
|
ME350
|
14 650
|
21 250
|
8 500
|
12 450
|
|
SE 400
|
23 000
|
15 760
|
14 500
|
9 900
|
SHEET 2
Commissions 200X
Consultant: Mary Murphy
|
Tour
|
Qtr 1 Sales
|
Qtr 2 Sales
|
Qtr3 Sales
|
Qtr4 Sales
|
|
EG200
|
10 600
|
16 430
|
3 700
|
12 000
|
|
AE250
|
9 500
|
3 740
|
5 600
|
9 700
|
|
LRL130
|
15 750
|
5 600
|
12 400
|
8 650
|
|
EA300
|
24 000
|
13 500
|
7 400
|
11 500
|
|
ME350
|
17 400
|
14 300
|
10 200
|
12 250
|
|
SE 400
|
18 950
|
20 600
|
14 240
|
17 800
|
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