Business Data Management

End-Term Submission

Case Study of Bansal Kirana Store

Name : Kartik Valecha
Email : [email protected]
Roll No. : 21f1000089
Table of Content

1) Executive Summary and Title :.................................................................................................... 3

2) Detailed Explanation of Analysis Process/Method :..................................................................4
2.1 : Data Analysis For Sales and Expenditure............................................................................. 4
2.2 : Increase the overall profit of the business............................................................................. 6
2.3 : Optimizing inventory.............................................................................................................. 7
2.4 : Fixed Cost Analysis............................................................................................................... 8
3) Results and Findings :..................................................................................................................9
3.1 : Volume Analysis (Sales , Purchase)......................................................................................9
3.2 : Profit / loss Analysis.............................................................................................................13
3.3: InventoryAnalysis..................................................................................................................15
3.4: PL & INSIGHTS.................................................................................................................... 16
4) Interpretation of Results and Recommendation...................................................................... 17
4.1 : Recommendation 1: Increase the sales of high profit item (such as :- Dry Fruits & Ghee). 17
4.2 : Recommendation 3 : Increasing Sales of Milk.....................................................................18
4.3 : Recommendation 2 : Restock inventory earlier than usual or during high sales periods.... 18
4.4 : Recommendation 4 : Increase Current Ratio...................................................................... 19
4.5 : Recommendation 5 : Additional Steps.................................................................................19
4.5.1 : Effective Pricing Strategies......................................................................................... 19
4.5.2 : Enhance Store Display and Visual Merchandising..................................................... 19
5) Conclusion :.................................................................................................................................20
6) Important Links :......................................................................................................................... 20
1) Executive Summary and Title :

Bansal Store is a medium-sized kirana store, started by Mr. Raj bansal in early 2018 , currently
encountering challenges in terms of profit and inventory management which is having an indirect
impact on the store's net profit and sales. The purpose of the proposed capstone project is
centered around the objective to understand the complexities of managing or controlling
inventory/good flow , enhancing the sales and formulating marketing strategies that increase net
profit and sales for the respective store.

Considering so , Primary goal of our capstone project will be to increase net profit , optimize and
check inventory to determine the optimum purchase time and manage goods flow. To achieve
this , the project will entail an in depth analysis of the sales data along with fluctuation in
purchase price over the course of month. Identifying the gaps and areas of improvement in the
current strategy will be a critical aspect of this project. The report will also include in depth
analysis of the sales data to identify patterns and trends in the sales. This analysis will allow us to
identify the best and worst performing SKUs, enabling us to understand and formulate marketing
strategies to improve net sales leading to an increase in net profit.
After a thorough analysis, the project report will focus on recommendations to combat the
problem areas identified above.

To effectively analyze the sales data and make informed business decisions, I will be utilizing
various Excel tools such as pivot tables, bar graphs, and line graphs etc .. that can provide
valuable graphical representations. These tools enable a visual representation of the sales data,
making it easier to identify trends, patterns, and key insights. By analyzing the outperforming
and underperforming products using these Excel tools, recommendation , marketing strategy and
data-driven decisions to optimize revenue generation can be formulated.
2) Detailed Explanation of Analysis Process/Method :

2.1 : Data Analysis For Sales and Expenditure

As mentioned above MS Excel is the main tool which will be used for the analysis.
Firstly sales data is collected in an unstructured format along with prices of each product from
Bansal Store over the period of 31 days.

Shop’s Way of storing data (informal)

This raw data is then entered into excel and basic data pre-processing tasks such as imputing,
typing errors, sorting etc.. are done.

● The pre-processed sales data have a total of 21 columns where 10 columns represent each
SKUs sales quantity along with the date (1 column) and 10 more represent each SKUs
price on the given day .
● Using sales and selling price revenue for the day , average sales , selling price and total
revenue can be calculated by formula :

