candidate style answer - iis windows serverweb1.crypt.gloucs.sch.uk/ict/resources/year...

Candidate Style Answer OCR GCSE ICT J461 / J061

Unit B062 Practical Applications in ICT: Controlled Assessment Task

High Level Candidate Response

This support material booklet is designed to accompany the OCR GCSE ICT specifications for teaching from September 2010.

OCR GCSE ICT J461 / J061 Unit B062 – High Candidate

Candidate Style Answers 2010 1

Introduction

OCR has produced these candidate style answers to support teachers in interpreting the assessment criteria for the new GCSE specifications and to bridge the gap between new specification release and availability of exemplar candidate work.

This content has been produced by subject experts, with the input of Chairs of Examiners, to illustrate how the sample assessment questions might be answered and provide some commentary on what factors contribute to an overall grading. The candidate style answers are not written in a way that is intended to replicate student work but to demonstrate what a “good” or “excellent” response might include, supported by examiner commentary and conclusions.

As these responses have not been through full moderation and do not replicate student work, they have not been graded and are instead, banded “middle” or “high” to give an indication of the level of each response.

Please note that this resource is provided for advice and guidance only and does not in any way constitute an indication of grade boundaries or endorsed answers.



Task

Litchfield Promotions works with over 40 bands and artists to promote their music and put on performances in the UK. The number of bands they have on their books is gradually expanding.

Litchfield Promotions needs to be sure that each performance will make enough money to cover all the staffing costs and overheads as well as make a profit. Many people need to be paid: the bands; sound engineers; and, lighting technicians. There is also the cost of hiring the venue.

Ben is the office administrator and currently uses a computer to type letters and other documents. He keeps records of the customers and artists in paper format in a filing cabinet. Ben uses a calculator to keep track of income and expenditure and types these values into a document he stores on his computer.

Litchfield Promotions needs to create an ICT solution to ensure that they have all necessary information and that it is kept up to date. Ben has been asked to produce this solution. The solution will show income, outgoings and profit. Ben will be responsible for the day to day management of this new system and it will be kept up to date by an administrative assistant.



INVESTIGATING A NEED

Research the requirements and context for a solution documenting sources of information.

(Work with others to plan and carry out research to investigate how similar companies have produced a solution.)

A group of us did some research into how existing businesses record their income and expenditure and how they calculate their profit. We also tried to find out how they kept their records of customers and how they produced invoices to send to their customers.

Their were 3 of us in our group and we decided to split the research, so that one of us used the internet to see if we could find templates for business plans; another of us used the system in a local shop where they have a Saturday job (with the permission of the owner) and I talked to my Dad about how he keeps his business records (he is a self-employed private chiropodist, with only himself working in the business and visiting care homes and individual patients in their own home).

The first source of information came from searching on the internet for “income + expenditure system”. This didn’t bring up any helpful websites so the search was changed to “income + expenditure spreadsheet”. This gave a link to the “SpreadsheetZone.com” website where there were business templates to download. A second search, for “business spreadsheets” led to another website where templates could be downloaded: “Jaxworks.com”.

The second source of information was the local outdoor shop called “The Outdoor Shop”, where one of our group has a Saturday job. This is a small independent business with just one shop, which is owned and run by a married couple who are keen walkers and campers themselves; one of them used to be a teacher who led expeditions both in this country and overseas with pupils. They have a good knowledge of customer requirements and their business has resulted from their own interest in the outdoors. They gave us a blank copy of a simple spreadsheet that was set up for them by a friend when they first started up the business several years ago.

The third source of information is my Dad’s business solution for his Chiropody business. His business is very small and he is the only employee but he has a lot of customers, either care homes or individuals. He has to keep records of all his patients’ names and addresses for when he visits them and also to send invoices to if they don’t pay him when he actually visits them, which most individual patients do. He also has to keep a record of all his expenditures including overheads, and how much income he receives each month. My Dad is not very confident with a computer but has set himself up a simple system to help with his business. He has 2 separate systems – a simple database where he keeps records of patients contact details and a simple spreadsheet where he records expenditure and income and works out profit. He then copies the data from the database and spreadsheet into a word processed document to create invoices.

