Finance & Supply Chain Analytics

A robust teaching-learning platform dedicated for you to gain the industrial skills
and knowledge by, making your learning journey more valuable.
Introduction :
AtliQ Technology, established in 2018, operates in the hardware
manufacturing sector, specializing in electronic gadgets like
mice, keyboards, laptops, printers, etc.
The company distributes its products to a diverse range of
customers, including both both Brick&Motar and Online stores.
These intermediary customers subsequently sell the products to
end-users.
The business model structure is illustrated in the image below.
Note :
You should not confuse consumer as a customer here.
Here customer means the direct customer of AtliQ.
The real customer in business model is Brick & Motar stores
and online stores.
Consumer means the end user who consumes the product.

AtliQ have a manufacturing facility where they build all this

hardware and then they send it to a warehouse, distribution
centers and so on they have business in different countries, so
they ship or supply these hardware gadgets to different
customers across different countries.
AtliQ diversifies its sales channels, employing both brick-and-
mortar stores and online E-commerce marketplaces to get their
products into customers’ hands.

In terms of customer channels, they utilize 3 types of Channels

in selling their products to consumers :
1. Retailers :- This includes partnerships with both traditional
brick-and- mortar stores and online marketplaces.
2. Direct Sales :- They also operate their own online stores
(AtliQ Direct E-Stores) and physical exclusive showrooms
(AtliQ Exclusive showrooms).
3. Distributors :- They leverage a network of distributors to
distribute product to local stores across the country.
Next up: AtliQ Hardware’s financial breakdown.
Let’s figure out how AtliQ Hardware makes money!

Here’s an illustrative example :-

AtliQ sells a mouse to Croma for $30 gross price.
That is the base price that AtliQ has set for this particular Mouse.

Now there is some amount that needs to be deducted from this

30$ because AtliQ sets a base gross price of $30 for a mouse
sold to Croma.
This is the full price, but Croma gets a special discount at the
start of each year based on thier longstanding business
relationship and the sales volume generated by the croma store
for AtliQ.
This pre-negotiated annual discount made at the beginning of
each year is called as Pre-Invoice Deductions.
Let’s assume the agreed discount for this mouse is $2, although
it’s typically expressed as a percentage.
Therefore, when AtliQ sells this mouse, Croma pays $28 instead
of $30 after the deduction.
This $28 is nothing but the Net Invoice Sales.
After this process, Croma provides promotional offers to
customers, facilitated by AtliQ Hardware.
For instance, during festive seasons like Christmas or Diwali,
AtliQ Hardware might suggest a 5% discount for the entire
month as a promotional offer that Croma can extend to its
consumers.
So that is promotional offer.
Discounts aren’t the only way AtliQ pushes their products.
AtliQ wants their products to stand out, so they sometimes offer
stores like Croma this premium discount or a bonus, called
a “placement fees”, to put them at a prime location in the stores,
hoping that this will draw more attention and boost their sales.
This is a strategy to boost sales for AtliQ Hardware, especially
considering that Croma may also source hardware from other
manufacturers like Dell and HP.
It’s all about getting their products noticed and bought!
This is another way for them to compete with bigger brands and
get their products noticed.

Let’s say chroma is pretty good in selling these devices. They

generate lot of sales for AliQ Hardware.
So besides discounts like a base price discount, placement fees
for prime locations, AtliQ can offer or rewards croma with other
incentives or a bonus, called a “performance rebate” if the
store sells a lot of their products.
Basically, the more devices Croma sells, the more money they
get back from AtliQ at the end of the quarter or a month or a
year, which benefits both companies in the long run.
It’s like a way of saying “Thank you for being a great sales
partner! We appreciate your business!”
After the initial pre-invoice sales price is set, there are 3 types of
discounts AtliQ can use to further reduce the cost for stores like
Croma.
These 3 types of discounts that we discussed earlier come under
the Post-invoice Deductions.
Think of post-invoice deductions as adjustments made after the
initial sale of a product.

Post-invoice Deductions = festival sales discount or