𝑅𝑒𝑣𝑒𝑛𝑢𝑒 = 𝑆𝑒𝑙𝑙𝑖𝑛𝑔 𝑝𝑟𝑖𝑐𝑒 * 𝑆𝑎𝑙𝑒𝑠

𝑇𝑜𝑡𝑎𝑙 𝑅𝑒𝑣𝑒𝑛𝑢𝑒 = ∑ 𝑅𝑖​

𝑖=0
𝑡ℎ
where 𝑅𝑖 = Revenue made at 𝑖 day

● Similarly purchase data has been collected for every SKUs which consists of purchase
quantity and purchase price , using which expenditure is calculated on each SKU.

● Along with expenditure Total expenditure for the day , average expenditure per SKU as
well as Total expenditure is calculated for 31 days using formulas :
 𝐸𝑥𝑝𝑒𝑛𝑑𝑖𝑡𝑢𝑟𝑒 = 𝑃𝑢𝑟𝑐ℎ𝑎𝑠𝑒 𝑄𝑢𝑎𝑛𝑡𝑖𝑡𝑦 * 𝑃𝑢𝑟𝑐ℎ𝑎𝑠𝑒 𝑃𝑟𝑖𝑐𝑒

𝑇𝑜𝑡𝑎𝑙 𝐸𝑥𝑝𝑒𝑛𝑑𝑖𝑡𝑢𝑟𝑒 = ∑ 𝐸𝑖​

𝑖=0
𝑡ℎ
where 𝐸𝑖 = Expenditure at 𝑖 day

2.2 : Increase the overall profit of the business

● During my discussion with the business owner we discovered that due to high
competition and entry of new shops and online grocery shops profits were declining ,
were not steady and it's becoming difficult for them to survive.

● Hence first step was to calculate profit / loss for each day , each SKU to determine the
authenticity of the owner , for that I used sales and purchase data to calculate profit/loss ,
profit % for each SKU day using formula :

𝑃𝑟𝑜𝑓𝑖𝑡 = 𝑆𝑎𝑙𝑒𝑠 − 𝑃𝑢𝑟𝑐ℎ𝑎𝑠𝑒

𝑃𝑟𝑜𝑓𝑖𝑡𝑆𝐾𝑈 % = (𝑝𝑟𝑜𝑓𝑖𝑡 𝑆𝐾𝑈 / 𝑇. 𝑝𝑟𝑜𝑓𝑖𝑡) %
 ● Considering the fact that there was some inventory for every SKU at the start and end of
data collection purchase of each SKU was calculated using formula :

𝑃𝑢𝑟𝑐ℎ𝑎𝑠𝑒 = 𝑇. 𝑃𝑢𝑟𝑐ℎ𝑎𝑠𝑒 + 𝐼. 𝐼𝑛𝑣𝑒𝑛𝑡𝑜𝑟𝑦 − 𝐸. 𝐼𝑛𝑣𝑒𝑛𝑡𝑜𝑟𝑦

Where I.Inventory = Initial inventory ,
E.Inventory = End inventory ,
T.Purchase = Total purchase

● Lastly cumulative profit was calculated for pareto chart

2.3 : Optimizing inventory

● Upon discussion with the owner we got to know that inventory management was the
main concern for the owner as according to owner stock was piled up at the end of the
month and also profit were less hence buying new stock was getting difficult due to
inflation
● Inventory data is collected for every SKU at the first day of data collection (i.e. 1/4/23) .
Further Inventory data is calculated using sales , purchase and initial inventory using
formula :

𝐼𝑛𝑣𝑒𝑛𝑡𝑜𝑟𝑦𝑖 = 𝐼𝑛𝑖𝑡𝑖𝑎𝑙 𝐼𝑛𝑣𝑒𝑛𝑡𝑜𝑟𝑦𝑖 − 𝑆𝑎𝑙𝑒𝑠𝑖 + 𝑃𝑢𝑟𝑐ℎ𝑎𝑠𝑒𝑖

 ● Using inventory data average inventory , total inventory is calculated for every day as