Record of existing solutions identified and information requirements analysed

(Clearly record and display your findings)

On the following pages are the results of our findings. First are screenshots of two business templates found on the internet; second is screenshots of the system used by The Outdoor Shop and lastly is the systems my dad uses for his business.



EXAMPLE OF SYSTEM USED TO RECORD CUSTOMERS & ORDERS AND FOR

CALCULATING INCOME, OUTGOINGS & PROFIT (Source: SpreadsheetZone.com)



This system uses 6 spreadsheets in one workbook. The main details are entered in the Control worksheet and formulas using absolute references are used to transfer this information into other worksheets, such as the Invoice worksheet.

The Report worksheet records and calculates the number of products sold, the shipping costs of each product and the total revenue for each product.


There are 3 worksheets, torecord the customer details,product details and the orderdetails.

SECOND EXAMPLE OF BUSINESS RECORD OF INCOME & EXPENDITURE (Source: Jaxworks.com)

The second example of a business system is a simpler spreadsheet than the first. It records the income and expenditure details in one worksheet. It uses an IF function and a SUM function to work out the calculations.


THIRD EXAMPLE OF BUSINESS RECORD OF INCOME & EXPENDITURE: (Source: The Outdoor Shop)



The system used by The Outdoor Shop is also a simple spreadsheet, a bit like the second example found from the internet. It records the numbers of each item bought and how much they were bought for, the number of each product sold and the selling price. The total spent and total income are calculated using a simple SUM function. Another worksheet records the purchases and the information from this is mail merged into a word processed invoice which is given to the customer if they purchase in the shop or it is sent to the customer with the goods if they order over the phone. There is a third worksheet which acts as a calculator to work out the cost of an order; a COUNTIF function is used to calculate the total number of items a customer has bought and ‘*’ is used to multiply the number of items by the price to get the total cost of each product bought; SUM function totals the overall price of the order. The data from the second spreadsheet is mail merged into an invoice document which is sent with an order if the items are posted to the customer, or given to the customer if they buy in the shop.

FOURTH EXAMPLE OF BUSINESS RECORD OF INCOME & EXPENDITURE: (Source: Independent Chiropody Business)



The spreadsheet used to record expenditure and income for the chiropody business is a simple system where my dad records what he has bought and the cost of it and then a simple SUM function adds this up for each month. There is another section where he records each visit per month (the patient name and cost of the visit) and a SUM function totals up the income per month. He has a simple database where he stores the patients surname, first name, address and telephone number(s). The information about the patients from both systems is copied and pasted into a word processed document template to produce an invoice. No mail merge is used to transfer the information.

ANALYSIS OF RESEARCH

Having looked at the different systems of existing businesses, it seems that spreadsheet software is commonly used to record income, outgoings and profit. I thought this would be the case as we have previously done work on spreadsheets in school, which has involved tasks such as budgets. We have learned about different formulae using the mathematical operands (+, -, * & /) and functions such as SUM, INDEX, COUNTIF, IF, MAX and VLOOKUP. If I designed a spreadsheet to solve this problem, I know that I could do simple calculations such as adding or multiplying a column of numbers but I could also do logical comparisons using, e.g., an IF function.

Other features of spreadsheet software I have learned about include cell validation and conditional formatting which might come in useful. I have also learned about form controls, such as buttons and combo boxes and also how to record simple macros and then link these to a button to perform common tasks. A macro could be used to go to another worksheet in a spreadsheet, to print or to perform a more complex task such as producing a graph or mail merge document automatically. I also know how to format a spreadsheet, such as removing gridlines, merging cells, inserting pictures and changing font.

In our research we found that one company uses a database to store records of customers. This could be very useful as in a database you could search easily for specific data, such as customer surname, using a query and then put the results into a report. However, it is not as easy to do calculations in a database as in a spreadsheet, as more knowledge of database features is required.