promotional offer discount from the consumer (i.e shoppers)
+ Platform fee from croma (i.e AtliQ’s customer) +
Performance Rebates based on how well croma sell (i.e
AtliQ’s customer).

After AtliQ sells a mouse to Croma, there might be some savings

and adjustments to the final price.
We call these adjustments “Post-invoice deductions”.
All these “Post-invoice deductions” add up to more profit for
Croma on each AtliQ device they sell, further reducing the cost,
creating a flexible pricing system!
These adjustments can significantly lower the final cost for
croma stores and hence AtliQ easily builds loyalty with stores
like Croma through post-invoice adjustments.

So, AtliQ sold mouse to croma for 28$ before Post-invoice

deductions.
At the end of the selling period (month or quarter or Year), they
decide and come to agreement for Post-invoice
deductions which amount to $3.
We need to account for those Post-invoice
deductions (discounts, fees and rebates) to get the Net Sales.
To figure out Net Sales, we must deduct the Post-invoice
deduction amount from the initial invoiced amount (Net Invoice
Sales) paid by Croma.

Net Sales actually defines the price of the product after

considering all the deductions.
Net Sales = Gross Price — (Pre-invoice deduction + Post-
invoice deduction)
Net Sales = (Gross Price — Pre-invoice deduction) — Post-
invoice deduction
Net Sales = Net invoice Sales — Post-invoice deduction

So eventually at the end of the day AliQ is selling mouse to

croma for $25, which is the revenue of AtliQ Hardware
Technologies (Net Sales).
After we cut those extra discounts from the initial invoiced
amount (Net Invoice Sales) we get Net Sales amount.
We now need to deduct the cost of goods sold(COGS) which
includes the sum of :
1. Manufacturing Cost
2. Freight Cost (Transportation)
3. Other expenses & costs
from the Net Sales to get our gross profit.

This tells us how much money we actually earned from selling

the mouse to Croma.
Suppose the COGS amount for this mouse is $20.

After deducting the COGS amount from Net sales we get profit.
$20 COGS means AtliQ Hardware earns a profit of $5 per
mouse.
That’s $5 for each mouse.
This profit is also referred to as Gross Margin or Gross
Margin on Net Sales.
Gross Margin = Net Sales — Cost of Goods sold (COGS)
Gross Margin as % of Net Sales = Gross Margin / Net Sales

Hence Gross Margin comes out as 20% of Net Sales

That means AtliQ Hardware make 20% profit on every mouse
they sell ($5 profit divided by the $25 sale price), after
accounting for the cost of making and shipping them.

This is a quick summary of Profit and Loss statement.

This summary is about the business model of the AtliQ, now we
can move towards the tables.

AtliQ’s Technologies Database details :

Let’s explore the schema structure of AtliQ hardware to gain a
deeper understanding of AtliQ hardware.
An Entity Relationship Diagram (ERD) will visually showcase
the connections between different tables, making it easier to
grasp the data organization.
AtliQ Technology initiates its business/financial year in
September in the month of September by every year, aligning
with high sales and the festive season lasting from September to
December.
This fiscal year runs from September to August.
All analyses conducted will be based on that particular fiscal
year timeframe, ensuring consistency and relevance.
Task-1

Q1) Create a database named as

SupplyChainFinanceManagement

Q2) Create Nine tables which is given below along with there
Columns name in the given image
1. dim_customer (describe about the customer information who
have interacted with AtliQ hardware.
You can analyze customer demographics, buying behavior,
frequency of purchases)
2.dim_product (describe about the products offered by AtliQ
hardware.
You can analyze product popularity, sales trends, pricing
strategies, and product categories that perform well.)

3. fact_forecast_monthly (describe the AtliQ’s monthly

sales prediction information or forecast data like all the estimated
quantity of products that we are expecting in the coming future
so that we can manage the inventory properly)

4. fact_freight_cost (describe about the AtliQ’s transportation

information for all fiscal years and markets.)

