Question

The spreadsheet below shows the sales of Jupiter Ltd. made by four salesmen in the four quarters of the financial year 2022-23:

	A	B	C	D	E	F	G
1	Sales in ₹
2	Salesman No.	Qtr 1	Qtr 2	Qtr 3	Qtr 4	Total Sales	Commission @ 10% of sales (₹)
3	S1	6,000	7,000	??	9,000
4	S2	8,000	9,000	8,200	8,500	33,700
5	S3	9,600	8,400	9,200	9,500	36,700	??
6	S4	??	7,600	8,000	12,000
7	Total

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

1. Write the formula to calculate the cost of the goods sold by Salesman No. S2 in Qtr 2, if he had sold the goods at a profit of 10% of the sales.
2. Write the formula to calculate the sales made by Salesman No. S2 in Qtr 3 in cell D3, if he had sold the goods at a profit of 10% of the cost.
3. In Qtr 1, Salesman No. S4 sold goods costing ₹ 8,800 at a loss of 10% of the sales. What is the selling price of the goods in cell B6.
4. The company gives a commission of 10% on its total sales. Write the formula to calculate the commission earned by Salesman No. S3 in cell G5.

Answer 1

1. 

Given: Profit = 10% of sales.

           S2 in QTR S.P = 9,000

Profit =  Selling price − Cost price   ...(formula)

Substituting the profit percentage,

0.10 × S.P = S.P − C.P

Rearranging the formula to solve for CP.

C.P = S.P − 0.10 × S.P

C.P = 0.90 × S.P

 Now, C.P = 0.90 × 9,000

∴ C.P = ₹ 8,100

The cost of the goods sold by Salesman No. S2 in Qtr 2, if he had sold the goods at a profit of 10% of the sales, is ₹ 8,100.

2.

Given: Profit = 10% of cost

            Qtr 1: ₹8,000

            Qtr 2: ₹9,000

            Qtr 4: ₹8,500

Total given sales = 8,000 + 9,000 + 8,500

= 25,500

Now, Subtract this from the total sales for the year to find the sales for Qtr 3:

Sales in Qtr 3 = 33,700 − 25,500 = 8,200 

∴ The sales made by Salesman No. S2 in Qtr 3, if he had sold the goods at a profit of 10% of the cost, is ₹8,200.

3.

Given: Loss = 10% of sales

Cost price = Selling price − Loss    ...(formula)

Since, L = 0.10 × S.P, we can substitute this into the equation:

C.P = S.P − 0.10 × S.P

8,800 = S.P − 0.10 × S.P

8,800 = 0.90 × S.P

∴ S.P = `"8,800"/"0.90"`

= 9777.78

Thus, the selling price of the goods in cell B6 for Salesman No. S4 in Qtr 1 is ₹9,778.

4. 

Given: Total sales of Salesman No. S3 = 36,700

Commission = Total sales × 10%   ...(formula)

                     = 36,700 × 0.10

∴ Commission = 3,670

To place this in cell G5, you would use the formula:

Cell G5 = 36,700 × 0.10

Thus, the commission earned by Salesman No. S3 is ₹3,670.