Why Choose Us?
0% AI Guarantee
Human-written only.
24/7 Support
Anytime, anywhere.
Plagiarism Free
100% Original.
Expert Tutors
Masters & PhDs.
100% Confidential
Your privacy matters.
On-Time Delivery
Never miss a deadline.
Brief Guidelines: Download the Special Assignment from Moodle
|
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.
- 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
- Insert today’s date from the computer clock (using a formula) into the cell B21 to the right of the heading Date:
- Insert your own name as a footer.
- 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.
- Use the SUM function to calculate total income for all ticket sales and display in cell C18 to the right of the heading Total.
- Use the AVERAGE function to calculate the average ticket cost and display in cell C19 to the right of the heading Average.
- Rename Sheet1 as Customer Records. Save the spreadsheet as Flight 1.
- Produce the following printouts:
- The whole spreadsheet excluding the main heading.
- Spreadsheet showing all formulas and row/column heading identifiers.
- Delete the record for passenger Keane R from the spreadsheet.
- 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
- 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 |
- 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.
- 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.
- 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.
- Use a formula to calculate the Ticket Price as Price plus Excess Baggage Charge plus Taxes & Charges minus Discount.
- Use the SUM function to calculate the total for the Ticket Price.
- Use conditional formatting to emphasise total ticket prices greater than €100 in Green Fill with Dark Green Text.
- Use the AVERAGE function to calculate the average Ticket Price in the appropriate cell.
- Sort the spreadsheet by Depart Date from oldest to newest and then by Name from A to Z.
- 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 |
- Save the spreadsheet as Flight 2.
- Produce a printout in landscape orientation of the spreadsheet showing row and column heading identifiers.
- 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.
- 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 |
- Print the chart.
- Save Flight 2.
- 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.
- Save as Flight 3.
Expert Solution
Buy This Solution
For ready-to-submit work, please order a fresh solution below.
