describe the purpose or function of the SUM formula
- Adds up the content of the cells between two points.
- I used the SUM function SUM(J21:K24) on page 16 screenshot 25 in cell J25
- To add up the total price of each product purchased to produce an overall total.
describe the purpose or function of the COUNT formula
- Counts cells within a specified range that meet a certain criteria.
- I used the COUNT formula COUNTIF(H21:I24,“>0”) on page 20 screen shot 2 in cell D29
- To count the number of numbers in cell range H21 to I24 if they are above 0. This is to show the number for the amount of items purchased.
describe the purpose or function of the MAX formula
- Checks entries within a certain range of cells and returns the one with the maximum figure.
- I used the MAX function MAX(H8:H13) on page 17 in cell H14
- To work out the maximum discount out of the records as well as the maximum total out of the records so they can be automatically calculated.
describe the purpose or function of the MIN formula
- Checks entries within a certain range of cells and returns the one with the minimum figure.
- I used the MIN function MAX(H8:H13) on page 17 in cell H14
- To work out the minimum discount out of the records as well as a the minimum total out of the records.
describe the purpose or function of the AVERAGE formula
- Adds number within a certain range of cells and returns the average figure.
- I used the AVERAGE function (H8:H13) on page 19 in cell H16
- To work out the average from the discounts as well as the average from the totals.
describe the purpose or function of the SINGLE IF formula
- Checks whether a condition is met, returns one value if True and another value if False
- I used the SINGLE IF function =IF(F27=TRUE,“0.15”,“0”) on page 21 in cell G27
- If the “Nandos membership card shown?’ check box was ticked the IF function would return the value ‘True’
- Applying a 15% discount to the total price for the customer’s order.
describe the purpose or function of the TODAY formula
- =TODAY returns today’s date.
- I used the =TODAY function on page 22 in cell I16
- To show the date on which the transaction occurred
- So when the invoice is printed out the customer knows that it is up to date.
describe a method (VLOOKUP) of simplifying data entry you created in your spreadsheet stating two different advantages of using such techniques for entering data into a spreadsheet.
- Looks up and retrieves information from a table and returns a corresponding value.
- I used the VLOOKUP function on page 9 screenshot 12 in cell range G21 to G24
- To find the price of the product
ADV=
- Reduces data entry errors
- fast speed of data entry
- if a price changes you only have to change it in the reference table.
DISADV=
- errors in the lookup table will mean errors in the result
- if tables are moved or renamed this function will not work
describe a method (SPINNER) of simplifying data entry you created in your spreadsheet stating two different advantages of using such techniques for entering data into a spreadsheet.
- Allows user to change quantity and investigate the outcome.
- I used a spinner on page 8 screenshot 10 cell range H21 to H24, using a button
- To let viewers see how input changes will alter the outputs in a model
- Allowing you to see the different outcomes more easily.
describe a method (CHECK BOX) of simplifying data entry you created in your spreadsheet stating two different advantages of using such techniques for entering data into a spreadsheet.
- If the box is clicked a tick appears, tick again and it disappears. Used to turn an option on and off.
- I used a checkbox on page 9 screen shot 11 cell range B27 to E27
- To click in the cell for TRUE/FALSE data placing a tick in the checkbox in cell B27.
ADV=
-increases efficiency as it saves time.
describe a method (DROP DOWN LIST) of simplifying data entry you created in your spreadsheet stating advantages of using such techniques for entering data into a spreadsheet.
- Gives the user a choice from a pre determined list.
- I used a drop down list on page 8 screen shot 9 in cell C21 for a list of the food items purchased.
- To restrict the users to a set choice of data to the items in the list such as chicken wings, whole chicken, and chicken roulette
ADV=
-reduces data entry errors -keeps data entered consistent
Describe the purpose of the following feature (SORT) you have used in your spreadsheet.
-To sort a set list of data into a specific order, eg. alphabetical, numerical
-I used sort on page 13 screen shot 20 for the total prices of customer’s purchases
descending from highest to lowest
-To make it a lot easier to see who came up with the highest total on a graph.
Describe the purpose of the following feature (SEARCH) you have used in your spreadsheet.
- To search a set list of data for a specific item of data.
- I used search on page 15 screen shot 24
- For the customers that lived in Pensby
- So it was easier to see the amount of customers that live in Pensby
- Which could then be presented on a graph.
Describe the purpose of the following feature (GRAPH) you have used in your spreadsheet.
- A visual representation of specified data.
- I used a graph on page 26 for total amount of money spent by each customer
- Which allowed me to clearly see who has spent the most money and who has spent the least money on their transaction.
Describe the purpose of the following feature (MACRO) you have used in your spreadsheet.
- A stored list of instructions which is used to automate a task ( OR a small program to reduce the number of keystrokes).
- I used a navigation macro (macro button 1) on page 6 -7 screen shot 5-8 in cell
- Which takes the user to the invoice page.
ADV=
they make my spreadsheet more user friendly and make moving backwards and forwards more efficient.
describe a method (RANGE CHECK) of simplifying data entry you created in your spreadsheet stating two different advantages of using such techniques for entering data into a spreadsheet.
- Checks that data entered is within two defined numbers.
- I used the range check on page 10- 11 screenshot 13-16 in cell H21 to H24
- Which allows only allowing whole numbers between 0 and 5 to be entered
- I then created an error message for when data was entered that had decimal places which said “Only whole numbers can be used”
ADV=
-makes sure data entered is reasonable
how to answer questions on formulas?
1 mark for stating what formula you used (stating where it is on your spread sheet print out and which cell it has been applied to) and what you used it for e,g I used SUM to add up all the costs
1 mark for stating why the formula was used for what you needed it to do e.g i used this formula to add up all the cost so I could work out my total income.
How to answer questions about data validation techniques?
1 mark for what type of technique you used e.g i used a list box
1 mark on detail which could be how you made that technique or why it was used or a first advantage
1 mark on why which is another advantage
How to answer purpose of function answers?
1 mark on what function you used and what on e.g i used a list box on transport methods in cell D3
1 mark on why it was used (kind of like and adv) e.g it made it easier to look for people with surnames
what is meant by simulation modelling
using a computer and mathematical formulas to imitate or predict the behaviour a real life phenomenon. They work by looking at “what if” scenarios
giving an example for each, state the advantages of financial modelling?
- Automatic recalculation of wages: can be done if data such as the rate of pay changes
- Can do ‘What if’ calculations for options: on comparing the cost of different materials/suppliers
- Can draw graphs for reports: to highlight wages of different departments/ compare monthly wage bill
- Accurate calculations of wages/quotes: which will save time/ will increase efficiency and save time
what are some examples where financial modelling may be used and what are the benefits of using simulation modelling for these examples?
- Allows the government to predict what will happen if they change taxes or VAT or interest rates
- allows the government to see what savings will be made if they make employees redundant
- How much will the extra payroll cost be if a company gives its workers a 3% rise in salary
- Can see the effect of different situations on the stock market
Benefits:
- it is possible to experience a lot more situations
- cheaper as you do not waste money on impossible actions
- it can give the government time to prepare for a financial crisis
- It can save time as you can roll on the model to see what it is like after more than one year
how are weather simulation created?
- COLLECT DATA (large number of measurements such as pressure, temperature, humidity etc. from all around the world from satellites)
- DERIVE MODEL ( from laws of physics. The model produce a set of mathematical equations which are solved to predict weather twice a day.)
- TEST/COMPARE (create the model, test the model and carry out what if investigations
what are the advantages of weather simulation modelling?
- only takes one hour to produce a six day forecast
- can predict the path of hurricanes
- can help farmers plan their work