Our research also showed that word processed documents can be used to produce invoices and that these can use mail merge to transfer information from a spreadsheet to the document. I have learned about mail merge before and know that it is also often used to transfer information from a database to a word processed document – we did a task in Year 9 where we had to set up a mail merge document linked to a database about a leisure centre.

Comment: The requirements of the solution have been researched and all sources documented (although there is no supporting evidence of interviews taking place, other than a log in the diary later in the project). There is evidence the candidate has worked effectively with others to gain and share knowledge from different sources. Existing solutions have been thoroughly analysed. There is some evidence of selection of appropriate data and information.

Design specification including user requirements

(Recommend a solution that will address the requirements of the task)

The system will need to:

1. store text information, e.g. bands and customers 2. store numerical information, e.g. staffing costs

3. do calculations, e.g. using +, -, * and / to work out income, outgoings and profit

4. use functions to do logical comparisons such as LOOKUP to obtain values easily from

another part of the system, or IF, e.g. to compare one month’s profit with another’s

5. have automatic re-calculation when variables, e.g. staffing costs, are changed

6. model a situation using ‘What if ..?’ scenarios, such as making sure each performance will cover the costs of staffing and overheads as well as making a profit

7. produce graphs of data for easy visual comparison

8. use validation rules to try and avoid errors when entering data

9. transfer information to a word processed document using mail merge

10. make use of macros for common tasks

11. use templates and a house style to ensure consistency across all the documents

12. have a user-friendly interface to make it easy to use for novice users

13. have some areas protected so that formulae and functions can’t be deleted or changed

unintentionally by the user

14. have a password to enter the system so that only the authorised users can access data about the customers, so that data is kept secure in line with the Data Protection Act.

Comment: A design specification had been produced which has measurable success criteria stated in a brief test plan on the following page. NB there is, however, no mention of hardware requirements.



Test Plan to test my system against the design specification

Test number Test 1 Enter text into my system 2 Enter numbers into my system 3 Test all formulas using a mathematical operand 4 Test all functions 5 Check that if variables are changed the formulae automatically recalculates

the answer correctly 6 Test that a user could predict profit made for a performance 7 Produce a graph from numerical data stored in my system 8 Check validation rules do not allow invalid data to be entered 9 Test that the correct data is transferred from my system to a letter using mail

merge 10 Test any macros perform the correct function 11 Check that all parts of the system have the same house style 12 The system should be easy to use, e.g. using buttons Plan of work to carry out: Week Plan of work to be done 1 Design my system 2 Refine my design and check formulas, etc should work 3 Start setting up my system 4 Continue setting up the system and add protection to it 5 Set up validation rules, etc 6 Enter data into the different sections 7 Test my formulae & functions work 8 Make any necessary amendments to my system 9 Test the whole system 10 Complete testing of the system 11 Evaluate my system 12 Complete the evaluation and complete the write up of the report of what I

have done Design brief

(Produce a design brief, incorporating timescales, purpose and target audience)

I am going to design and produce a system to enable Ben and an administrative assistant to record and calculate the income, outgoings and profit of Lichfield Promotions. The system must allow the users to predict how much money each event will cost and how much profit will be made. The system will also keep up-to-date records of the company’s customers and artists. The system must be easy to use and have a user-friendly interface, as the users may be novices. It will include validation rules and macros to ensure the system is user-friendly and to minimise errors in data entry. There will be security set on the system to prevent unauthorised access to confidential data. The system must also incorporate the facility to produce letters and other word processed documents. Templates and house-style will be used to ensure consistency in a user-friendly way. The time-scale for me to produce the system is 12 hours; I have a 1 hour lesson each week in which to produce this work. I will keep a log of the work I do and note any errors and amendments I make to my system. My design for the system can be found on P. 104 – 109 at the end of this project. I have chosen this design as it includes formulae which will automatically update when I change variable data, meaning the solution can be used to model the income, expenditure and profit. It has some complex functions such as VLOOKUP which will allow data from one part of the system to be automatically transferred to another part of the system, which should lead to less errors when entering data and it will also make it less time consuming to enter data. The functions and formulae I have used in my



