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

  1. Format all text in the spreadsheet as Myriad Pro, 12pt font. If this is not available, choose a sans serif font.
  1. Resize columns A:C to fit their contents.

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

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

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

6. Name this cell DEST.

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.

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.

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.

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.

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

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

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.

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.

17. In C20 calculate the total for the trip.

Find out how

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

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

20. Make all text in row 20 bold.

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

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) ?

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

25. Hide row 8.

26. Test your model with the following test data:

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

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