Business Modeling Using Excel: Case - Shoe Demand and Supply

Contribution:
u11321-Paarki Malhotra, paarkhimerotra11@gmail.com
u113099-Prachi Agarwal, prachi.agarwal09@gmail.com

Sachin owns a shoe store in Bhubaneswar. He recently opened a store in Pal Heights and for XIMBians. Sachin plans to offer a new promotion for the month of July. Sachin can order shoes directly from the wholesaler for Rs.1000/-. During the promotion period, he wants to sell the shoes for Rs 1500/-, an attractive discount from the MRP of Rs.2500/- a pair.

To get shoes at Rs.1000/- from the wholesaler, he must place an order two month in advance. If customer demand exceeds the quantity Sachin ordered, then he has to get the shoes from another dealer for Rs. 1500/- per pair. After the promotion period is over, remaining shoes he has to sell it at Rs.1800/-.

Your task: Build a spreadsheet that captures the information above and allows Sachin to change the values of quantity Ordered and Demand in the model to see how revenues and costs are affected under different conditions.

a. What happens if Sachin orders 200 pairs of shoes but demand is only 240?

b. If he orders 100 pairs of shoes and demand is 50?

Solution –

To compute – revenues and costs of pairs of shoes

Notice that the problem covers the following scenarios. The output (revenue and cost) under all these scenarios will be different so, we need to determine it under all these scenarios.

1. Shoe sale during the promotion period

a. demand exceeds the quantity ordered by Sachin

b. quantity with Sachin exceeds the demand

2. Shoe sale after the promotion period

a. demand exceeds the quantity ordered by Sachin

b. quantity with Sachin exceeds the demand

Steps to taken -

1. Make a table for recording Demand and Stock Records for Sachin during promotion period. It should have the following columns –

a. Demand

b. Stock available with Sachin (Stock)

c. Total cost price to be incurred on the demanded number of shoes (CP)

d. Total selling price of the demanded number of shoes (SP)

Demand and Stock Records for Sachin during promotion period

Demand	Stock	CP	MP	SP	Profit/Loss

2. If the demand is 240 and the sock available is 200 the total cost price incurred by Sachin will be (200*1000)+(40*1500) = 26000.

However, if the demand had been less than or equal to the stock available, CP would have been only demand*1000.

So we use an ‘if’ condition as shown.

3. Total selling price of the demanded quantity of shoes during the promotional period will be quantity demanded*1500.

4. So, the profit or loss is SP-CP.

5. Now, enter a case where stock is more than demand, say demand = 50, stock = 100. Copy and paste the cells under CP, SP and Profit/Loss down.

6. Now, make a similar table for modeling sales after the promotion period. Enter values of demand and stock as shown in the image below. The total cost price will be same as computed earlier. The total selling price in this case will be (50*1500)+(50*1800)=165000.

If the quantity available would have been less than or equal to the demand, the selling price would simply have been demand*1500.

So, we use an ‘if’ condition –