well as every SKU

𝑇𝑜𝑡𝑎𝑙 𝐼𝑛𝑣𝑒𝑛𝑡𝑜𝑟𝑦 = ∑ 𝐼𝑖​

𝑖=0
𝑡ℎ
where 𝐼𝑖 = Inventory at 𝑖 day

2.4 : Fixed Cost Analysis

● For fixed Cost analysis cost Transport , Rent , furniture , Electricity , Accessories , Loan
were calculated for a period of 31 days (according to data) along with depreciation rate
(approx as per area)
● According to data Total fixed cost was calculated
3) Results and Findings :

3.1 : Volume Analysis (Sales , Purchase)

The below graph is generated for the Revenue (Sales) generated for the month.

● The above analysis shows that start and end days for the month are the highest revenue
generating days with early days of month end being the most important time (i.e. 22th -
28th) (referring this period to golden period)
● While it can also be seen that in middle of month the business is struggling to generate
revenue
● The analysis above shows the average daily revenue stands at ₹23,753 with a standard
deviation being ₹16557.77 which is quite high for such a low average indicating high
revenue fluctuation
● Minimum revenue stand at : ₹8,093
Maximum revenue stand at : ₹63,028
 Giving us a range of : ₹54,935 (using formula : 𝑅𝑎𝑛𝑔𝑒 = 𝑀𝑎𝑥 − 𝑀𝑖𝑛)
To analyze revenue generated by each SKU , the below graphs are generated 1) for the revenue
generated by each SKU over the period of a month and 2) pareto chart in respect to the Total
revenue generated over the period of a month

● Above analysis depicts that Rice , Milk & Dairy , Dry Fruits , Cooking Oil , Ghee and
Urad Dal are the main revenue generating SKU for the shop which can be seen on the
pareto chart can be seen as well as these 6 contributes approx 80% to the total revenue
generated of the shop
To analyze each SKU contribution along with pareto , the below graphs are generated to show
the proportion of each SKU contribution to Total revenue generated as well as Total sales volume
of the shop for a period of month

● From the above analysis it can be clearly deduced that Sales and revenue proportion for
each SKU are directly related to each other meaning there is no high revenue generating
product at low sales volume for the shop except
● Cooking oil and Ghee which has a contribution of 6.2% and 6% respectively to the sales
volume proportion while having a contribution margin of 11.4% and 11.3% to the total
revenue indicating high revenue generation at low volume in comparison to other SKU’s
● It can also be seen that the major 6 SKUs from the pareto chart for revenue have a high
sales volume proportion and revenue proportion making our pareto chart results valid

After Sales , to analyze the fluctuation or trend in purchase price , the below graph is plotted for
the purchase price of each SKU over the period of month
 ● The above analysis shows that there tends to be a slight increase in purchase price for
most of the SKU’s (except Cooking oil) at the middle of the month to the end of the
month which happens to coincide with the highest revenue generating period for the shop
(i.e. 19th to 27th (golden period))
● On the other hand Cooking oil purchase price tends to show a dip on the same period of
time (golden time period) making it the best time to buy and generate profit from cooking
oil
● And Urad Dal purchase price tends to show a significant increase of ₹10 - ₹12 on the
same period of time (golden time period) making it a early investing item (i.e. should be
stocked earlier to make huge profit)

Based on above analysis below graph are plotted for Cooking oil and Urad Dal purchase price
(Cost Price) and purchase to analyze the buying decision made by the owner
 ● From above analysis it can be seen that shop owner made two wrong purchases , which
were when prices were high , and the seller have earned at least extra 10-15 Rs/Kg, if it
would have been brought 2-3 days later or earlier in respective case

3.2 : Profit / loss Analysis

The below graph shows the comparison of avg purchase price to the average selling price for
each item present in the shop which can be further used to calculate avg profit per item to
analyze the areas / SKU’s which can be improved to increase net profit