design will calculate the ticket income, the total expenditure and the profit, which the user requires. My system also includes a main menu with macros and buttons, which will give a user-friendly interface, which is also required by the user. There are also drop-down boxes to make entering data easier for the user. I intend to produce 2 mail merge letters, incorporating data from each of the database worksheets (Customer and Band) so that many letters can be produced by the user which only require writing and proof-reading once.

Comment: The design brief shows timescale, purpose and awareness of the audience. There is a plan of action for work to be carried out. The candidate has done some design work including data structure (see end of project) although there are not designs of all parts of the solution, such as outputs. There is justification for the proposed solution.

Practical use of software tools to produce a working solution

(Produce a solution, ensuring: it can be modified to be used in a variety of situations; it has a friendly user interface; it is suitable for the target audience; it has been fully tested.)

SETTING UP MY SYSTEM OPENING THE SOFTWARE

I opened Micorsoft Excel to create my system



RENAMING THE WORKSHEETS I renamed each worksheet to match my design idea; I did this by right-clicking on the sheet name and selecting Rename:

When I needed to add another sheet to my system, I clicked on this tab

These are all of my sheets named, ready for me to enter the data:

ENTERING DATA INTO DATABASE OF BANDS SHEET I entered data for 5 bands into the Database of Bands but didn’t enter any address details.

ENTERING DATA INTO DATABASE OF CUSTOMERS SHEET I entered details of 5 venues into the Database of Customers, but again didn’t enter address details.

ENTERING DATA INTO EXPENDITURE SHEET



I entered the data into the Expenditure sheet first, typing in formulae and functions as planned

NAMING CELL RANGES I needed to name some cell ranges so that I could use them in VLOOKUP functions – this is easier than using absolute cell references and I can use a named range more than once. I named the cells in the Database of Customers as VENUE_DETAILS.

I named the cells in the Expenditure sheet EXPENDITURE.



I named the cells in the Database of Bands sheet as BAND_FEES

For each range of cells, the software automatically knows how many columns of data there are and will number them from 1 onwards. So if I need a value in column 10 of a named range, then I use this in my VLOOKUP function.

Comment: The solution shows understanding and appropriate use of advanced software features.



The screenshot below shows the VLOOKUP functions I entered into the Expenditure sheet, to look up data from cell ranges I had previously named or from absolute cell references in the same worksheet.


Comment: The solution shows understanding and appropriate use of advanced software features.

I entered the functions using the Function button at the side of the formula bar

I was then able to enter the different parts of my function and the software automatically put in the brackets, etc in the right place, so I didn’t need to remember the exact structure of the function to type it in.


ENTERING DATA INTO PROFIT CALCULATOR SHEET I entered formulae into the Profit calculator sheet as below. My original design had the Band and Venue data as drop-down menus, made by validating the cells, but I changed these to VLOOKUP functions to make sure the data matched the Performance Code (see section on amendments to my system P. 48). The Band name is entered by looking up the Performance Code (cell C1) in the cells I named Expenditure in the Expenditure sheet and returning the value found in column 4 of that table. The Venue name is entered by looking up the Performance Code (cell C1) in the cells I named Expenditure in the Expenditure sheet and returning the value found in column 3 of that table. The Number of Available Tickets is entered by looking up the value in C5 (the Venue name) in the cells I named Venue Details in the Database of Customers sheet and returning the value.

Comment: The candidate evaluates at each step, making amendments to their work.

The Ticket Income is found by multiplying the price of a ticket (C11) by the number of tickets sold (C13).

Expenditure looks up the value in C1 (Performance Code) in the named cells EXPENDITURE and returns the value in column 15 of those named cells.

Profit is found by subtracting the value in G15 (Total Expenditure) from the value in G13 (Ticket Income).



Comment: The solution shows understanding and appropriate use of advanced software features, including VLOOKUP function, named cell ranges and absolute cell references.

