Tuck shop Spreadsheet

In this example the spreadsheet has been set up to calculate the profits of a school tuck shop. The various products have been put in column A, the warehouse costs of the products in column B and the retail selling price put in column C. The profit margin in column D will be the retail cost minus the warehouse cost. The profits in column F will be the profit for each individual item times the number sold.

A / B / C / D / E / F
1 / Cost / Retail / Profit Margin / Number sold / Profits
2 / Mars bars / £0.30 / £0.45 / 32
3 / Twix / £0.25 / £0.40 / 17
4 / Snickers / £0.25 / £0.40 / 34
5 / Cream eggs / £0.20 / £0.35 / 52
6 / Plain crisps / £0.15 / £0.35 / 24
7 / Cheese crisps / £0.15 / £0.40 / 63
8 / Bacon Crisps / £0.15 / £0.40 / 42
9 / Can Coke / £0.15 / £0.60 / 92
10 / Can Lilt / £0.15 / £0.60 / 84
11 / Can Pepsi / £0.15 / £0.60 / 77
12 / Can Tango / £0.15 / £0.60 / 56
13 / Total income
14 / Commission

Question 1 In the space below, write down the formula that would calculate the profit made on each Mars Bar

Question 2 In the space below, write down the formula that would calculate the profit made on each packet of Plain Crisps

Question 3 Write down the formula that would calculate the total profit made on all the cans of Coke sold in the shop (cell F9)

Question 4 Write down the formula that would calculate the total profit made on all the produce sold in the shop in cell F13

Question 5 You have to pay a commission of 10% of the profits back to the school. What formula would you write in cell F14?

Extension task Copy the information into a spreadsheet and enter all the formulas.

See if your formulas work and make a print in normal and formula view.