8.1. Creating a spreadsheet
A subsection of Information Technology, 9626, through 8. Spreadsheets
Listing 10 of 27 questions
Here is a spreadsheet created by the organisers of a concert. They have assumed that 400 people will attend the concert. They have also assumed that each member of the audience will purchase 2 snacks and 2 drinks during the concert. If these assumptions are correct, their profit will be $710. A B C D Tickets Drinks Snacks Souvenirs Programmes Electricity $20 $15 $3 $2 $40 Price Quantity per concert goer Total income $16,000 Income $1,600 $2,400 $8,000 $6,000 $34,000 $650 Paid out $22,000 $860 $880 $5,000 $2,400 $1,500 $33,290 Total outgoings $710 Profit/Loss Estimated audience size Performers Drink supplies Snack supplies Venue hire Souvenir supplies Programme supplies A number of factors could affect the amount of profit. • The audience size might be higher than the organisers estimated. • Members of the audience might not buy 2 snacks and 2 drinks. • The cost of souvenir supplies might change. Explain how the organisers could use what-if analysis to see the effects of these three factors on their profit. You should refer to specific cells from the spreadsheet in your answer. The organisers wish to achieve a larger profit. By explaining what each step requires them to do, describe how the organisers would use goal seek to find the size of the audience needed to make a profit of $5000. You should refer to specific cells from the spreadsheet in your answer. Include in your answer a reason why the result might not be satisfactory.
9626_w22_qp_12
THEORY
2022
Paper 1, Variant 2
View
A company sells glass for installation into rectangular windows. The company cannot make glass for windows with dimensions smaller than width 12 cm and height 8 cm. The price of a window is calculated by working out the area of the window and multiplying it by the cost per square centimetre (cm2), which is 20₹. a b Area of window = a × b Price = area × cost per cm2 A spreadsheet with formulas has been created to carry out these calculations. A Input a Input b Area is: Cost per cm2 is: Price is: D C cm cm cm2 rupees rupees B These cells will be validated using a range check
9626_m19_qp_32
THEORY
2019
Paper 3, Variant 2
View
The spreadsheet below shows information about sweets that a shop sells. The data has been formatted but the spreadsheet needs some improvements. Nutty elete Item Y X $1.10 $0.95 $1.05 $0.80 $1.30 $1.10 $1.30 $0.90 $0.20 $0.15 $0.25 $0.10 Cost pSelling Profit Amount sold A B C D E F Describe, in detail, what would happen within the spreadsheet if the mouse was double- clicked with the pointer positioned at X. Describe, in detail, what would happen within the spreadsheet if the mouse was double- clicked with the pointer positioned at Y. Originally, the data was entered into the cells in the range B2 : C5 as numbers. Describe the formatting that has been applied to the cells in this range. Validation is to be applied to cells B2 to B5. This will prevent numbers less than the current minimum value from being entered. The shop owner will not use a range check or a lookup check. Name and describe the validation check that will be used. Please for Question 12.
9626_s21_qp_12
THEORY
2021
Paper 1, Variant 2
View
A secretary has been given a list of product details. She was asked to type the details into a spreadsheet. Unfortunately, when she typed them in, wrong data was entered into cells A7, A10, A16 and B10. List secretary was given Spreadsheet produced by secretary Product number Department code B167 B132 C185 A675 D132 B453 E342 D543 E723 Z418 M329 B132 A675 E647 F341 C231 Describe four validation checks which could have prevented these errors from occurring, including a description of how they would have prevented the errors. The secretary’s manager typed a formula into cell D2 to calculate the price of the product which is given at the beginning of each Product number. Write down the formula which he would have entered in cell D2 and describe how the Price was formatted so that it would appear as it does in the spreadsheet. = You may use this space for working out.
9626_w18_qp_12
THEORY
2018
Paper 1, Variant 2
View
An IT specialist is employed by a car showroom to manage the spreadsheets which record all details of car sales. Here is a spreadsheet showing some of the cars for sale in a showroom. Make Model Licence Price Engine Economy (L/Km) Year made A B C D E F G H I Daui Tiaf Arajug Daui Tiaf Tiaf Arajug Daui Tiaf Arajug Tiaf JPY 648 FGB 721 FFA 419 BFK 297 GFL 364 HDC 684 JYU 381 KJN 384 GXL 347 KNN 478 LSA 442 1.8 1.4 1.4 1.2 1.2 1.2 1.8 1.6 1.2 1.4 18.0 16.7 13.7 12.0 12.1 12.3 15.5 18.5 16.3 16.6 19.9 Daui $25,000 $15,500 $18,000 $10,500 $11,000 $12,500 $42,500 $28,000 $19,000 $47,500 $45,000 Visette EX Firetips Firetips EX Delrah JX Visette E18 =SUMIFS(E5:E15, A5:A15,I6,G5:G15,">"&I8) fx ✓ ✗ The IT specialist has entered a formula in E18 after receiving a request from the showroom manager. Write down the information the showroom manager asked for. Explain, step by step, how the spreadsheet software calculated the result. Describe, step by step, how the IT specialist could set up the cell so that the user could just use a mouse to change the input in cell I6. The IT specialist accidentally saved the spreadsheet as a csv file. Explain what a csv file is. Describe the changes the user would see in the spreadsheet after it is opened again using spreadsheet software.
9626_w20_qp_12
THEORY
2020
Paper 1, Variant 2
View
Below is an extract from a spreadsheet showing the wages paid in one week to a group of workers. Their wages depend on the type of job they do as well as the number of hours worked. Cells C2 to E4 contain the data which is referred to in the lower part of the spreadsheet.  A B C D E F G Standard rate per hour L Labourer Se Semi-skilled Sk Skilled Worker ID Job Code Job type Hours worked this week Rate per hour paid Wage paid this week 8 12478L L Labourer 9 13691R Se Semi-skilled 10 21463R Se Semi-skilled 11 29816M L Labourer 12 36479L Sk Skilled 13 49786R Sk Skilled 14 52376M Se Semi-skilled 15 18964L L Labourer 16 13578R L Labourer 17 38945M L Labourer 18 36249M Se Semi-skilled Write down the formula which should be used in cell C8 in order to display the Job type using the Job Code in the preceding column. The formula should be in a form that can be easily replicated. = You can use the space below for any working you need. The spreadsheet contains different formulae in different columns. Explain the other formulae used in the spreadsheet which enable the data in the Wage paid this week column to be displayed. The formulae should be in a form so that they can be easily replicated.
9626_m17_qp_12
THEORY
2017
Paper 1, Variant 2
View
The spreadsheet shows the values of used cars and the deposits required to buy them. Car make Diap Diap Sentel Sentel Panther WRM WRM Diap Panther WRM Diap Panther Sentel WRM D5 D1 M810 R500 ZE WB WA D4 ZK WB D3 ZJ G47 WB $4,752.00 $6,732.00 $6,655.00 $5,445.00 $8,470.00 $10,285.00 $12,100.00 $4,235.00 $9,438.00 $7,502.00 $5,082.00 $10,890.00 $11,374.00 $10,527.00 $475.00 $673.00 $666.00 $545.00 $847.00 $1,029.00 $1,210.00 $424.00 $944.00 $750.00 $508.00 $1,089.00 $1,137.00 $1,053.00 Car model Car value Deposit required A B C D E D2 ✘ ✔ =ROUND(C2*10%,0) fx Explain what is meant by the term ‘formula’. Refer to the contents of cell D2 in your answer. Explain what is meant by the term ‘function’. Refer to the contents of cell D2 in your answer. The spreadsheet has now been sorted. Car make Diap Diap Diap Diap Panther Panther Panther Sentel Sentel Sentel WRM WRM WRM WRM D5 D4 D3 D1 ZK ZJ ZE R500 M810 G47 WB WB WB WA $4,752.00 $4,235.00 $5,082.00 $6,732.00 $9,438.00 $10,890.00 $8,470.00 $5,445.00 $6,655.00 $11,374.00 $7,502.00 $10,285.00 $10,527.00 $12,100.00 $475.00 $424.00 $508.00 $673.00 $944.00 $1,089.00 $847.00 $545.00 $666.00 $1,137.00 $750.00 $1,029.00 $1,053.00 $1,210.00 Car model Car value Deposit required A B C D E Identify the steps that have been taken by a user to produce this sorted spreadsheet.
9626_s21_qp_13
THEORY
2021
Paper 1, Variant 3
View
Questions Discovered
27