I then added an IF function to the Profit calculator as a quick visual test of how good the profit was for a performance. The logical test was if the value in G19 (Profit) is less than half of the total income then the true statement would be returned which is ‘PROFIT IS LESS THAN 50%’ but if the logical test was not true then the false statement would be returned which is ‘PROFIT IS OVER 50%’.

I then added conditional formatting to the Profit cell so that if the Profit value is greater than 0 the cell is green and if it is less than 0 the cell is red.



SETTING UP THE MAIN MENU SCREEN To set up the Main Menu I used the Form Controls on the Developer tab to insert buttons

I recorded macros which linked to these buttons; each button automatically takes the user to another sheet in the system.



The screenshot below shows the finished buttons. After this I inserted clipart suitable to the company.

Comment: The solution shows understanding and use of advanced software features, including macros.

Then I changed the colours of the cells and the text and I removed the gridlines to give the menu a better appearance.



Comment: The candidate demonstrates a good understanding of the purpose and needs of the user for the task.

The screenshot below show the final system after I had added more sheets in and also added hyperlinks to the letters (see Amendments for reasons):



TESTING MY FINISHED SYSTEM With my specification I drew up a test plan which I am now going to use to make sure that my system meets the design specification. The evidence of these tests is shown after the test plan. Test number

Test Expected result Actual result

1 Enter text into my system When I type text into my system it should be stored

2 Enter numbers into my system When I type numbers into my system they should be stored

I did not do a specific test for these but the production and use of my system shows that text and numbers are stored in all the worksheets in my system.

3 Test all formulas using a mathematical operands: Ticket Income formula, & Profit formula in Profit record sheet

The values in F2 & G2 when multiplied together (25000 x 725) should give a result in H2 of 18,125. The value in H2 – the value in I2 (18,125 – 11,000) should give a result of 7125 in J2.

The values in F2 & G2 when multiplied together (25000 x 725) did give a result in H2 of 18,125 The value in H2 – the value in I2 (18,125 – 11,000) did give a result of 7125 in J2.

4 Test all functions: SUM & VLOOKUP function in Expenditure sheet LOOKUP & VLOOKUP in Profit record sheet

The SUM function should correctly add up the values in G4, J4, K4, L4 & N4 (1200, 500, 10000, 1000 & 0) and give 12,700 The Vlookup should look up ‘Rockers’ from D4 and return the value 10,000 from column 8 of the Band Fees named range of cells. The Lookup should look up ‘Rock City’ from cell C2 & return the value 750 from Venue Detail named cells. The VLookup should lookup the value in A2 which is Performance code 1 & return the value in column 4 of the Expenditure named cell range which is ‘Rockers’

The SUM function did correctly add up the values in G4, J4, K4, L4 & N4 (1200, 500, 10000, 1000 & 0) and give 12,700. The Vlookup should look up did return the value 10,000 from column 8 of the Band Fees cells. The Lookup did return the value 750 from Venue Detail named cells. The VLookup did return the value of ‘Rockers’

5 & 6 Check that if variables are changed the formulae automatically recalculates the answer correctly Test that a user could predict profit made for a performance

When I change the Performance code in the Profit calculator from 1 to 2 the data should change from the Rockers at Rock City with 750 available tickets to Electric Classics at the Assembly Rooms with 500 tickets When the data is automatically entered as above, the profit in G19 should be

The data did change automatically from the Rockers at Rock City with 750 available tickets to Electric Classics at the Assembly Rooms with 500 tickets The profit changed in cell G19 from £7125 to £14625



automatically calculated from the ticket income and expenditure in cells G13 & G15

7 Produce a graph from numerical data stored in my system

When I click on each of the graph buttons on the main Menu screen, the system should take me to graphs already produced, which compare total expenditure of each performance and the ticket income with expenditure for each performance

When I clicked on each of the graph buttons on the main Menu screen, the system did take me to graphs already produced, comparing total expenditure of each performance and the ticket income with expenditure for each performance

8 Check validation rules do not allow invalid data to be entered

