Assignment Brief Unit 42 Spreadsheet Ass1 by 2M2E0P7


									Assignment brief
  Qualification                               BTEC National Diploma for IT Practitioners
  Unit number and title                       Unit 42: Spreadsheet Modelling
  Start date                                  w/c 28 March 2011
  Deadline                                    w/c 9 May 2011 – (late assignments capped at a pass)
  Assessor name                               Sheila Cassidy-Allan

  Assignment title                            Assignment 1 - Discuss and compile a complex spreadsheet model

  The purpose of this assignment is to:
  Discuss and develop a complex spreadsheet model to meet the particular needs of a specified user/organisation.

  Ridgeway is a large college offering a full range of academic courses, together with vocational courses such as
  plumbing, carpentry and so on. It also offers evening and weekend courses as part of its adult education programmes.
  The administration team at Ridgeway want to use spreadsheet software to keep track of student enrolments and
  course funding for the Year 2011.


  A Student worksheet that stores the following:

           Student Number
           Student First and Last Name
           Student Address
           Student Sex (M/F)
           Student Status (Enrolled / Not Enrolled) -This should be automatically set if the student exists within the
            enrolment sheet. It would also be useful if Enrolled is displayed in blue and not Enrolled displayed in red.
            (Conditional formatting)

  A filtered list on the student worksheet is needed to allow the user to select and display specific student records.

  A Course worksheet that stores the following details:

           Course Code
           Course Name
           Course Start Date
           Course End Date
           Course Funding Cost (the cost of the course per student, usually between £150 and £1,000 – use a mix
            of costs)

  An Enrolment worksheet that displays the following:

           Student Number
            This field should be a user input field to allow a user to enter the relevant Student ID
           Student Name
            This should be automatically displayed by using the Student Number to find and display the student’s name
            from the Student worksheet (VLOOKUP)
           Student Sex
            This should be automatically displayed by using the Student Number to find and display the student’s sex from
            the Student worksheet (VLOOKUP)
           Course Code
            This field should be a user input field to allow a user to enter the relevant Course Code
           Course Name
            This should be automatically displayed by using the Course Code to find and display the course name from
            the Course worksheet (VLOOKUP)

Sheila Cassidy-Allan – Spreadsheet Modelling - Assignment 1 – BTEC National ITP Level 3                                  1
  A Funding worksheet that displays the following:

           Course Code
            Directly referenced from the Course worksheet

           Course Name
            Directly referenced from the Course worksheet

           Course Cost
            Directly referenced from the Course worksheet

           Number of Attendees
            Automatically generated using the enrolment worksheet

           Basic Cost Funding
            Number of attendees on the course multiplied by the Course Funding to give the total basic funding available

           Funding Allowance – Single cell that holds the value of £1000 which will be used for an absolute cell

           Extra Funding
            Shows the value displayed in the Funding Allowance cell if the basic funding is less than £1500 and the
            attendees are more than 5. Only half the amount of the Funding Allowance will be allocated to courses that
            have exactly 5 attendees enrolled. Courses that do not meet these conditions will receive no extra funding.
            (IF, AND) (Nested IF)

           Total Funding – The total amount of funding available (Basic Cost Funding + Extra Funding)

  Each worksheet should make use of an input form to allow the user to enter data into each of the sheets; you should
  use a macro and assign it to a button to display each of the forms.

  All of the worksheets should be user-friendly, with the correct use of colours, formatting, consistency and validation.
  You have to create the data going into the worksheets, include at least 40 student records, 20 courses and ensure that
  30 students are involved within enrolments.

  Task 1

  The Principal at the Ridgeway wants to know how spreadsheets can be used to solve complex problems. He wants you
  to create a PowerPoint presentation which explains to him the benefit of Spreadsheets, include the following (P1):

          Typical users
          Manipulating complex data to support decisions
          Data mining.
          How they’re used to solve complex problems i.e. cash flow forecasting, budget control, ‘what if’ scenarios,
           sales forecasting etc.

 For (D1) research the above points in more depth and produce a report detailing how Ridgeway college can use
 interpretation methods to analyse data i.e. comparisons of totals, trend analysis.

  Task 2

  Having studied the college’s requirements create all the worksheets specified (P2), ensuring that it contains some of
  the following aspects:

          multiple worksheets
          cell and worksheet links
          complex formulae, for example at a least two-step process
          data entry forms (input form)
          nested IF functions
          cell protection

Sheila Cassidy-Allan – Spreadsheet Modelling - Assignment 1 – BTEC National ITP Level 3                                    2
  Use a selection of the following formulae and functions to meet the requirements of the enrolment process (P3):

          relative references
          absolute references
          logical functions, for example IF, AND, OR
          correct operators,
          named ranges/cells
          lookup functions

  Customise the spreadsheet in order to avoid data being entered incorrectly within the Course worksheet (P5):

          Ensure that the course start date is within the range of 01/01/11 and 01/01/12 and the end date is within the
           range of 01/01/11 and 01/06/12. (data validation)
          Show error message if this range is not met – show and explain all 3 potential messages available

  Formatting to include:

          Embolden, italic, borders, shading, evidence of appropriate column alignment and consistency (common look
           and feel throughout all worksheets) should be shown.

  (Evidence: Print screens of the spreadsheets showing them with and without the formulas. These must include
  explanations of the choice of formulas/functions/formatting used; detailing how they have met the requirements).

  Task 3

  The Principle at Ridgeway college is worried that the spreadsheet will not cover all their needs and that the staff may
  accidently delete parts of the formulae and functions by mistake. To ensure your spreadsheet is ‘fit for purpose’
  demonstrate your spreadsheets functions and formulae, including cell/worksheet protection to the Principle (your

          Use their feedback to ‘refine’ your spreadsheet model to improve its efficiency

  (Evidence: Print screens with changes and explanations and submit with a copy of your Demo sheet)

  Task 4
  In order to assist the Management Team at the college to present, interpret and analyse their data complete the

          Create and print an excel report to enable the number of students attending each course to be displayed.
           Included in the report add a function to allow the data to be filtered (your choice of data). (M2)

  (Evidence: Print report (with and without filtering option) and explain how the report was used to interpret the
  spreadsheet data and what you learnt from the findings.)

          From the data generated in the report, create a bar chart (in the Enrolment worksheet) to graphically display
           the information with appropriate titles, axis scales and colours. (P4)

   (Evidence: Print screen of the graph and appropriate titles with explanations, including how the chart/graph met the
  user need)


          All relevant evidence stated within the assignment is structured in an appropriate way with a front sheet, a table
           of contents and reference page. Use coloured printouts and ensure explanations are clear and relating to the
           screen prints.

Sheila Cassidy-Allan – Spreadsheet Modelling - Assignment 1 – BTEC National ITP Level 3                                      3
  Sources of information

  Information Technology Practitioners – Book 2
  BTEC Wiki
  Information and Communication Technology – R P Richards and P M Heathcote

  This brief has been verified as being fit for purpose



Sheila Cassidy-Allan – Spreadsheet Modelling - Assignment 1 – BTEC National ITP Level 3   4

To top