Fill This Form To Receive Instant Help

Help in Homework
trustpilot ratings
google ratings


Homework answers / question archive / Brief Guidelines: Download the Special Assignment from Moodle

Brief Guidelines: Download the Special Assignment from Moodle

MS Excel

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

 

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

 

  1. Insert your own name as a footer.

 

  1. 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.

 

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

 

 

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

 

 

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

 

 

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

 

 

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

 

 

  1. 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

 

 

  1. 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

 

 

  1. 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.

 

  1. 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.

 

  1. 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.

 

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

 

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

 

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

 

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

 

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

 

  1. 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

 

 

 

 

 

  1. Save the spreadsheet as Flight 2.

 

 

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

 

  1. 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.

 

 

  1. 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

 

 

  1. Print the chart.

 

  1. Save Flight 2.

 

  1. 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.

 

  1. Save as Flight 3.

Option 1

Low Cost Option
Download this past answer in few clicks

16.99 USD

PURCHASE SOLUTION

Already member?


Option 2

Custom new solution created by our subject matter experts

GET A QUOTE