When there are 500 tickets available in the Profit record sheet I should not be able to enter 501 in cell C13 for the number of tickets sold – I should get an error message on my screen saying ‘Tickets sold cannot exceed the number of tickets available’. When I enter 499 in C13 the data should be accepted & no error message will come up.

When I entered 501 in cell C13 the error message came up saying ‘Tickets sold cannot exceed the number of tickets available’. When I entered 499 in C13 the value was accepted. However, when I changed the data by just changing the Performance code, the validation rule did not work.

9 Test that the correct data is transferred from my system to a letter using mail merge

When the letter to Bands is opened, there should be 5 letters, the first being to Boy Band and the last to Rockers

The results of this can be seen on P.74-75. There were 5 letters produced, the first to Boy Band & the last to Rockers.

10 & 12 Test any macros perform the correct function The system should be easy to use, e.g. using buttons

The buttons on the main menu screen should take the user to the correct part of the system, according to the label on the button, e.g. the ‘Customer Database button should take the user to the Database of customers (venues) worksheet

A warning message about security came up on the screen. After I had enabled macros in my system the buttons all worked and the Customer database button did take me to the Database of customers worksheet

11 Check that all parts of the system have the same house style

All parts of the system should have purple font, purple shading, the Litchfield Promotions title and the same logo

The letter and main Menu screen have the same use of colours and the same logo; the other sheets have the same font colour but do not really conform to the house style, as seen on P.87 onwards.



TESTING THE BUTTONS ON THE MENU SCREEN (Test 12) I tested that each of my buttons would take me to the expected sheet. When I tested each button I got the error message below.

I realised this was because when I reopened my system the security had automatically blocked macros, so I needed to enable the content by clicking on the ‘Options’ button.

Each of the buttons now worked correctly and took me to the correct sheet:

(Test 7)



(Test 7)

Comment: The candidate evaluates at each step, making amendments to their work.

Comment: The candidate tests the final solution.



TESTING THE HYPERLINKS TO THE MAILMERGE DOCUMENTS I then tested the hyperlinks on the Menu screen to check that my mail merge letters opened correctly. When I clicked on each hyperlink I got a message asking if I wanted to place data from the correct database into the document. When I selected ‘Yes’, the correct letter opened. The message for the Database of Bands is shown below and the letter that then opened to the Bands.




TESTING THE PROFIT CALCULATOR (Tests 5 & 6)


The screenshots above and below show that when I changed the Performance code from 1 to 2, the correct data was entered automatically and the Profit and Expenditure values recalculated.

The screenshots show that when I changed the Performance Code again, to 5, the data automatically recalculated giving the correct Band, Venue and number of available tickets.

Comment: The candidate tests the final solution and demonstrates knowledge of how changes in the data can affect the final solution. The software is used to model ‘what if’ situations.


I needed to change the price of the tickets manually as I didn’t have a function to look this up. TESTING THE EXPENDITURE SHEET (Test 4)

THE SUM FUNCTION: The SUM function adds up the values in columns G, J, K, L and N



THE VLOOKUP FUNCTIONS: This looks up the value in D4 (Band name Pop Soloist) from the cells named BAND_FEES and returns the value in column 8 of the named range which is £5,000.00. This function has worked.


TESTING THE PROFIT RECORD SHEET (Test 4) TESTING THE LOOKUP FUNCTIONS AUTOMATICALLY ADD THE CORRECT DATA TO THE SHEET

This function should look up the value in C2 (Venue Name – Rock City) and return the correct number of available tickets, i.e 750. The function works correctly.



This VLOOKUP function should look up the value in A2 (Performance Code 1) and return the value from the named cell range EXPENDITURE in column 4

The screenshots show this function works correctly.



This VLOOKUP function should look up the value in A2 (Performance Code 1) and return the value from the named cell range EXPENDITURE in column 3 this time

The screenshots show this function also works correctly




TESTING THE TICKET INCOME FORMULA USING ‘*’ (Test 3) This formula multiplies the value in F2 by the value in G2, i.e. 25,000 x 725. I used the calculator to check the correct answer:


The formula was correct.

