# Task SP2: FlyTPE

FlyTPE is a new, budget airline operating out of Taipei Taoyuan airport and flying to a number of destinations in Asia. They aim to offer a clear, easy-to-understand pricing structure for their tickets, which customers will buy through Android and iOS apps. As a first step in designing their apps, you have been asked to create a simple spreadsheet mockup of how the app should work.

The spreadsheet for this task (**tripplanner.xlsx**) contains a simple framework to build upon. Follow the instructions carefully, step-by-step.

Please note that during times of heavy use this website can respond slowly. All of the video tutorials for this activity are also **available on Youtube** in case this happens.

## Instructions

- Format all text in the spreadsheet as Myriad Pro, 12pt font. If this is not available, choose a
**sans serif**font.

##### Find out how

- Resize columns
**A:C**to fit their contents.

##### Find out how

3. Merge and center cells **B1:C3**. Make the contents **36pt** font.

##### Find out how

4. Insert the image **flytpe.png**. Resize it to fit vertically into rows 1-3 and place it in the centre of column A.

##### Find out how

5. Add **data validation** to cell B6, adding a drop-down menu that provides a list of airport codes to select from.

##### Find out how

6. Name this cell **DEST**.

##### Find out how

7. In cell C6, use a** VLOOKUP** function to display the full name of the airport based on the **DEST** cell. The table of all airport data is on the Destinations worksheet. Test that it works by choosing **SIN** as the destination in B6.

##### Find out how

8. In cells **B9:B10** and** B14:B16** use data validation to only allow whole numbers between **0** and **10**.

9. Test this validation rule using** normal**, **extreme** and **abnormal** test data.

##### Find out how

10. In cell C9, use a **lookup function** to find the ticket cost based on the destination, then multiply the result by the number of adults flying. 1 adult is flying on this trip.

##### Find out how

11. The cost of a child’s ticket is **three-quarters** the cost of an adult’s ticket. Type the formula to calculate this into cell **C10**. 1 child is flying on this trip.

##### Find out how

12. Add the values in** C9** and **C10** in cell **C11** using a formula, not a function.

##### Find out how

- Below are the costs for extras. In cells C14:C16 type formulas to calculate the total costs for each extra:

Extra legroom |
1000NTD |

Additional bag |
2500NTD |

Meal |
500NTD |

##### Find out how

14. In cell C17, calculate the total of all extras using a function, __not__ a formula. On this trip the customer does not want extra legroom but wants to book an additional bag and two meals.

##### Find out how

15. Tax is **15%** of the total combined cost of tickets and extras. In cell C19 type a formula to calculate this.

16. It is possible for the value in cell C19 to be a decimal, but we can’t charge somebody less than a whole NTD. Edit the formula to **round the result up** to a whole number.

##### Find out how

17. In **C20** calculate the total for the trip.

##### Find out how

18. Format cells **C9:C20** as **NT$** currency.

##### Find out how

19. Add **outside borders** to the ranges **A9:C10** and **A14:A16**.

##### Find out how

20. Make all text in row 20 **bold**.

##### Find out how

21. Test your spreadsheet using the following **test data**.

##### Find out how

## Extension: single or return?

One of the app development team noticed that with this simple model it is only possible to book a single flight. Most of FlyTPE’s bookings are for return flights, so we need to add this functionality.

22. **Insert two** rows on row 7. In **A7**, type **“Single/return”**. In B7, add **validation** to restrict the possible values in this cell to either “single” or “return”.

##### Find out how

23. In A8, type **“Multiplier”**. In **B8**, use an IF statement to apply a multiplier to the trip depending on whether or not it is single or return. Return flights are double the price of single flights.

**Q: Which is better?**

**=IF(B7=“Single”,1,2)** or **=IF(B7=“Return”,2,1)** ?

##### Find out how

24. Name cell B8 **multiplier** and apply the multiplier to cells C11, C12, C16, C17 and C18. Be careful to use brackets correctly.

##### Find out how

25. **Hide** row 8.

##### Find out how

26. Test your model with the following test data:

##### Find out how

27. Fill the entire spreadsheet in white to hide the gridlines (other than the borders you made).

##### Find out how

28. Put your name, candidate number and center number (TW008) in the header and make sure it prints to just one portrait A4 page.

##### Find out how