Creating a spreadsheet model to help in planning educational visits
To create a spreadsheet model like the one above:
- Type in the headings as above, or similar.
- Enter possible locations for an educational visit in column A.
- To format the cells under the headings in columns C, E, G, H, I and J to display amounts of money correctly, select and highlight the relevant cells by clicking and dragging. Now click on Cells in the Format menu and select "Currency" from the list on the right of the resulting dialog. Click OK.
- In cell I2, enter the formula =C2*D2+E2*F2+G2-H2
to
- multiply the cost per child by the number of children added to
- the product of the cost per adult and the number of adult helpers going on the visit added to
- the cost of the coach
- less the amount of subsidy available from school funds.
- Copy the formula to cells I3 to I8 by dragging
the fill handle from cell I2 down to I8.
- In cell J2, enter the formula =I2/D2 to work out the cost per child of the visit. Copy the formula down the column using the method described in 5.
- In cell K2, enter the formula =IF(AND(B2>=7,J2<5),"Go for it!","Too pricey!"). This formula uses an IF statement to determine whether the visit will be viable or not. In this case the visit is judged to be viable if the value in the Educational Value column (B) is greater than or equal to 7 out of 10 AND the Cost per child in column J is less than £5.00. The statements Go for it! or Too pricey! are inserted in the cell if the criteria are met or not met. Copy the formula down the column using the method described in 5.
- Use the colour, clip-art and border tools to give the spreadsheet an attractive appearance.
- Now you can manipulate some of the variables in
the spreadsheet to identify a visit which meets the appropriate learning outcomes and is not too expensive.
- Variables which can be changed include the number of adult helpers and the amount of subsidy from school funds;
- You could also change the conditions in the IF statement, eg. =IF(AND(B2>=5,J2<6),"Go for it!","Too pricey!") would accept a lower Educational Value and a higher Cost per child.
Download the file - visits.xls 
