Bsbitu402 - create a spreadsheet workbooks according to the

Question

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: [phone]
Fax: [phone]
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:

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