Task SP1: EarthDigital

In this first task you will learn some of the fundamental skills you need to get started with spreadsheets, including:

  • Opening a CSV file & saving as a spreadsheet
  • Resizing & formatting columns, rows & cells
  • Using formulas & functions to create models
  • Using relative and absolute cell references, including named ranges
  • Using complex functions like LOOKUPs to make decisions based on your data
  • Replicating formulae & functions across many cells
  • Preparing a spreadsheet to print
  • Adding data to the header & footer

Follow the instructions step-by-step, and expand the “Find out how” sections for more guidance if you need it.

EarthDigital is a Taiwanese technology company that sells laptops to schools in four countries; Taiwan, Malaysia, Hong Kong & Thailand. The spreadsheet for this task (earthdigital.csv) contains their sales figures for last year. Follow the instructions carefully, step-by-step.

Instructions

1. Open the file earthdigital.csv in an appropriate spreadsheet package. Make all columns wide enough to see the data inside them (1).

2. Merge & center cells A1:H1 (1) & format as serif, 26pt bold (1).

3. Select cells A3:H3 & format as bold, with an orange background (1).

4. Format cells A4:A46 as short date (1).

5. Use a function in cell C4 to look up the representative for the region based on the region in B4, from the range K4:L7 (2). Make this range absolute (1) & replicate down the column (1).

6. Select cells J12:K15. Name this range ‘laptops’ (1).

7. Use a function in cell F4 to look up the price of the laptop from cell D4 from the ‘laptops’ range you created in step 5 (2). Replicate this function down the column (1).

8. In cell G4 enter a formula to calculate the total cost in NTD. (1) Replicate this down the column. (1)

9. Select cells F4:G46 & format as accounting, currency NT$ Chinese (Traditional, Taiwan) (1).

10. In cell G47, use a function to calculate the total value of sales (1).

11. In cell F47, write ‘Total’ (1). Format both cells as bold (1).

12. In cell H4, use a function to look up the exchange rate from the table K4:N7 & multiply the total price in cell G4 by this value (3).

13. Select cells A3:H46 & format this as a table in any readable style (1)

14. Sort the table in ascending order of Region (1).

15. In the column Total (local currency) format each cell with the appropriate currency formats. You can find these under ‘Other accounting formats’. Use the following currencies (4):

Taiwan                 NT$ or NTD Chinese (Traditional, Taiwan)
Thailand              ฿ Thai or THB (Thailand)
Hong Kong          HK$ Chinese or HKD (Traditional, Hong Kong S.A.R.)
Malaysia              RM Malay or MYR (Malaysia)

16. Select cells A1:N47 & set print area (1). Make the spreadsheet fit on one page (1), formatted landscape (1).

17. Add your name to the header, left aligned (1), the centre number centre aligned (1) and your candidate number right aligned (1).

18. Print (1), ensuring all text is readable (1).

19. Show the formulas (1), making all columns only wide enough to fit their contents (1) and print cells A1:H47, ensuring all text is readable (1).

20. Save the file as earthdigital.xlsx (1)

Total marks: 40