Section outline

  • DIGITAL TOOLS FOR TEAMWORK

    Digital technology has allowed organisations to work across geographical borders. It allows members of an organisation to stay connected for a variety of purposes. The act of connecting and motivating staff remotely is a common practice in the workplace and is especially relevant today as digital technology continues to evolve and improve. This allows us to connect seamlessly - no matter where we are in the world.
    • Students must
      Mark as done
      TASK ONE

      Read through the scenario below, then work step-by-step through the tasks listed to discover the functions and features of Microsoft Excel.

      Scenario:
      Go! Travel has started selling a small range of travel products on their website. The accounting department has had some major IT issues in establishing the bookkeeping resources to manage and report these sales. Your manager has approached you to set up a simple sales tracker that can be used in the interim. He wants to record the number of items sold, profit per item, and compare the revenue of the different products.

      In Excel (or your chosen application), spend five minutes exploring the top ribbon, which includes different tabs that have their own unique panels/groups within. This will help establish an overview of the features and functions available to you. You must save your spreadsheet at the completion of every task. To do this, select the 'File' tab from the top ribbon, then click 'Save As' and save to an appropriate destination on your device.
      1

      Open a new/blank spreadsheet.
      2

      Make columns A-L wider.

      • Click and drag from the column A header to column L, to select all cells in these columns.
      • Right-click on any column header and select Column Width.
      • Enter 15 and click OK.
      3

      Make rows 3-11 taller.

      • Click and drag from the row 3 header to row 11, to select all cells in these rows.
      • Right-click on any row header and select Row Height.
      • Type 40 and click OK.
      4

      Insert the Go! Travel logo in cell I1.

      • Save the logo to your computer, then click on cell I1.
      • Navigate to Insert > Pictures > Place over Cells > This Device
      • Select the logo file.
      5

      Insert the title; "Online Sales Tracker".

      • Click in cell A1.
      • Type the title and press Enter.
      • Click in cell A1 and change the font size to 28.
      6

      Insert your name.

      • Click in cell A2.
      • Type "Prepared by: [your name]" and press Enter.
    • Students must
      Mark as done
      TASK TWO

      Your manager has given you the following sales information.
      Follow the instructions provided to set up a table, then enter the product cost and revenue figures provided.
      7

      Set up column headers for the data table.

      • From cells B4 to J4, enter these headings:
        Item, Cost per item, Percent markup, Total sold, Total revenue, Shipping charge/item, Profit/item, Returns, Total profit/product
      8

      Set up products.

      • From cells B5 to B9, enter these product names:
        T-shirt, Shorts, Hoodie, Passport Wallet, Lanyard
      9

      Format data cells correctly.

      • Click and drag to select cells C5 to C9.
      • Right-click in those cells and select Format Cells.
      • Choose Currency and click OK.
      • Repeat these steps for cells F5 to F9, G5 to G9, H5 to H9 and J5 to J9. To select these cells altogether, click on F5 and hold the left mouse button, drag the cursor down to F9, release the mouse button, hold Ctrl and click and drag the cursor from G5 to G9, H5 to H9 and J5 to J9.
      • Select cells D5 to D9 and format these as Percentage.
      10

      Enter cost, markup, sales, returns and shipping data.

      • From the data provided in the handwritten notes above, enter figures into the appropriate four columns.
      • Enter $10 as the shipping charge for each product.
      11

      Calculate total revenue.

      • Click in cell F5.
      • In the Formula Bar, enter "=C5*(1+D5)*E5" without the quotation marks, then press Enter.
      • Click in cell F5 again.
      • Click and hold on the small square in the bottom right of the cell outline (this is called the fill handle), and drag it down across all cells to F9, and release.
      12

      Calculate profit per item.

      • Click in cell H5.
      • In the Formula Bar, enter "=C5*(1+D5)-C5" without the quotation marks, then press Enter.
      • Click in cell H5 again.
      • Click and hold on the fill handle, and drag it down across all cells to H9, and release.
      13

      Calculate profit per product.

      • Click in cell J5.
      • In the Formula Bar, enter "=(E5-I5)*H5-(I5*G5)" without the quotation marks, then press Enter.
      • Click in cell J5 again.
      • Click and hold on the fill handle, and drag it down across all cells to J9, and release.
      14

      Calculate total profit.

      • Click in cell I10.
      • Type "Total profit".
      • Click in cell J10.
      • In the Formula Bar, enter "=SUM(J5:J9)" without the quotation marks, and press Enter.
    • Students must
      Mark as done
      TASK THREE

      Well done on your on your efforts so far. Your manager can now see all the data in one place, and this can be added to as sales increase, with the correlating data updating along with it. To better tell the story of the data for your manager, you decide to use some graphs. Graphs are a great way to illustrate data and show trends and proportionality in a visual manner.
      15

      Generate a graph to show product proportions of total profit.

      • Select cells B4 to B9 and J4 to J9 (remember to use Ctrl).
      • Navigate to Insert, and click on the pie chart icon in the Charts section of the ribbon.
      • Choose the basic 2-D Pie
      • Position the chart below the data table, on the left.
      16

      Generate a graph to show comparison of profits per product.

      • Select cells B4 to B9 and J4 to J9 (remember to use Ctrl).
      • Navigate to Insert, and click on the bar chart icon in the Charts section of the ribbon.
      • Choose the basic 2-D Column chart.
      • Position the chart below the data table, on the right.
    • Students must
      Mark as done
      TASK FOUR

      The spreadsheet is now looking good, but needs a little formatting. As this is a workplace document, it needs to follow the Go! Travel Style Guide for spreadsheets. Check out Section 9.5.10 of the Go! Travel Policies and Procedures Manual. Finally, you will export the sheet as a PDF to present to your manager for review.

      Important: For this Activity tab, you are not required to upload your completed presentation.
      Once this task is complete, click the 'Submit Assessment' button below.
      17

      Format the spreadsheet.

      • Action each item listed in the Style Guide, Section 9.5.10, that is relevant to this particular spreadsheet.
      • Add any other formatting you feel is appropriate/effective.
      18

      Export the sheet as a PDF.

      • Navigate to File > Print.
      • In the Printer section, choose Print to PDF.
      • In the last dropdown option, choose Fit Sheet on One Page.
      • Click Print and save in an appropriate location of your choice.