5. fact_gross_price (describe the gross price for each unique

product that AtliQ sells for a given fiscal year.)
6. fact_manufacturing_cost (describe about AtliQ’s product
manufacturing information related to COGS amount [summation
of Manufacturing Cost, Freight Cost ( Transportation),Other
Cost] that needs to be deducted from net sales)

7.fact_post_invoice_deductions (describe about AtliQ’s post-

invoice-deduction information related to post invoice amount
that needs to be deducted for per product when we get the net
sales.)

8. fact_pre_invoice_deductions (describe about AtliQ’s pre-

invoice-deduction amount(which is basically the yearly discount
agreements made at the beginning of each financial year) that
needs to be deducted from the gross sales)

9. fact_sales_monthly (describe the AtliQ’s monthly sales

transaction information which aggregates sales data,
summarizing revenue generated from each transaction)
 Task-2

Q1) After creating the tables insert the given records which is in
the below image in respective tables:
 Task-3

Q1) Assume calendar_date is '2023-07-15'. Apply the function to

this date and explain what value it will return as the fiscal year.

Q2) Analyzing Gross Sales: Monthly Product Transactions

Report
Write a Query for making report on monthly product
transactions, including details such as date, product code,
product name, variant, sold quantity, gross price, and gross price
total. The query should involves joining several tables and
filtering results based on customer code and fiscal year.
 Task-4

Sales Trend Analysis:

Query the fact_monthly_sales table to identify the monthly sales
trend for each product. How do the sales volumes fluctuate over
time?

Customer Segmentation:
Utilizing the dim_customer table, segment customers based on
their purchasing behavior. Which customer segments contribute
the most to sales revenue?

Product Performance Comparison:

Compare the performance of products in terms of sales quantity
and revenue generated. Which products are the top performers,
and which ones need improvement?

Market Expansion Opportunities:

Analyze the fact_forecast_monthly table to identify potential
market expansion opportunities. Which markets show the highest
forecasted demand growth?

Cost Analysis:
Calculate the total manufacturing cost for each product and
compare it with the gross price to determine profitability. Which
products have the highest profit margins?

Discount Impact Analysis:

Assess the impact of pre-invoice discounts on sales revenue.
How do varying discount levels affect overall revenue and
customer retention?

Market-specific Freight Costs:

Determine the average freight costs for different markets over
the years. Are there any noticeable trends or outliers in freight
expenses?

Seasonal Sales Patterns:

Explore the fact_monthly_sales table to identify seasonal sales
patterns. How do sales volumes vary throughout the year, and
are there any recurring trends?

Customer Loyalty Analysis:

Analyze customer purchase frequency and retention rates over
time. Which customers exhibit the highest levels of loyalty, and
how can their behavior be leveraged for targeted marketing
campaigns?

Forecast Accuracy Evaluation:

Evaluate the accuracy of sales forecasts by comparing forecasted
quantities with actual sales data. Are there any significant
discrepancies, and how can forecast models be improved?

Channel Performance Assessment:

Compare sales performance across different sales channels (e.g.,
E-Commerce vs. Brick & Mortar). Which channels are most
effective in driving sales, and are there any opportunities for
optimization?
Geographical Sales Distribution:
Analyze sales distribution across different geographical regions.
How does sales performance vary by region, and are there any
emerging markets worth focusing on?

Customer Acquisition Cost Analysis:

Calculate the customer acquisition cost (CAC) for each market
and channel. Which acquisition channels provide the highest
return on investment (ROI), and where should resources be
allocated for customer acquisition?

Product Mix Optimization:

Determine the optimal product mix based on sales volume,
profitability, and market demand. How can product portfolios be
adjusted to maximize overall revenue?

Customer Lifetime Value Calculation:

Calculate the customer lifetime value (CLV) for each customer
segment. Which segments are the most valuable in terms of
long-term revenue generation?

Inventory Management Analysis:

Analyze inventory turnover rates and identify slow-moving or
obsolete products. How can inventory management practices be
optimized to reduce carrying costs and improve cash flow?

Competitor Benchmarking:
Compare sales performance with competitors in the same market
segments. How does your company's market share and growth
rate compare to industry benchmarks?

