Quarterly & Yearly Sales Analysis - Excel
Budget: ₹37,500 – ₹75,000 INR
I am in need of someone experienced in financial data analysis, specifically sales analysis, to review and interpret an Excel file. The deadline is this sunday 21st january 2024.
The ideal candidate for this project will have a strong background in financial analysis and will be comfortable using Excel for data interpretation and reporting. A knowledge of sales patterns, key contributing factors and an ability to identify trends is crucial. Also know how to put maps on dashboards and make it dynamic with filter selection.
Info on the file.
We have a set of data for one customer only.
In yellow are variables that can be used to create filters/graphs
- 56 unique vessel name with corresponding vessel #
- 3 delivery region (asia, europe or america)
- 29 countries within these 3 delivery regions
- 6 product group
- 19 product line - 1 line belong to 1 group only, 1 group can have multiple line.
- 50 product description - 1 product desc belong to 1 line only, 1 line can have multiple product.
In green there are 4 calculated values.
- Quantity (column o)
- Amount $ (column P)
- Gross margin $ (column Q)
- Gross margin % (column R)
In Blue there are 3 time frames
- Year
- Month
- Quarter
Column A to T was in the original file, Column U to AB is added by me.
Requirements:
Over the past 3 years the profitability and income from this client has declined. Using the past 3 years sales history and the key contributing factors outlined below, develop the optimal pricing strategy for this client. (my interpretation: need to renew this client's yearly contract, determine the best prices for each product line the client uses and the best port to buy those products from)
1) Analyze the income/profits of each vessel and their corresponding products and see if the client could buy the same products from a different port nearby instead for cheaper unit price.
2) Check the margins on each product line for the past 3 years and see which ones can increase prices and maintain significant demand, and those product line with decreased demand/qty, check if can lower prices and maintain healthy gross margin %.
3) do some dynamic charts to show qty/revenue/gm/gm% increase or decrease quarter over quarter & year over year.
4) use a world map to show each vessel's country visited during a specific year/quarter/month when selected a vessel #. Also show which product line purchased, qty/revenue/gm% for each.
5) highlights key trends in the data set.
6) 3 powerpoint slides that link to the excel sheet to show key trends
Factors to consider:
-company hesitant to raise prices to boost profits.
-Singapore is the company's lowest cost delivery location
The ideal candidate for this project will have a strong background in financial analysis and will be comfortable using Excel for data interpretation and reporting. A knowledge of sales patterns, key contributing factors and an ability to identify trends is crucial. Also know how to put maps on dashboards and make it dynamic with filter selection.
Info on the file.
We have a set of data for one customer only.
In yellow are variables that can be used to create filters/graphs
- 56 unique vessel name with corresponding vessel #
- 3 delivery region (asia, europe or america)
- 29 countries within these 3 delivery regions
- 6 product group
- 19 product line - 1 line belong to 1 group only, 1 group can have multiple line.
- 50 product description - 1 product desc belong to 1 line only, 1 line can have multiple product.
In green there are 4 calculated values.
- Quantity (column o)
- Amount $ (column P)
- Gross margin $ (column Q)
- Gross margin % (column R)
In Blue there are 3 time frames
- Year
- Month
- Quarter
Column A to T was in the original file, Column U to AB is added by me.
Requirements:
Over the past 3 years the profitability and income from this client has declined. Using the past 3 years sales history and the key contributing factors outlined below, develop the optimal pricing strategy for this client. (my interpretation: need to renew this client's yearly contract, determine the best prices for each product line the client uses and the best port to buy those products from)
1) Analyze the income/profits of each vessel and their corresponding products and see if the client could buy the same products from a different port nearby instead for cheaper unit price.
2) Check the margins on each product line for the past 3 years and see which ones can increase prices and maintain significant demand, and those product line with decreased demand/qty, check if can lower prices and maintain healthy gross margin %.
3) do some dynamic charts to show qty/revenue/gm/gm% increase or decrease quarter over quarter & year over year.
4) use a world map to show each vessel's country visited during a specific year/quarter/month when selected a vessel #. Also show which product line purchased, qty/revenue/gm% for each.
5) highlights key trends in the data set.
6) 3 powerpoint slides that link to the excel sheet to show key trends
Factors to consider:
-company hesitant to raise prices to boost profits.
-Singapore is the company's lowest cost delivery location