● From the above graph it can be seen that Dry fruits and Ghee are the items that holds
maximum profit generating capacity while comparing this results to the revenue and
revenue volume proportion pie chart it can be clearly seen that Dry Fruits and Ghee sales
volume (1.1% and 2.1%) need to be improved for the shop to increase its net profit

Further to analyze , below graph is plotted for , each SKU contribution to the profit in
comparison to the contribution in revenue

● The above analysis validated our previous analysis about Dry fruits
 ● It also concludes that although the shop is prosperous in Milk & Dairy , Cooking oil sales
but their contribution to the overall profit remains significantly lower to that of others
hence lower net profit which can be due to the fact of wrong purchase decision (shown
above)

To validate our above finding , the below pareto chart for total profit is plotted

● From the above graph we can see that Rice , Dry Fruits , Ghee , Urad Dal , Cooking OIl
and Atta to be the 6 SKU contributing to the 80% of shop total profit
● Secondly ,from the above graph, we do validate our above analysis about Milk & Dairy
products (as they are not in 80%)

Furthermore , The below is generated to analyze the gross profit/loss over the period of month
 ● From the above graph our analysis about the golden period for the shop (i.e. 22th - 28th)
validates as well as it can be concluded that the wrong decision made by the owner in
purchase of Cooking oil and Urad Dal (shown above) caused very high loss for the shop
resulting in lower net profit

3.3: InventoryAnalysis
To start with inventory analysis , below graph represents the average stock in bags/carton for
every item

● Comparing the above avg stock graph with avg sales of the each item in exception to
Milk & Dairy we can see that there are no abnormalities

As per owner claim of inefficient inventory management , the below graph is plotted to analyze
inventory fluctuation over the period of month
 ● From the above graph it can be clearly seen that although there are no abnormalities in
avg stock as per sales but there are high fluctuation / variance in stock present in the
inventory for every SKU around the golden period (i.e. 22th - 28th) when sales are high
which clearly indicated poor planning and validates owner claim
● Secondly it can be observed that shop tend to refill it’s stock in relation to demand or
when stock is limited in inventory which can be referred to as a good practice but is
backfiring in this particular case (given all months follow the same trend.)

3.4: PL & INSIGHTS

Further to analyze the net profit firstly fixed cost is calculated using the data given by the owner

● The above table provides us with fixed cost analysis along with depreciation rate (approx
given by the owner)
● Items in above table can be majorly divided into two segments :- fixed assets and
monthly expenses
● Majority of the loan amount was used by the seller to purchase fixed assets. Also as it can
be seen that the rate of depreciation for them is very low (1-2%), which is an indication
of a good investment
● The amortized monthly cost of the fixed assets if only ₹56,000 , is very low compared to
the returns he gets from them indicating good investment
● From the above table following things can also be calculated :
○ Total fixed cost : ₹421,000
○ Total Normalized Fixed cost : ₹36,600 , which can be used to calculate net profit
using formula :
(𝑁𝑒𝑡 𝑝𝑟𝑜𝑓𝑖𝑡 = 𝐺𝑟𝑜𝑠𝑠 𝑃𝑟𝑜𝑓𝑖𝑡 − 𝑇𝑜𝑡𝑎𝑙 𝑁𝑜𝑟𝑚𝑎𝑙𝑖𝑠𝑒𝑑 𝐹𝑖𝑥𝑒𝑑 𝐶𝑜𝑠𝑡)
○ Net profit : ₹41,500
 Further the below graph is plotted to analyze fixed cost proportion

● From the above graph it can be seen that Electricity and Containers are the main
contributors to the fixed cost but due to them being a necessity for shop there is not much
reduction to do.

4) Interpretation of Results and Recommendation

4.1 : Recommendation 1: Increase the sales of high profit item (such as :- Dry Fruits & Ghee)
Based on the analysis, it is evident that Dry Fruits and Ghee have significantly higher profit
margins compared to other items. The shop owner can take advantage of this by increasing the
sales of these items, resulting in higher profits and additional assets for the shop.

