Understanding the profitability problem of businesses due to poor inventory management

Brief Description: This milestone explores how sorting and filtering techniques in Excel can enhance data analysis by organizing information. The goal is to be able to uncover trends, identify outliers, and gain valuable insights from their datasets.

Methodology: Utilize sorting and filtering techniques in Excel to organize and analyze sales data, customer information, and financial statements. The analysis will be for business data.

Tasks:

1. Sort sales data by order date to analyze trends over time (Latest-Oldest).
2. Sort sales data by product category (A-Z)and sub-category(Z-A) to identify top-selling products.
3. Sort sales data by profit to prioritize high-profit orders.
4. Custom sort the data by region in this order North, South, East, West, and Central
5. Sort the data in this order – customer name (A-Z), city (Z-A), order date (2018, 2015, 2017, 2016), sales (highest – lowest)
6. Filter sales data by product category to analyze the performance of Bakery products.
7. Filter customer data by geographic region to analyze Norther region customer preferences.
8. Use filters to filter sales data by discount amounts of more than 1000 to analyze the impact of discounts on sales.
9. Filter out the data for April 2018.
10. Filter customer data to identify unique customers.
11. Identify outliers in sales data by sorting sales values in descending order.

Note: Submit the Excel sheet and Word file for the same

Brief Description: This milestone explores how conditional formatting in Excel can enhance data visualization by applying formatting rules based on specified conditions. By understanding the principles of conditional formatting and its application, users can transform raw data into visually appealing and informative representations.

Methodology: Utilize conditional formatting in Excel to visually highlight important insights in sales data, financial statements, and operational metrics. Also, use visualization for examination. The analysis will be for business and finance data

Tasks:

1. Apply conditional formatting to highlight top-performing products based on sales revenue i.e. sales is more than 2400.
2. Use conditional formatting to colour-code orders with top 10% profit.
3. Highlight cells with negative values in financial statements using conditional formatting.
4. Apply data bars to visualize sales trends over time (oldest to latest).
5. Use icon sets to identify outliers in sales data.
6. Create a traffic light system to indicate sales level based on profit thresholds i.e. below 500, 500-1000 and above 1000.
7. Highlight cells wherein the discount is between 0.30 to 0.35.
8. Apply conditional formatting to identify losses in financial statements.
9. Create a trendline to visualize sales trends
10. Create a plot to analyze the impact of discounts on sales
11. Generate a chart to visualize the distribution of sales across different product categories
12. Use a bar chart to visualize profitability across different subcategories
13. Generate an area chart to analyze sales concentration over time
14. Analyze the distribution of sales amounts with bins of 200 each
15. Create a plot to identify outliers within the profit data
16. Create line sparklines to depict the sales trends for “Ridhesh” customers over time
17. Sum each region's sales and then draw the column sparkline to show each region's sales performance
18. Use win/loss sparklines to analyze the profitability of the company

By incorporating these tasks, the milestone provides a step-by-step guide on leveraging conditional formatting and data visualization techniques in Excel to transform raw sales data into visually compelling representations, enabling users to gain deeper insights and make informed decisions.

Note: Submit the Excel sheet and Word file for the same

Brief Description: This milestone explores how conditional statements and logical operators in Excel can drive decision-making by automating data analysis and applying logical criteria to datasets.

Methodology: Utilize conditional statements and logical operators in Excel. The analysis will be for business and finance data

Tasks:

1. Categorize sales orders as profitable or unprofitable based on profit amount i.e. if more than 800 then profitable else not.
2. Categorize sales orders 4 categories i.e. Low sales (below 700), medium sales (700-1400), good sales (1400-2100), Excellent sales (more than 2100)
3. Categorize orders such that if sales are more than 1000 and profit is more than 1000 as profitable else not profitable.
4. Categorize orders as profitable if sales are more than 2300, discount less than 0.15 or profit more than 1000 else not.
5. The company wants to change the discount for the customers by the criteria that if the sales are more than 2000 then 10% discount if sales are between 1500-2000 then 5%, else no discount. Create a new column for recording the new discount amount for each order.
6. Assign performance ratings based on the criteria that if sales are more than 2000 and profit is more than 1000 then 5, sales are more than 2000 but profit is more than 800 then 4, if sales are more than 1500 and profit is more than 500 then 3, if sales more than 1000 and profit more than 200 then 2, and for all remaining conditions, it's 1.
7. Categorize the transactions such that if net profit/loss is less than -500 cr. then the company need serious consideration else no major change is required