Price Elasticity Estimation:

Assess the price elasticity of demand for different product
categories. How sensitive are sales volumes to changes in
product prices, and what pricing strategies can be implemented
to maximize revenue?

Customer Satisfaction Analysis:

Utilize customer feedback data to assess overall satisfaction
levels and identify areas for improvement. How do customer
satisfaction scores correlate with sales performance?

Marketing Campaign Effectiveness:

Evaluate the effectiveness of marketing campaigns in driving
sales and brand awareness. Which campaigns have yielded the
highest return on investment, and how can future campaigns be
optimized for better results?

Task-5

1. Define a user-defined function to calculate the total

forecasted quantity for a given product and fiscal year.

2. Write a query to find the customers who made purchases

exceeding the average monthly sales quantity across all
products.

3. Create a stored procedure to update the gross price of a

product for a specific fiscal year.

4. Implement a trigger that automatically inserts a record into

the audit log table whenever a new entry is added to the
sales table.
5. Use a window function to rank products based on their
monthly sales quantity, partitioned by fiscal year.

6. Utilize the STRING_AGG function to concatenate the

names of all customers who purchased a specific product
within a given timeframe.

7. Develop a user-defined function that calculates the total

manufacturing cost for a product over a specified range of
years, using a subquery to retrieve the necessary data.

8. Design a stored procedure to insert new records into the

sales table and use a trigger to enforce constraints on the
quantity sold, ensuring it doesn't exceed the available
inventory.

9. Apply the LEAD or LAG function to compare monthly

sales quantities of a product with the previous month's
sales.

10. Create a query to identify the top-selling products in

each market based on their total sales quantity, utilizing
subqueries and window functions.
11. Develop a user-defined function to calculate the total
freight cost for a product based on its market and fiscal
year. Then, integrate this function into a stored procedure to
update the overall cost.

12. Write a trigger that automatically updates the

inventory count in the product table whenever a new sale is
recorded, utilizing inbuilt functions to perform the
calculation.

13. Implement a trigger to enforce referential integrity,

ensuring that only products listed in the product table can be
added to the sales table, utilizing subqueries to validate the
data.

14. Design a stored procedure to generate a report showing

the month-over-month growth rate of sales for each
product, using window functions to calculate the percentage
change.

15. Develop a user-defined function to calculate the

average discount percentage given to customers for a
 specific product, utilizing inbuilt functions to aggregate and
analyze the data.

16. Write a query to identify the customers who made the

highest total purchases in each region, using subqueries and
window functions to perform the analysis.

17. Create a stored procedure to calculate the total revenue

generated from sales for a given period, using inbuilt
functions to handle date manipulation and aggregation.

18. Implement a trigger to automatically update the

forecasted quantity in the forecast table whenever a new
product is added to the product table, utilizing a user-
defined function to calculate the forecast.

19. Develop a trigger to identify outliers in the monthly

sales data and flag them for further investigation, leveraging
window functions to detect deviations from the expected
sales patterns.
20. Write a query to retrieve the products with the highest
average gross price across all fiscal years, using subqueries
and inbuilt functions to perform the analysis
 Task-6

Aanalyze the monthly forecast accuracy for a specific product

over multiple fiscal years.

Question:

Write a SQL query that calculates the forecast accuracy for a

given product over different fiscal years.
Utilize Pivot table functionality to present the forecast quantity
and actual sold quantity side by side for each month within a
fiscal year.
Calculate forecast accuracy as the percentage of actual sold
quantity compared to forecast quantity.
Interpret the results to identify months with high or low forecast
accuracy and provide possible reasons for the discrepancies.
Include considerations for handling missing or incomplete data
in the analysis.
Submission Guidelines
Format: PowerPoint or PDF
Length: 1-20 slides.
Sections: Introduction, Key Findings, Actionable,
Methodologies , Approaches, Insights, Conclusions

Tools and Technologies :

SQL / MS-SQL SERVER

Deadline:
Submit your report and presentation within 21 Days from the
day you will start.
********************Thank You***********************