Steps which can be taken to increase the sales of high profit margin items :-

● Seasonal Promotion: Dry Fruits are commonly consumed during winter. The shop can
host a sale on dry fruits during this season to capitalize on increased demand and boost
sales.

● B2B Platforms and Business Partnerships: Dry Fruits and Ghee are essential items for
other businesses as well. The shop can leverage this by participating in B2B platforms or
approaching other businesses to sell these items at a lower rate than what is offered to
customers. While this may reduce the profit margin per unit, it can significantly increase
overall sales and ultimately lead to higher net profits for the shop.

● Bulk Sales and Discounts: Instead of selling small quantities of dry fruits, the shop owner
can offer larger quantities of dry fruit packets at discounted prices to attract customers
and other business owners. This strategy can incentivize customers to buy in bulk and
increase sales volume.
4.2 : Recommendation 3 : Increasing Sales of Milk
In line with the first recommendation, it is important to address items like Dry Fruits, Milk, and
Dairy that may not be meeting sales expectations due to seasonal, time-specific, or
incentive-based factors. To optimize inventory further and increase sales, the following strategies
can be implemented:

● Morning-focused Milk Sales: Based on discussions with the owner, it was found that
98% of milk sales occur in the morning. To enhance inventory management and increase
sales, the shop can prioritize stocking milk specifically for morning demand.
Additionally, the shop can encourage customers to purchase other goods along with milk
or offer house delivery of milk at no additional cost. This strategy can optimize fixed
assets, such as delivery vehicles, and increase overall sales. Successful startups like
SUPR DAILY have employed similar strategies to boost their sales.
● Introduce Delivery Charges: Once the initial phase of offering free house delivery of milk
is established, the shop owner can gradually introduce nominal charges for delivery. This
will not only help cover the cost of the service but also generate additional profits for the
shop.

4.3 : Recommendation 2 : Restock inventory earlier than usual or during high sales periods
The shop owner made poor decisions in purchasing cooking oil and Urad Dal, resulting in
significant losses for the shop. These decisions were primarily influenced by high demand and
low stock availability at that time. To avoid such situations and optimize inventory management,
the following recommendations can be implemented:

● Time-based Restocking: Instead of purchasing and selling products based solely on

demand, it is advisable to restock the inventory at fixed intervals. The items can be sorted
based on perishable and non-perishable goods, considering the shop's facilities and the
owner's preferences. This approach allows the shop owner to identify items that can be
purchased in advance, reducing the risk of making last-minute decisions.

● Timing Restock with High Sales Periods: Analyzing the data, it is observed that the
period from the 22nd to the 28th of each month exhibits high sales. Therefore, it is
recommended to restock inventory slightly earlier than the 22nd to avoid making
incorrect decisions due to inadequate stock. By aligning restocking with high sales
periods, the shop can ensure sufficient inventory availability without excessive load or
unnecessary risks.
4.4 : Recommendation 4 : Increase Current Ratio
The analysis reveals that the shop's current ratio (CR) is currently low, indicating potential
liquidity issues. The current ratio can be improved by either increasing current assets or
decreasing liabilities. (based on formula given below)
𝐶𝑢𝑟𝑟𝑒𝑛𝑡 𝐴𝑠𝑠𝑒𝑡𝑠 = 𝐼𝑛𝑣𝑒𝑛𝑡𝑜𝑟𝑦 (𝑠𝑡𝑜𝑐𝑘) + 𝐶𝑎𝑠ℎ = ₹28,525
𝐶𝑢𝑟𝑟𝑒𝑛𝑡 𝑅𝑎𝑡𝑖𝑜 = 𝐶𝑢𝑟𝑟𝑒𝑛𝑡 𝐴𝑠𝑠𝑒𝑡𝑠 / 𝐿𝑖𝑎𝑏𝑖𝑙𝑖𝑡𝑖𝑒𝑠 = 0.07
Considering the constraints of the scenario where household expenses rely on the majority of
profits and essential liabilities for running the business, reducing liabilities is not a feasible
option. Therefore, the focus should be on increasing sales to boost current assets and improve the
current ratio.
By implementing the aforementioned recommendations to increase the sales of high-profit items,
restocking inventory at optimal times, and targeting specific sales strategies for milk and other
items, the shop owner can enhance profitability, optimize inventory management, and improve
the current ratio, leading to a more financially stable and successful business.