Note: Submit the Excel sheet and Word file for the same

Brief Description: This milestone dives into the application of statistical functions within Excel to extract valuable insights from the provided dataset. 

Methodology: Utilize statistical functions in Excel to analyze the provided dataset, extract insights, and inform decision-making. The milestone uses business data

Tasks:

1. Counting Orders
2. Counting Customers.
3. Counting order no. for the North region
4. Counting order no. for the west region when the city is Bodi
5. Computing sales for Tamil Nadu State
6. Calculating Average Sales
7. Calculating average sales when the customer is adavan and the sub-category is cookies
8. Determining Maximum Profit
9. Determining the middle value of sales and the most frequently occurring subcategory
10. Measure the variability in profit
11. Measure the correlation between sales and profit columns, identifying any relationship between the two variables.
12. Identify the lowest sales amount recorded in a central region
13. Determine quartile values of sales data
14. Identify the sales value at the 90% percentile
15. Round sales amount down to 5 multiple
16. Round sales amount up to 2 multiple
17. Round sales amounts to the 1 digit, round up to 0.5 digit, round down to 0.3 digit
18. Round sales to the nearest integer, even integer and odd integer
19. Identify the 56^th largest value and 20^th smallest value of profit
20. Rank profit values in the dataset
21. Generate random sales values from 2300 to 2500
22. Calculate the weighted average of sales values for Malik customers with all 2015 sales value weights to be 0.3, 2016 be 0.4, 2017 be 0.5 and 2018 be 0.6.
23. Calculate potential sales value raised to 0.1 power
24. Implement the SUBTOTAL function to perform different aggregate functions (e.g., SUM, AVERAGE)

Note: Submit the Excel sheet and Word file for the same

Brief Description: This milestone delves into the comprehensive utilization of date and text functions in Excel to manipulate, analyze, and extract insights from the provided dataset. 

Methodology: Utilize a combination of date and text functions in Excel to manipulate and analyze the provided dataset, extracting valuable insights for decision-making. The milestone uses business data.

Tasks:

1. To determine the day of the week for each order date, aiding in understanding weekly sales patterns.
2. To individually derive the day, month, and year based on each order date
3. To calculate the number of days between the order date and today of each customer
4. To locate specific text “da” within customer names
5. To convert all the text of the city in UPPER case and then derive the proper case of the column
6. To convert text strings in the Customer Name column to uppercase
7. To convert text strings in the Category column to lowercase
8. To extract the month component from order dates
9. to combine text strings from the Customer Name and City columns, creating a unique identifier for each customer-location pair.
10. to extract substrings from text strings in the Subcategory column
11. to replace “a” text within product names with “A”
12. To calculate the length of text strings in the Customer Name column
13. to remove leading and trailing spaces from text strings in the Category column
14. to identify orders placed on Saturdays and Sundays, enabling analysis of weekend sales patterns.

Note: Submit the Excel sheet and Word file for the same

Brief Description: This milestone focuses on mastering various lookup functions in Excel to efficiently retrieve and analyze data from the provided dataset.

Methodology: Implement each lookup function in Excel to extract and analyze data from the provided dataset, enabling informed decision-making. The milestone uses business data.

Tasks:

1. Retrieve sales, customer, profit and discount data for Order IDs - OD3244, OD1300, OD2837, OD7338, and OD1413
2. To identify the position of sales amounting to 2147, discount 0.32, and order ID OD4966
3. To extract data of customer, city, state, discount, and category for sales amounting – 2147, 1736, 2058, 2118, and 2270
4. To derive values of all columns for customer Aditi
5. To categorize the sales orders into 4 categories i.e. Low sales (below 700), medium sales (700-1400), good sales (1400-2100), and Excellent sales (more than 2100) using lookup functions

