Call/WhatsApp: +1 332 209 4094

Creating spread sheets for cover books

Creating spread sheets for cover books.

The assessment task is due on the date specified by your assessor. Any variations to this arrangement must be approved in writing by your assessor.

Submit this document with any required evidence attached. See specifications below for details.

Performance objective

The candidate must demonstrate the knowledge, skills and abilities required to prepare, develop and use a spreadsheet that includes automated and standardised spreadsheet options. They will also need to represent numerical data in graphical form using the spreadsheet data.

Assessment description

You are required to use Microsoft Excel to create spreadsheets for Covers Books that will manage the store’s investments.

Covers Books

Covers Books is a small bookstore located in a local shopping precinct owned by DJ Mathis. Operating for just over two years, Covers Books specialises in fiction books and has built up a steady business with many repeat customers from the local area. DJ (the owner) has set up a small investment portfolio in the name of the store to better utilise excess cash.

Procedure

  1. Import the text file Covers Investments provided to you by your assessor into Excel and make the following changes:
    1. format the information into a table of figures
    2. create a chart titled ‘Investment Prices June 2009 – June 2010’ showing the price of each share over the 12 months covered by the data
    3. name the worksheet ‘Investment Prices’.
  2. In a new worksheet titled ‘Investment details’ create a table that calculates the value of each share holding at the end of each month and then totals the share values for each corresponding period. You will need to include and refer to the additional information in Appendix 1 for this calculation
  3. Create a chart titled ‘Total Investment Values’ showing the value of the total investment over 12 months.
  4. From the figures in the ‘Investment Details’ table, use formulas to determine the average value of each investment over the 12 months.
  5. Create macros as follows:
    1. to print the ’Investment Prices’ chart
    2. to print the ‘Total Investment Value’ chart
    3. create buttons for each of the macros, ensuring you link the buttons to the macros.
  6. Save the file using the following format: [Your initials] Covers Investments with Macros ddmmyy.
    For example: DP Covers Investment with Macros 221210.
  7. Export the table of each share for 12 months and the related chart to a document:
    1. Format the document by adding the logo (copy from the Covers Fortnight payroll file provided for Assessment Task 2)
    2. Add an appropriate title
    3. Ensure that the graph and table will print to a single A4 page
    4. Save the report using the following format: [Your initials] Covers Investments Report ddmmyy.
      For example: DP Covers Investments Report 221210
    5. Print a copy of the report file.

Specifications

You must submit:

  • a soft copy of the following files:
    • the investment file named [Your initials] Covers Investments with Macros ddmmyy
    • the investments report file named [Your initials] Covers Investments Report ddmmyy.
  • a print out of the investments report.

Your assessor will be looking for whether you have:

  • analysed the task and determined specifications for spreadsheets
  • utilised spreadsheet design software functions and formulae to meet identified requirements
  • linked spreadsheets in accordance with software procedures
  • formatted cells and used data attributes assigned with relative and/or absolute cell references, in accordance with the task specifications
  • tested formulae to confirm output meets task requirements
  • evaluated tasks to identify those where automation would increase efficiency
  • created, used and edited macros to fulfil the requirements of the task and automate spreadsheet operation
  • entered, checked and amended data in accordance with organisational and task requirements
  • imported and exported data between compatible spreadsheets and adjusting host documents, in accordance with software and system procedures
  • used manuals, user documentation and online help to overcome problems with spreadsheet design and production
  • previewed, adjusted and printed spreadsheet in accordance with organisational and task requirements
  • named and stored spreadsheet in accordance with organisational requirements and exited the application without data loss or damage
  • determined style of graph to meet specified requirements and manipulated spreadsheet data if necessary to suit graph requirements
  • creating graphs with labels and titles from numerical data contained in a spreadsheet file
  • saved, viewed and printed graph within designated timelines
  • developed complex spreadsheets
  • developed graphical representations of data contained in spreadsheets.

Your assessor will also be looking for:

  • literacy skills to interpret and evaluate the purposes and uses of various features of spreadsheets and to use a variety of strategies for planning and reviewing own work
  • proofreading and editing skills to check for accuracy and consistency of information by consulting additional resources
  • numeracy skills to collate and present data, graphs and related references
  • knowledge of the advanced functions of spreadsheet software applications
  • knowledge of the impact of formatting and design on the presentation and readability of data
  • knowledge of organisational policies and procedures.

Adjustment for distance-based learners:

  • Requirements are as per the above.
  • The answers may be electronically transmitted to the assessor, for example via email.
  • Your assessor will provide you with additional information on how you can meet these requirements.

Appendix 1 – Additional investment information

Shares Date of Purchase No of Shares
TeleCom (TC) 01/07/2009 1000
State Bank (SB) 29/08/2009 1200
United Retail (UR) 01/07/2009 1500