Brief Guidelines: Download the Special Assignment from Moodle

Subject:MS ExcelPrice:16.99 Bought3

Share With

Brief Guidelines: Download the Special Assignment from Moodle.  Name the file Special Assignment (your initials). IMPORTANT:  Please open a word document and called it “Evidence”.  For any question that requires you to print; set the document up as per the question and take a screen shot of it in “print preview” mode and paste into the “Evidence” document. This must be uploaded to Moodle at the end. Scenario: Skyline is an independent budget airline, which specialises in carrying passengers from Dublin to a limited number of European capital cities. There are special deals currently on offer to London, Paris, Madrid and Brussels. There are two fare types, Low and Business. Passengers may qualify for a discount, under certain conditions.  You have been asked to create a spreadsheet to track tickets sales for June 2017. Assessment Criteria: Creating (30marks) Edit (5 marks) Organise (5 marks) Save + Print (5 marks) Submissions: Once completed, upload to Moodle using the upload point provided.

Learner name:
Date of issue:  12.04.2021.	Due date & time:   19.04.2021.

 

Authorship Statement	Initial each section
I confirm that I have read and understood all policies and procedures regarding project and assignment submission, plagiarism etc as described in the Learner Handbook.
I confirm that I have retained a copy of this project/assignment for my own personal use and in the event of an additional copy being requested by the subject teacher or the college.
I hereby declare that all work included in this project/assignment except where referenced is entirely my own. All sources of information have been acknowledged by means of reference as required.
Signed:	Date:

This briefing document must be attached to the element of assessment.

 

All monetary data should be displayed in currency format € to two decimal places.

All files should be saved as per instructions on cover sheet.

 

1. Set up the spreadsheet and input the data as shown in Figure 1 with alignments and formatting as shown and appropriate column widths.

 

	A	B	C
1	Skyline
2
3	Flight Bookings June 2017
4
5	Name	Fare Type	Price
6	O’Reilly F	Business
7	Connolly J	Business
8	Carroll P	Business
9	Fahy S	Low
10	Johnston M	Low
11	Keane R	Business
12	Robinson M	Low
13	Dunne D	Business
14	Fitzgerald N	Low
15	West H	Low
16
17
18		Total:
19		Average:
20
21	Date:

       Figure 1

 

2. Insert today’s date from the computer clock (using a formula) into the cell B21 to the right of the heading Date:

 

3. Insert your own name as a footer.

 

4. Use the IF function in cell C6 to calculate the ticket Price based on the following information:

If the Fare Type is Low then the ticket price is €60; otherwise the price for Business is €100.

Replicate the formula to cells C6:C15.

 

5. Use the SUM function to calculate total income for all ticket sales and display in cell C18 to the right of the heading Total.

 

 

6. Use the AVERAGE function to calculate the average ticket cost and display in cell C19 to the right of the heading Average.

 

 

7. Rename Sheet1 as Customer Records.  Save the spreadsheet as Flight 1.

 

 

8. Produce the following printouts:
   1. The whole spreadsheet excluding the main heading.
   2. Spreadsheet showing all formulas and row/column heading identifiers.

 

 

9. Delete the record for passenger Keane R from the spreadsheet.

 

 

10. Input the additional information as shown in Bold print in Figure 2 below.  Delete the data in the Total and Average cells and move the labels to their new position.  Move the date to its new position.

 

 

	A	B	C	D	E	F	G	H	I	J
1	Skyline
2
3	Flight Bookings June 2017
4
5	Name	Depart Date	Destination	Fare Type	Price	Baggage Weight (kgs)	Excess Baggage Charge	Discount	Taxes & Charges	Ticket  Price
6	O’Reilly F	14/06/17	London	Business	€100.00	12
7	Connolly J	22/06/17	Paris	Business	€100.00	16
8	Carroll P	01/06/17	Madrid	Business	€100.00	45
9	Fahy S	01/06/17	Brussels	Low	€60.00	21
10	Johnston M	09/06/17	Paris	Low	€60.00	38
11	Robinson M	16/06/17	London	Low	€60.00	20
12	Dunne D	16/06/17	Paris	Business	€100.00	30
13	Fitzgerald N	17/06/17	Madrid	Low	€60.00	16
14	West H	06/06/17	London	Low	€60.00	13
15
16
17									Total:
18	Date:								Average:
19
20		Taxes & Charges
21		Destination	Rate
22		Brussels	5
23		London	8
24		Madrid	6
25		Paris	7

Figure 2

 

 

11. Insert the following record for Coffey B , replicating formulas as necessary.

 

 

Name	Depart Date	Destination	Category	Baggage Weight
Coffey B	17/06/17	Brussels	Business	36

 

 

12. Use an IF function to calculate the charge, if any, for excess baggage weight:

a)           If baggage weighs more than 30 kgs there is a charge of €20;

b)           If baggage weighs between 20 and 30 kgs the charge is €15;

c)            Otherwise there is no charge.

 

13. Use an IF function to calculate the Discount, if any, based on the following:

a)         If Destination is Brussels and Fare Type is Business, there is a discount is 10% of the price;

b)         If Destination is Paris and Fare Type is Business, then the discount is 5% of the price;

c)          Otherwise there is no discount.

 

14. Use a LOOKUP function to input the appropriate Taxes & Charges.  The rates, in Euro, are shown in the table on the bottom of the spreadsheet. Name the range of cells B22:C25 as taxes_charges.

 

15. Use a formula to calculate the Ticket Price as Price plus Excess Baggage Charge plus Taxes & Charges minus Discount.

 

16. Use the SUM function to calculate the total for the Ticket Price.

 

17. Use conditional formatting to emphasise total ticket prices greater than €100 in Green Fill with Dark Green Text.

 

18. Use the AVERAGE function to calculate the average Ticket Price in the appropriate cell.

 

19. Sort the spreadsheet by Depart Date from oldest to newest and then by Name from A to Z.

 

20. Format data in the Customer Records worksheet using the styles listed below:

 

Range of cells	Cell style
A1	Heading 1
A3, A18 , I17, I18,     A5:J5	Accent 1
J17, J18,     A6: J15	20% Accent1

 

 

 

 

 

21. Save the spreadsheet as Flight 2.

 

 

22. Produce a printout in landscape orientation of the spreadsheet showing row and column heading identifiers.

 

23. Produce a printout in landscape orientation of the spreadsheet showing all formulas with cell references and row and column heading identifiers. Widen the columns as appropriate to show entire formulas.

 

 

24. Produce a chart on a separate sheet to show the Ticket Price paid by each client. Information relating to chart setup is displayed in table below:

 

Chart Type	Clustered Column (2D)
Chart Title	June Sales (followed by your own initials)
Horizontal Axis Title	Passengers
Data labels	Show payment values outside end position
Chart location	Separate sheet named June Sales

 

 

25. Print the chart.

 

26. Save Flight 2.

 

27. Implement spreadsheet protection so that data can only be entered in cell range A6:E15 and G6:G15 and formulas are not displayed. DO NOT ENTER A PASSWORD.

 

28. Save as Flight 3.