Note: Submit the Excel sheet and Word file for the same

Brief Description: This milestone delves into advanced lookup techniques in Excel, tailored to the provided dataset.

Methodology: Implement advanced lookup techniques in Excel to analyze and extract insights from the provided dataset, enabling informed decision-making. The milestone uses business data.

Tasks:

1. To retrieve data of order date, region, and city, for-profit amounting to 760.32, 567.24, and 468.26
2. to extract Sales data for the customer having “av” in their name.
3. To retrieve Sales data for a Cakes and North region.
4. To search all sales data for Mathew
5. To retrieve discount data for a Vidya customer within Ooty City

Note: Submit the Excel sheet and Word file for the same

Background: Pivot Tables and Charts stand out as indispensable tools for gaining insights, identifying trends, and presenting data in a visually appealing manner. This milestone aims to delve into the application of Pivot Tables and Charts in analyzing the Bigbasket business status.

Methodology: The methodology involves leveraging Pivot Tables to aggregate and summarize data based on various dimensions such as Category, Region, or Date. The data used is business data.

Tasks:

1. Generate a Pivot Table to summarize Sales data based on Category and Region, providing insights into regional sales performance.
2. Utilize a Pivot Table to calculate and analyze Profit by sub-category, identifying high-profit areas.
3. Create a Pivot Chart to visualize Sales trends over time, highlighting fluctuations and identifying patterns.
4. Use a Pivot Table to compare Discounts offered across different Regions, identifying regions with the highest and lowest discounts.
5. Apply filters in the Pivot Table to analyze Sales by City within the West region
6. Generate a Pivot Chart to visually represent the distribution of Sales across various Categories, aiding in product performance analysis.
7. Use a Pivot Table to analyze Profitability by Customer Name or Customer Segment, identifying key customers contributing to overall profitability.
8. Generate a Pivot Table to compare Sales performance across different States, identifying regions with the highest and lowest sales volumes.
9. Add slicers to the pivot table generated in the 8^th step based on the customer name.

Note: Submit the Excel sheet and Word file for the same

Background: Dashboards serve as powerful tools for synthesizing complex data into actionable insights, enabling stakeholders to monitor key performance indicators (KPIs) and make informed decisions. This milestone focuses on leveraging the provided dataset to create a dynamic dashboard in Excel, showcasing essential metrics such as Sales trends, top-selling products, profitable regions, popular sub-categories, and cities with the highest discounts. 

Methodology: The methodology involves aggregating and summarizing data using Pivot Tables and then visualizing the insights through interactive charts and graphs. Excel's features such as slicers and dynamic ranges will be utilized to enhance dashboard interactivity and flexibility. The data used for analysis is business data.

Tasks:

1. Create a dynamic smoothened line chart to visualize Sales trends over time, allowing users to track performance fluctuations and identify seasonal patterns.
2. Generate a pivot table and respective chart to showcase the top 10 sold products by Sales volume, enabling stakeholders to prioritize product offerings.
3. Utilize a pivot table and chart to display the contribution of different regions to overall Profit, highlighting regions with the highest profitability.
4. Create a pivot table and chart to highlight the top 4 sub-categories based on Sales revenue, facilitating product category analysis.
5. Generate a pivot table and chart to identify cities receiving the highest discounts, enabling analysis of discount distribution.
6. Incorporate interactive slicers for filtering data based on specific criteria such as Region, Category, or Date, enhancing dashboard interactivity.
7. Design a dynamic dashboard layout

Note: Submit the Excel sheet and Word file for the same

Background: In today's competitive business landscape, the ability to effectively organize and analyze financial data is crucial for making informed decisions and driving business growth. Financial statements, including the Income Statement, Cash Flow Statement, and Balance Sheet, provide valuable insights into a company's financial health and performance. 

Method: This milestone demonstrates a systematic approach to organizing Big Basket's financial statements in Excel and conducting comprehensive financial analysis. The dataset used here is financial data.

Tasks:

