I.T
COURSEWORK
(Spreadsheet)
Analysis
The problem the Lancre Lunch box, want me to solve is to create a system calculating the price she would charge for any number of different fillings in a sandwich and the bread and butter or low fat spread and rolls. This will help Karen calculate the price of each sandwich and roll easily and will keep her calculations organised.
1. The form of output that will take place is a disk given to her by me, which will contain the system I have created for her. Her form of output will be her monitor that will display the system I have created for her.
2. The information to be output is:
A system, which calculates the price, she should charge for sandwiches and rolls
Add 5p to the cost of each pack because this is the cost of the container.
Add £1 profit to the total
I must also show the ingredients
3. The data needed to produce the output is:
- (This information is all given to be by Karen on disk)
- Add 5p to the cost of each pack because this is the cost of the container (conversation on page 8)
- Add £1 profit to the total (conversation on page 9)
- I must have the list of costs and they must have:
- Items (on disk)
- Cost of a pack (on disk)
- Number of portions (on disk)
- Cost of bread from Karen’s list (on disk)
- Cost of butter/low fat spread (on disk)
- Cost of rolls (on disk)
- Fillings (made by me)
4. The desired outcomes and performance criteria:
- She must be able to change the costs of each item on the computer
- It must be clear when Karen has to type things in
- The answer must stand out
- The sandwich price must be rounded up to the nearest 10p. This might make her profit slightly less than £1
- System must be easy to understand
- Must add 5p to the cost of each pack because this is the price of the container
- Must add a profit of £1
- I must test my system by working out the cost of a portion then I must divide the cost of the pack by the number of portions
- My system should look professional
- I should use a heading to add to Karen’s understanding of what the system is about
- I must give Karen space to add new items
- I must have the cost of the pack
- I must have the number of portions
- I must have the item
- I must make it clear to Karen were to type in things
- Answer must include bread, butter/low/ fat spread, container and profit as well as fillings
- Must allow for bread and rolls
- Must be able to choose the fillings to use
- Must be able to choose the fillings to use
- Solution must work
5. Testing will be needed to test if my system works correctly. I will test it by dividing the cost of the pack by the number of portions. I am going to try it out for a cheese and tomato sandwich with bread; low fat spread and the packaging and the profit should be £1.40. If my solution works, I will use it to work out the cost of ham, cheese and pickle roll with low fat spread.
Design
I will begin to solve Karen’s problem by opening a spreadsheet program called Microsoft Excel. I am going to use Microsoft Excel because I can:
- I can perform calculations
- I can perform formulas to make calculations
- I can format cells
- I can change the font size and style
- I can change the text alignment
- I can add borders and lines
- I can insert extra rows and columns
- I can change column width and row height
- I can change the format of the cell into currency
- I can add graphs and charts
- I can sort data easily
Some of these features will be useful to me because I will need some of them to solve Karen’s problem professionally.
Open the ‘2006 GCSE’ folder then follow these steps:
- Click on the excel file called ‘fillings’
- A spreadsheet should appear with fields
- Highlight the fields then copy it
- Open a new Microsoft Excel and paste it onto the third row
- When finished, make the columns wider until the fields fit
- Then in the column next to the ’Portions’ enter ‘Cost of portion’ as shown Fig 1
- When finished you’ll have to work out the cost of a portion
- Do this by dividing the cost of a pack by the number of portions as shown Fig 2
- Do this for all the fields, until finished
- When finished highlight all the fields then right click it and click on ‘Format’
- Then click on ‘Currency’ then click ‘OK’
- When finished change the width of the column until the fields fit in
- Then click on the cell D16 and right click it
- Then click on ‘Format’
- Then on ‘Currency’
- Instead of making it to 2 decimal place, make it to 3 decimal places as shown in Fig 1
- Know you should have four columns and they are: ‘Items’, ‘cost of a pack’, ‘portions’, ‘cost of portions’ as shown Fig 1
- If so you’ll have to make 2 new columns next to the ‘Cost of portions’ column
- In the first column next to the ‘Cost of portions’ column enter ‘Portions wanted’ then under it enter 1 until you get to the end of the items as shown Fig 1
- When finished, change the width of the column until all the fields fit as shown Fig 1
- Then in the column next to the ‘Portions wanted’ column enter ‘Price’ as shown Fig 1
- When done, you’ll have to do a calculation
- You do this by multiplying the cost of the portion by the portions wanted
- Do this for all the fields until finished as shown Fig 2
- When finished highlight all the fields then right click it and click on ‘Format’
- Then click on ‘Currency’ then click ‘OK’
- Know you’ll have to create a ‘Refresh’ button which will reset the ‘Portions wanted’ column to ‘0’
- You do this by clicking the icon ‘View’ which is located in the toolbar then on ‘Toolbars’, then on ‘Forms’
- Know there should be a toolbox on the spreadsheet
- Click on the ‘Button’ icon and create a button underneath the ‘Portions wanted’ row
- A pop-up screen should appear
- Click on the icon ‘Record’
- A second toolbox should appear
- Know for all the fields in the ‘Portions wanted’ column make them into zero than click on the icon ‘Stop’ which is in the second toolbox
- Know you should have a Refresh button called ‘Button 1’
- To change the name of the button to ‘REFRESH’ right click on the button and click on ‘Edit text’ as shown Fig 1
- Then enter ‘REFRESH’ as shown Fig 1
- Know you’ll have to add the cost of container and profit to the spreadsheet because that is required
- To do this on row 20 under the column ‘Item’ enter ‘Cost of Container:’
- Then beside enter 0.05
- When finished highlight all the fields then right click it and click on ‘Format’
- Then click on ‘Currency’ then click ‘OK’
- Then below the field’ Cost of Container’ enter ‘Profit:’
- Then beside it enter 1
- When finished highlight all the fields then right click it and click on ‘Format’
- Then click on ‘Currency’ then click ‘OK’
- Know you’ll have to make a cell which will add the price of how may portions wanted, cost of container and the profit
- It should also be rounded up to the nearest 10p
- To do this go to row 23
- Then underneath the ‘Item’ column enter ‘Total cost of sandwich/roll:’ this should take up to cells as shown Fig 1
- So you’ll need to merge it
- To merge the cells click on the icon ‘Merge and Centre’ which is located in the toolbar
- Now you’ll have to make a calculation
- So, in the cell next to ‘Total cost of sandwich/roll:’ enter ‘=ROUND(F4+F5+F6+F7+F8+F9+F10+F11+F12+F13+F14+F15+F16+F17+B20+B21,1)’ as shown Fig 2
- This will cause the answer to round up to the nearest 10p and to automatically change when the ‘Portions wanted’ changes.
- Make this cell into Bold as shown Fig 1
- Know you’ll have to create a heading to do this Click on the cell B1 and enter ‘Calculations for the Price of Sandwiches and Rolls’ as shown Fig 1
- When finished highlight the cells that have the heading
- Click on the ‘Merge and Centre’ icon which is located in the toolbar
- When finished highlight the whole sheet and change the font to Tw Cen MT as shown Fig 1
- Then change the font size to 12pt Fig 1
- Now for the heading change the font size to 14pt and make it bold as shown Fig 1
- For the cell C23 make it bold as shown in as shown Fig 1
- If you have successfully followed these steps then the system should be correct.