I checked that when I changed values the formula would automatically update, which it did. I changed the value in G2 from 725 to 750 and the Ticket Income in cell H2 changed to £18,750.00:



TESTING THE EXPENDITURE FORMULA The formula in cell I2 of the Profit Record sheet is a simple formula which just fills in the value from a cell in the Expenditure sheet. The correct value was inserted as shown below:




TESTING THE PROFIT FORMULA IN THE PROFIT RECORD SHEET, USING ‘-’ (Test 3) This formula subtracts the value in I2 (11,000) from the value in H2 (18,125). I tested this on the calculator and it gave the same answer as my system, so this formula works.


When I changed the value in cell H2 to 17,500, the formula automatically recalculated to give the correct answer of 6,500:


TESTING CONDITIONAL FORMATTING IN THE PROFIT CALCULATOR SCREEN I tested that my conditional formatting worked correctly in my Profit calculator sheet. If the Profit is less than 0 the cell should be red, which it was when I reduced the number of tickets sold to 100 as shown below:


When I changed the number of tickets to 500, the maximum available for that venue, the Profit became a value above 0 and the cell turned green. This shows that the conditional formatting works.


Testing validation rules in my system (Test 8) I set a validation rule on the ‘Tickets Sold’ cell so that the number of tickets sold cannot be more than the number of tickets available in cell C9.



When I tried to enter invalid data (501) the error message I had set came up so my validation rule worked.


I then checked the rule with extreme valid data (499) and the rule did allow this to be entered. So my validation rule was working correctly.

However, I noticed that the rule doesn’t prevent errors when data is automatically entered in the Profit calculator – when I changed the performance code from 1 to 5, the number of tickets available was automatically and correctly filled in as 500 but the value of 725 remained in the Tickets Sold cell, as can be seen in the 2 screenshots below. OCR GCSE ICT J461 / J061 Unit B062 – High Candidate

I had originally had a validation on the Number of Available Tickets cell, but I had removed this when

MENDMENTS TO SYSTEM DESIGN

1. Changing drop down boxes to VLOOKUP functions in Profit calculator

I changed it to a VLOOKUP function.


Comment: The candidate tests the final solution and evaluates at each step, making amendments to their work.

A


I initially set up drop down boxes, using cell validation, to enter the details of the band and the venue into the Profit calculator. But I realised that this could lead to errors when entering data as the performance code might not match with the correct band and venue from the Expenditure sheet. So I changed the drop down boxes to VLOOKUP functions so that all the necessary information from the expenditure sheet is automatically entered into the Profit calculator when a performance code is entered.

Comment: The candidate evaluates at each step and makes amendments as a result. The candidate demonstrates knowledge of how changes in the rules governing any computer model can affect the final solution.

This screenshot shows that I used VLOOKUP functions to find data automatically from

the Expenditure sheet and the Customer sheet. I named the cells in each of these sheets so that I didn’t need to use absolute cell references and so it was easier for me to see which cells I had used in each function. The screenshots below show the cells that were named. In each case it was all of the columns in the table and 50 rows.



2. I decided to add conditional formatting to the profit cell so that the user could see clearly if an event would make a profit or loss – the cell is green if the value is greater than 0 and red if the value is less than 0.

at the user could see clearly if an event would make a profit or loss – the cell is green if the value is greater than 0 and red if the value is less than 0.

I also used the VLOOKUP function to enter the total expenditure from the Expenditure sheet. I used ‘*’ to multiply the number of tickets sold by the price of each ticket to get the income and then subtracted the expenditure from the income to show the profit.



3. I decided to add an IF function to the Profit calculator so the users can see at a glance if they are making a big enough profit. I decided to use less than 50% or greater than 50% of the ticket income as the logical comparison with the profit.


4. When I had set up my system I realised there were some things that did not work in an

efficient way: the Profit calculator sheet only stored the profit made for one performance at a time so there was no permanent record of the profit made for each performance. So I decided I needed to add another worksheet to the system to permanently record the profit of each performance. I called it Profit Record. I used VLOOKUP functions to insert some of the data automatically, to avoid too many errors.