1. Organize data in a structured manner with the usage of basic Excel functions following standard accounting principles.
2. Analyze Big Basket's profitability ratios such as Net Profit Margin, gross profit margin, return on equity and Return on Assets.
3. Identify activity ratios such as inventory turnover ratio, account receivable turnover, asset turnover
4. Evaluate Big Basket's liquidity and solvency ratio such as current ratio, quick ratio, debt to equity ratio, equity ratio, and debt ratio.
5. Use line graphs for depicting revenue growth, bar charts for comparing expenses, and pie charts for illustrating expense composition.

Note: Submit the Excel sheet and Word file for the same

Background: The summary report aims to provide a comprehensive overview of Big Basket's current business status based on the findings derived from the preceding milestones. Through Excel-based market and financial analysis methods, we have delved into various aspects of Big Basket's operations, financial performance, and market positioning. This article summarizes the information derived with the utilization of Excel-based market and financial analysis methods by stating the understanding of the current business status of Bigbasket.

Methodology: This article reports the findings derived from all the above milestones. About 5-6 articles will also be referred from external sources to validate the information derived about the inventory problem and business status of Bigbasket. The article should be written as a 1000-Word report. The structure of writing the article is described here

1. Introduction – state the background of BigBasket and its profitability problem due to inventory
2. Financial analysis – discuss the financial information like financial ratios and performance
3. Market analysis – state the mostly sold products, market segmentation, and other details
4. Insights and recommendation – state the actionable insights for improving business performance and how the problem can be solved by a detailed assessment of financial and business

Requirements: All the previous milestones need to be completed and the writing should be done in a clear and concise format for communicating the problem and case without complicating the information.

Note: - Do not copy and paste from any source. The findings should be the insights you have derived from your analysis in milestones 1 to 10. Submit the Word file for the same

Background: This milestone aims to conduct a comprehensive financial analysis for Bigbasket, addressing the inventory management challenges and enhancing profitability. The objective is to evaluate investment options and strategies to optimize inventory management processes, reduce wastage, and increase profitability. 

Methodology: This milestone aims to utilize financial functions to analyze Big Basket's financial data and evaluate potential investment opportunities for resolving the inventory management problem and deriving better profitability. Perform calculations for present value (PV), future value (FV), net present value (NPV), internal rate of return (IRR), periodic payments (PMT), and other financial metrics. This milestone uses investment data for deciding the appropriate investment strategy for the company.

Tasks:

1. Compute the present value of expected benefits for each investment option.
2. Estimate the future value of investments over the specified period.
3. Calculate the net present value of cash flows for each investment option.
4. Calculate the internal rate of return (IRR) for each investment option using the IRR function in Excel.
5. Determine the periodic payments required for loan repayment, considering loan details and interest rates.
6. Calculate the interest portion of loan payments for each period.
7. Compute the principal portion of loan payments for each period.
8. Calculate the interest rate for a loan or investment, given periodic payments and the total number of periods.
9. Determine the number of periods required to repay a loan or reach a financial goal, given periodic payments and interest rates.
10. Identify the maximum and minimum expected benefit

These tasks encompass the application of specific financial functions in Excel to conduct a comprehensive analysis of investment options and provide actionable insights and recommendations for enhancing inventory management and increasing profitability at Bigbasket.

Note: Submit the Excel sheet and Word file for the same

Background: The What-If Analysis milestone is crucial for Bigbasket to evaluate potential scenarios impacting its profitability, leveraging both business and finance datasets. By exploring various hypothetical situations and their financial ramifications, this analysis aims to provide insights into how changes in key parameters may affect the company's financial performance. This understanding will facilitate informed decision-making and strategic planning to optimize profitability and ensure sustainable growth.

Methodology:- In this milestone, we will use different scenarios each for Goal seeking, Sensitivity Analysis, and Scenario Analysis. Using Excel, Sensitivity analysis will be conducted to evaluate how changes in key parameters affect profitability across different scenarios. Additionally, Excel's Goal Seek function will be utilized to determine the required adjustments in specific parameters to achieve desired profitability targets. Scenario analysis will involve exploring various hypothetical situations and their potential effects on financial performance, considering factors such as market conditions, consumer behaviour, and operational strategies. The dataset considered for this is finance data and the majority of data for the year 2023.  

