Looking for Assistance with Complex Excel Formulas for Data Analysis

[Oliver50]

Hello Everyone ,

I'm contacting this informed group because I'm in need of help with some complicated Excel formulae for a project that I'm working on that involves data analysis. Excel has been my go-to tool for simple jobs, but this project calls for more intricate calculations, so I'm a little stumped in this community .

Here is a quick synopsis of my project:

I'm doing sales data analysis for a retailer. Columns for the product ID, category of the product, sale amount, region, and date of sale are all included in the dataset.

I have the following analyzes to complete, however I'm unsure of which functions or formulas to use:

Monthly Sales Overview: I must compile the total amount of sales broken down by product category and month. I'm guessing I'll need to use SUMIFS & DATE functions in this combination, but am not sure exactly how to configure it properly.

Year-to-Date Sales Development: I am interested in finding out how much each product category's sales have increased so far this year in comparison to the same time previous year. Basic % change formulas are nothing new to me, however I'm having trouble applying it to a temporal date span.

Top Performing Regions: According to total sales, I must determine which five regions are the best. Although I've previously used the SORT and LARGE functions , I'm not sure how to combine them with my dataset to produce the intended outcomes.

Sales Forecasting: Using previous sales data, I wish to produce a straightforward linear forecast. Although I am aware about the FORECAST function , I would really appreciate some advice on how to apply it in this particular situation.

For your reference, I've attached another portion of my dataset. I would greatly appreciate any advice, example formulas, or sap resources that you could provide.

Thank you in advance.

 

[VetMini]

The first three problems can be sorted out with Pivot Table.
Please look for solutions in Pivot/Power Pivot and Data Model.

The fourth problem is a bit more complex, depending on how deep you want the forecast should look.
Trend and ForeCast.
Since you only want the Forecast with Linear Regression, it's quite simple:
In this example below, the function is used to calculate 2025 sales forecast, with sales data from 2018~2024 (note that 2021~2022 are missing, the fact is: not really matter, you don't need your data in continuous order)
As you can see:
- the first parameter is the x value for the y value that you want to calculate.
- the second param is the range for your x-values
- the third param is the the range for corresponding y-values

 

[ptm0412]

As post #2 has said, if you give some sample data I would make a sample result by Power Pivot, Data Model, and pivot table for the first 3 problems.

 

[tranhungdao12a3]

Hello Everyone ,

I'm contacting this informed group because I'm in need of help with some complicated Excel formulae for a project that I'm working on that involves data analysis. Excel has been my go-to tool for simple jobs, but this project calls for more intricate calculations, so I'm a little stumped in this community .

Here is a quick synopsis of my project:

I'm doing sales data analysis for a retailer. Columns for the product ID, category of the product, sale amount, region, and date of sale are all included in the dataset.

I have the following analyzes to complete, however I'm unsure of which functions or formulas to use:

Monthly Sales Overview: I must compile the total amount of sales broken down by product category and month. I'm guessing I'll need to use SUMIFS & DATE functions in this combination, but am not sure exactly how to configure it properly.

Year-to-Date Sales Development: I am interested in finding out how much each product category's sales have increased so far this year in comparison to the same time previous year. Basic % change formulas are nothing new to me, however I'm having trouble applying it to a temporal date span.

Top Performing Regions: According to total sales, I must determine which five regions are the best. Although I've previously used the SORT and LARGE functions , I'm not sure how to combine them with my dataset to produce the intended outcomes.

Sales Forecasting: Using previous sales data, I wish to produce a straightforward linear forecast. Although I am aware about the FORECAST function , I would really appreciate some advice on how to apply it in this particular situation.

For your reference, I've attached another portion of my dataset. I would greatly appreciate any advice, example formulas, or sap resources that you could provide.

Thank you in advance.

You can use Excel, but I recommend using Power Pivot + Data Model. It is suitable for sales analysis.
You can relationships the dDate table with the Sales table through the Date field, relationships Products table with the Sales table through Product ID, and relationships Region table with the Sales table through Region ID:
Question 1: Monthly Sales Overview
Write a function Total Sales = Sum(Sales([Salesamount]))=>create a Pivot Table in Power Pivot => drag the Category field, Product into Rows, Year and Month into Columns, and Total Sales into Values.
Question 2: Use the Total YTD Sales function: YTD = TOTALYTD([TotalSales],'dDates'[Date]) or Sales YTD2 = CALCULATE([TotalSales],DATESYTD('dDates'[Date]),...
Power Pivot has a powerful Time Intelligent function that allows you to calculate months, quarters, years,....
Question 3: Top Performing Regions:
Use the TopN function or Rankx function to get the top 5 products or Top 5 Regions.
Question 4: Sales Forecasting:
For simple forecasting, you can use the Trend or Forecast function in Excel or use the Calculate + Sum + Average + Var + Predict function in Dax Power Pivot. If You need more in-depth forecast is needed, I suggest using regression models, Arima models, or other models. To do this, you may need more specialized tools, such as Python or R, or combine Power Bi with Python or R.
I'm sorry, my English is very Bad!

 

[VetMini]

Back to the basics with your project:

Here is a quick synopsis of my project:

I'm doing sales data analysis for a retailer. Columns for the product ID, category of the product, sale amount, region, and date of sale are all included in the dataset.

Unlike most others in this forum, who always come up with the excuse of having to comply with ridiculous datasets and output interfaces, you have the luxury of designing the output yourself, and a proper dataset to begin with.

Your situation is [almost] a perfect example for Power BI. Having it prepared professionally, your client will have a beautiful dashboard to play with various scenarios.

For your reference, I've attached another portion of my dataset. I would greatly appreciate any advice, example formulas, or sap resources that you could provide.
...

We havent got the opportunity to view that dataset.
Otherwise you would have had the demo of Power BI in Action (as promised in post #3).