Question

Mink & Sons run a bakery that sells sandwiches, cookies, muffins and pastries. The raw material is sourced from a well-known supplier and fresh items are prepared every day for the customers. The cost of each item also includes the cost of cutlery and paper napkins.

During the festive season, the bakery gives small discounts to its customers.

The spread sheet given below is a summary of its Purchases, Sales and Unsold Stock for the month of October 2023:

	A	B	C	D	E	F	G	H	I	J	K
1	Bakery items	No. of items prepared	Cost price per item (₹)	Total cost (₹)	No. of items sold	List price per item (₹)	Festival Discount per item (₹)	Total sales (₹)	Cost of items sold (₹)	Cost of unsold stock (₹)	Profit (₹)
2	Sandwiches	275	80	22,000	220	105	5	??	17,600	4,400	4,400
3	Cookies	250	50	12,500	220	75	5	15,400	??	1,500	4,400
4	Muffins	330	40	13,200	300	75	5	21,000	12,000	??	9,000
5	Pastries	225	60	13,500	200	95	??	18,000	12,000	1,500	6,000
6	Total	1,080		61,200	940						23,800

Based on the above transactions and the information given in the spreadsheet, answer the following questions:

1. Write the formula to calculate the total sales of sandwiches in cell H2.
2. Give the formula to calculate the cost of cookies sold in cell I3
3. Write the formula to calculate the cost of unsold stock of muffins in cell J4.
4. 1. Give the formula to calculate the festival discount on the sale of Pastries in cell G5.
   2. Calculate the amount of festival discount per pastry in cell G5.

Answer 1

i.

To find the total sales value, you subtract the discount from the list price to find the net selling price per item, and then multiply this by the number of items sold. The formula to put in cell H2 is:

= E2 × (F2 − G2)

= 220 × (105 − 5)

H2 = 22,000

ii. 

The formula to find the total cost of cookies sold will be the product of the number of cookies sold and the cost price per cookie. 

= E3 × C3

= 220 × 50

I3 = 11,000

iii.

To calculate the cost of unsold stock of muffins in cell J4, you will need to determine the number of muffins that were unsold and then multiply this number by the cost price per muffin.

The formula to input in cell J4 will be:

= (B4 − E4) × C4

= (330 − 300) × 40

= 1,200

iv. 

a) The formula to find the discount per pastry can be set up by first calculating the expected revenue without any discount (i.e., list price multiplied by the number sold) and then subtracting the actual sales:

• Expected Revenue without discount = List Price per Pastry × Number of Pastries Sold
• Subtract the Total Sales from this to find the total discount.
• To find the discount per item, divide this total discount by the number of pastries sold.

The formula to calculate the festival discount per pastry and input into cell G5 would be:

= `(F5 × E5 − H5) / (E5)`

= `(95 × 200 − 18,000) / (200)`

= 5

b) Using the formula derived above:

• List Price per Pastry (F5) = ₹ 95
• Number of Pastries Sold (E5) = 200
• Total Sales (H5) = ₹ 18,000

Substitute these values into the formula:

• Expected Revenue without discount = ₹ 95 × 200 = ₹ 19,000
• Total discount = ₹ 19,000 − ₹ 18,000 = ₹ 1,000
• Discount per pastry = ₹ 1,000 / 200 = ₹5

Thus, the festival discount per pastry is ₹ 5. This is the amount you would input into cell G5, reflecting a reduction in price per pastry due to the festive discount.