Tasks:

1. Goal Seek Scenarios:
   1. Scenario 1: Determine the required increase in sales volume to achieve a 10% growth in net profit in 2023
   2. Scenario 2: Find the optimal advertising promotional expenses needed to maintain a target net profit margin of 20% in 2023
   3. Scenario 3: Identify the maximum allowable expenses for earning a profit of Rs. 10 crore in 2023.
   4. Scenario 4: State the change in inventory for deriving the inventory turnover ratio value as 0.8 for 2023
   5. Scenario 5: Determine the change in operating activities for 2023 to derive net cash flow as 0.
2. Sensitivity Analysis Scenarios:
   1. Scenario 1: Assess the sensitivity of net profit to changes in sales volume resulting from improved inventory management practices.
   2. Scenario 2: Evaluate the impact of varying promotional expenses on net profit
   3. Scenario 3: Analyze the sensitivity of ROI to fluctuations in revenues.
   4. Scenario 4: Assess the effect of changes in net cash flow due to changes in operating activities
   5. Scenario 5: Evaluate the sensitivity of profit margins to changes in logistics cost
3. Scenario Analysis Scenarios:
   1. Scenario 1: Explore the impact of introducing a new product line on overall profitability
   2. Scenario 2: Assess the effects of changes in consumer preferences or purchasing behaviour on sales
   3. Scenario 3: Analyze the potential cost savings from subcontractor impact on the profit
   4. Scenario 4: Evaluate the impact of changes in cash flow from investing activities on net cash flow
   5. Scenario 5: Assess the effects of change in inventories on the inventory turnover ratio.

This milestone will provide valuable insights into the potential effects of different scenarios on Bigbasket's profitability, enabling informed decision-making and strategic planning to enhance overall business performance.

Note: Submit the Excel sheet and Word file for the same

Background: Descriptive analysis plays a vital role in uncovering insights from data, allowing Bigbasket to understand the underlying patterns and trends related to inventory management and profitability. In this milestone, we will conduct ANOVA (Analysis of Variance), T-Test, and Histogram analysis using both business and finance datasets. These statistical techniques will help identify significant differences between groups, and assess the distribution of key variables impacting profitability.

Methodology: Using Excel, we will perform ANOVA to compare means across multiple groups to determine if there are significant differences in sales performance or profitability. T-tests will be conducted to assess whether there are statistically significant differences in means between the two groups. Additionally, Histogram analysis will be used to visualize the distribution of key variables. Herein, 2 datasets will be used i.e. business data and survey data.

Tasks:

• ·         To understand the basic demographics and perception of consumers
• ·         To determine how the demographics of customers affect the satisfaction level of consumer  
• ·         To evaluate the difference in satisfaction level as per the preference of the consumer, their identification of factors and willingness to pay annual membership fees.
• ·         To compare the satisfaction score of customers before and after implementing the Har din Sasta campaign
• ·         To visualize the distribution of sales volumes and profit to identify any patterns or outliers that may influence inventory management decisions.
• ·         To understand the shape and variability of the data, providing insights into the stability of inventory management processes.

By conducting descriptive analysis using ANOVA, T-Test, and Histogram techniques, Bigbasket will gain valuable insights into the underlying factors influencing inventory management effectiveness and profitability. These analyses will enable the company to identify areas for improvement, refine inventory strategies, and enhance overall business performance.

Note: Submit the Excel sheet and Word file for the same

Background: Correlation and regression analysis are powerful techniques for exploring relationships between variables and making predictions based on observed data. By analyzing correlations and regression coefficients, insights can be gained into the factors influencing business performance. This milestone aims to conduct correlation and regression analysis on Big Basket's business data to identify key relationships and drivers of profitability, contributing to the goal of designing a tailored strategy for improvement.

Methodology: The methodology for this milestone involves conducting correlation and regression analyses to examine the relationships between variables related to customer purchases on the Bigbasket app. Firstly, correlation analysis will be performed to assess the strength and direction of associations. Secondly, multiple linear regression will be employed to assess the impact. Herein, survey data will be used for analysis.

