Fig. 1 shows a worksheet layout for a student-run refill shop. The shop sells products by weight. Column C holds the mass purchased in grams, D holds the pr...
Question 1 Report
Fig. 1 shows a worksheet layout for a student-run refill shop. The shop sells products by weight. Column C holds the mass purchased in grams, D holds the price per 100 g, E calculates product cost, F adds a fixed container deposit held in J1, and G displays the final cost. The team needs formulas that can be copied down, checks for orders below 100 g, and a clear chart for a presentation. They will save the completed spreadsheet before printing a customer summary.
(a) Create a formula in E2 to calculate the product cost. [3]
(b) Create a formula in F2 that uses the container deposit in J1. [2]
(c) Create a formula in G2 to calculate the final cost. [2]
(d) Use conditional formatting to identify orders below 100 g. [2]
(e) Create a suitable chart type to compare final costs by product. [2]
(f) Explain why the price in D2 should not be formatted as a percentage. [2]
(g) Give a suitable filename before the worksheet is saved. [1]
(h) Identify the absolute reference needed for the container deposit. [2]
Answer Details
(a) Enter =C2/100*D2 in E2 [3]. Since D2 is the price per 100 g, divide the mass by 100 before multiplying by that price. For 250 g at £0.38 per 100 g, the product cost is \(250/100 \times 0.38 = £0.95\).
(b) Enter =$J$1 in F2 [2]. This returns the fixed deposit and keeps its reference fixed when copied down.
(c) Enter =E2+F2 in G2 [2] to add product cost and deposit.
(d) Select the mass cells and apply conditional formatting for values less than 100 [2].
(e) A bar chart or column chart is suitable for comparing final costs by product [2].
(f) D2 is a monetary price, not a proportion [2]. Percentage formatting would misrepresent £0.38 as 38%, rather than showing a price of £0.38.
(g) A suitable filename is Refill_shop_orders.xlsx [1].
(h) The required absolute reference is $J$1 [2]. It locks both column J and row 1 during copying.
Download The App On Google Playstore
Everything you need to excel in your exams