sed conditional formatting on the Profit column to show if the profit was a positive or negative alue.

om the Profit Record worksheet as a graph

I also uv I also did a graph of the income and expenditure, frgives an easy visual comparison of 2 sets of data.

Comment: The candidate evaluates at each step and makes amendments as a result.


5. I also realised I had made an error on my profit calculator in one of the formulas – the formula in G13 to work out the ticket income was incorrect, as I had used the wrong cell for part of my formula. I corrected this so the formula now multiplied the correct values together.


Comment: The candidate evaluates at each step and makes amendments as a result.


6. When I tested my buttons from each worksheet worked by returning me to the MENU sheet, I

got this error message.

I went into the MAIN_MENU_1 macro which I had recorded to find the error.

I realised that the macro was recorded before I renamed the worksheet MENU, so it was referring to Sheet_3, which didn’t exist. I changed the text from Sheet_3 to MENU and then re-tested the buttons. They all now took me back to the main MENU worksheet.



FORMULAE & FUNCTIONS USED IN MY SYSTEM Naming ranges of cells and use of VLOOKUP:

The screenshot above shows where the cost of the band comes from: the cells named BAND_FEES in the Database of Bands sheet. So when a band name is entered into the Expenditure sheet, the correct fee is automatically entered, as shown below.

Comment: The candidate uses advanced software features appropriately. The candidate shows a good understanding of the software options.

The screenshot above shows the VLOOKUP function that is used to automatically enter the correct cost of hiring a venue into the Expenditure sheet. I used absolute cell references as the data always comes from the same cell for each venue.



Use of SUM function: This screenshot shows the function used to add up the total expenditure for a performance. This value is then automatically entered into the Profit calculator sheet using a VLOOKUP function, shown earlier.

VALIDATION RULES USED IN MY SYSTEM These validation rules show how my system restricts unreasonable data being entered into certain cells: the number of tickets sold in the Profit calculator cannot be more than the tickets available.

Comment: The candidate uses advanced software features appropriately. The candidate shows a good understanding of the software options.



The number of tickets available can only be a whole number between 200 and 1000.

MACROS USED IN MY SYSTEM I used macros in my system to make it more user friendly – so that some tasks can be done automatically. The first macro I recorded was to draw a graph of the expenditure data. I clicked Record Macro on the Developer tab and gave the macro an appropriate name as below.



Then I performed all the actions needed to create a graph – I highlighted the data I wanted in the graph, holding down the ctrl key as I didn’t want to highlight all the columns & I needed columns that weren’t adjacent to each other.

Then on the Insert tab I selected to insert a Column chart

I selected 2-D column chart

The graph appeared on the screen

But I wanted the chart to be in its own worksheet, so on the Design tab I selected Move Chart and chose to save it as a new sheet called EXPENDITURE GRAPH



The graph was then in its own sheet

I then stopped recording the macro. Although I now realised that I didn’t really need a macro to produce the graph as the graph was now part of the system and it will update automatically if any data in it is changed.

Comment: The candidate makes good use of efficiency tools such as wizards. The candidate shows a good understanding of the software options including advanced features.

SETTING UP A SCREEN FOR THE USER TO ACCESS EACH SHEET I then set up a menu screen from where the user can access all the other worksheets in the system by buttons and where a brief description of the different parts of the system is given. I used the Developer tab and selected to insert a button from the Form Controls



The following window came up, for me to assign an existing macro to or to record a new macro for. I needed to record a new macro so I selected New.

I named the macro and when the macro was recording I performed the actions to take me to each of the other sheets in the system. I then edited the text on the buttons to show what each button did.

The screenshot below shows all the buttons added and also I changed the colour of this screen, removed the gridlines and added clipart, to make it more user-friendly.

Comment: The candidate shows a good understanding of the purpose and needs of the user for the task.

Comment: The candidate uses advanced software features appropriately.



candidate style answer - iis windows serverweb1.crypt.gloucs.sch.uk/ict/resources/year...

Documents