Tasks:

1. To determine the efficiency of the questionnaire
2. To identify the linkage between the identified factors and consumer purchasing behavior
3. To model the relationship between consumer purchasing behaviour and identified factors

Note: Submit the Excel sheet and Word file for the same

Background: The objective of this milestone is not only to compile and report the findings from the previous six milestones but also to provide comprehensive answers to the overarching question of Goal 2: "To design a tailored strategy for improving the profitability of the company through impact analysis, projections, and financial analysis." This milestone aims to synthesize the insights gained and propose actionable strategies that align with the company's objective of enhancing profitability.

Methodology: The methodology involves a review of the findings and recommendations derived from each of the previous six milestones. The methodology also encompasses validating the proposed strategies against the company's goals and assessing their feasibility and potential impact. About 5-6 articles will be referred from external sources to validate the information derived. The article should be written as a 1000-word report. The structure of writing the article is described here

1. Introduction - Overview of the project's objective and the importance of designing a tailored strategy for improving profitability.
2. Summary of Milestones and strategic recommendation- Recap of the key findings and insights from each milestone, focusing on their relevance to profitability enhancement.
3. Implementation Plan - Detailed roadmap for implementing the proposed strategies, including timelines, resource allocation, and responsible stakeholders.

Requirements: All the previous milestones need to be completed and the writing should be done in a clear and concise format for communicating the problem and case without complicating the information.

Note: - Do not copy and paste from any source. The findings should be the insights you have derived from your analysis in milestones 1 to 6 under goal 2. Submit the Word file for the same

Background: Understanding historical sales trends and projecting future financial performance is essential for designing strategies to improve profitability. By analyzing trends in sales data and projecting future financial outcomes, insights can be gained to inform decision-making and optimize business operations. This milestone aims to identify patterns in Big Basket's sales data and forecast future financial performance, contributing to the goal of designing a tailored strategy for improving profitability.

Methodology: Utilizing Excel's TREND function, historical sales data will be analyzed to identify trends and patterns. Financial projections will be made based on these trends, incorporating factors such as seasonality, market dynamics, and business initiatives. The projection model will consider various scenarios and assumptions to forecast future sales and profitability. The data considered for analysis would be business and finance data

Tasks:

1. Based on the given data, compute the total sales for each order date. Based on retrieved data, forecast the sales for the next 1 year.
2. Analyze the trend line to identify patterns and fluctuations in sales.
3. Also, project the net income for the next 2 years based on the given net income
4. Project all the financials for Big Basket for 2024 considering 10% profit compared to the average of the past 4 years (gross profit, net profit, total non-current assets, total current assets, equity share capital and net cash flow)
5. Project financial for 2025 with the aspect that 40% profit compared to an average of past 5 years (gross profit, net profit, total non-current assets, total current assets, equity share capital and net cash flow)

Note: Submit the Excel sheet and Word file for the same

Background: Effective inventory management is critical for Bigbasket's success as a leading online grocery retailer. With a vast array of products and fluctuating consumer demand, optimizing inventory levels is paramount to ensuring customer satisfaction, minimizing costs, and maximizing profitability. Linear programming offers a powerful solution by enabling Bigbasket to mathematically model its inventory decisions, considering various constraints and objectives. This milestone aims to leverage linear programming techniques to design an optimized inventory strategy that balances supply and demand dynamics, maximizes profitability, and enhances operational efficiency.

Methodology: This milestone employs linear programming techniques within Excel to construct a sophisticated model for inventory optimization. Decision variables are defined to represent product quantities, while an objective function is formulated to maximize profit. Constraints such as budget limitations and demand forecasts are incorporated to ensure feasibility. Herein, using Excel Solver the analysis would be done. The weekly inventory data will be used for assessment.

Tasks:

1. Define decision variables for product quantities.
2. Formulate an objective function to maximize profit.
3. Specify constraints including demand constraint, inventory constraint, and non-negativity constraint
4. Utilize Solver to find the optimal solution.

Note: Submit the Excel sheet and Word file for the same