4.5 : Recommendation 5 : Additional Steps

4.5.1 : Effective Pricing Strategies

Another aspect to consider in increasing profitability is implementing effective pricing
strategies. The shop owner can explore the following approaches:

● Bundle Pricing: Consider creating bundled offers where related products, such as dry
fruits and ghee, are sold together at a discounted price. This strategy encourages
customers to purchase multiple items and increases the overall value of each
transaction.
● Promotional Pricing: Periodically offer special promotions, such as discounts,
buy-one-get-one-free offers, or limited-time offers, to create a sense of urgency and
attract customers. These promotional pricing strategies can boost sales during specific
periods and generate excitement among customers.

4.5.2 : Enhance Store Display and Visual Merchandising

Following suggestions can taken in account to enhance the store visual merchandiser:

● Eye-Catching Displays: Arrange high-profit items, such as dry fruits and ghee, in
attractive displays near the entrance or high-traffic areas. Use creative and appealing
signage or decorations to draw attention and entice customers to explore those
sections.
● Sample Stations: Consider setting up sample stations where customers can taste or try
certain products, especially for dry fruits or other items that customers may be less
familiar with. This allows customers to experience the quality and flavor, increasing
their likelihood of making a purchase.

By implementing effective pricing strategies and enhancing store display and visual
merchandising, the shop can create a more enticing and customer-friendly environment, leading
to increased sales and profitability.
5) Conclusion :
In conclusion, the analysis of Bansal Kirana Store's sales and expenditure data has provided
valuable insights and recommendations for improving the shop's profitability and inventory
management.

The analysis revealed that certain items, such as Dry Fruits and Ghee, have high-profit margins
and represent significant opportunities for increasing sales. By implementing targeted marketing
strategies, such as seasonal promotions and partnerships with other businesses, the shop can tap
into the potential of these high-profit items. Additionally, offering bulk sales and discounts can
incentivize customers to purchase larger quantities, further boosting sales volume and overall
profitability.

The findings also highlighted the importance of optimizing inventory management. By

restocking inventory earlier during high sales periods and improving planning and forecasting,
the shop can ensure sufficient stock availability without excess inventory buildup or shortages.
This will help to maintain customer satisfaction, reduce carrying costs, and improve overall
operational efficiency.

Furthermore, the analysis emphasized the significance of making informed purchase decisions.
The shop owner should closely monitor price fluctuations and market trends to avoid purchasing
items at inflated prices, as observed with Cooking Oil and Urad Dal. By implementing a
proactive approach to purchasing, the shop can minimize losses and increase profitability.

The analysis of fixed costs identified areas of expenditure that require attention, such as
electricity and containers. The shop owner should continuously evaluate and optimize these costs
to improve cost efficiency and maximize profit margins.

In addition, the recommendations to enhance store display and utilize effective pricing strategies
can contribute to increased customer attraction and sales. Eye-catching displays, sample stations,
and promotional pricing can create a positive shopping experience and encourage customers to
make additional purchases, leading to higher revenue and profitability.

By implementing these recommendations, Bansal Kirana Store can improve its financial
performance, increase profitability, and establish a stronger position in the market. It is important
for the shop owner to continually monitor and evaluate the effectiveness of these strategies,
making necessary adjustments to ensure long-term growth and sustainability.

6) Important Links :

Spreadsheet : 21f1000089 BDM capstone

Presentation: 21f1000089 BDM capstone

END