Tackling the dashboard

[Wai-Chung]

Hello Excel Experts,

I am trying to create a dashboard to calculate the monthly sales of each location broken down to by All Product Groups. This spreadsheet comes from the supplier monthly and I am trying to take their data and create an internal spreadsheet. They calculate their data on a YTD basis, from Jan-Dec. However, I would like the data on a month to month basis.

I started the spreadsheet in March (the first month) and manually entered the data. But I would hope that there are some suggestions as to how I can take the YTD data and calculate the monthly numbers going forward with less manual input from my side.

For example, in March, LOC671759 has a total Prior YTD of 213 and Current YTD of 297. In April, their Prior YTD is 215 and Current YTD is 644. So, it means that in April 2014, their sale was 2 and in April 2015, their sale is 347.

At this point, I am more concern with being able to get the monthly total of All Product Groups for each location in a table (TL-Internal) so that I can create a pivot table with the data. I hope that I am clear with what I would like to do with the data and hopefully someone will be able to give me some advice.

Thank you for your help and for reading.

Chung

 

[eibi]

Chung,

Take a look at the attached. I've created formulas for April 2014 and April 2015 based on the directions you provided in the third paragraph of your original post.

An aside: There is some data clean-up needed. The monthly spreadsheets sometimes include or omit the leading space " " before the Location number...which confuses the formula...

Also, not knowing the maximum length of your spreadsheet, I've assumed that you won't ever have more than 750 rows...​

Is this the direction you want to go?

I would need more direction from you in order to understand how you want to calculate the monthly numbers for March from the data you provided...

All best.

 

[Wai-Chung]

Thank you very much Eibi. I am just trying to understand the formula and where everything goes. I don't think I have been exposed to too many multi function formulas so my knowledge is this area is poor.

When I receive the worksheet for May, do I replace "April!" with "May!" in the formula.

Thank you once again for your help,

Chung

 

[eibi]

Generally, you'll find that it is best to use formulas that don't have to be changed every time you update the spreadsheet...but I tried to fit my solution to the sample file you had provided.

The short answer is yes. You can easily replace all the occurrences of April! with May! by using the the find and replace dialogue: Ctrl F.

Remember that you'll need to replace all the March! references with April! references too, because your formulas use data from both.

Watch out! You don't want to reverse the order and first convert all your Marches to Aprils -- or you will have a mess to clean up when you replace the Aprils with Mays...

All best.

 

[Wai-Chung]

Hello Eibi,

If it is not too troublesome, would you be able to show how I could use formulas on this spreadsheet.

Thank you,

Chung

 

[eibi]

Chung,

See the attached example, it provides a very simple illustration of the concept that I would suggest for you. However, you know your situation best, so I'll leave it to you to decide whether it makes sense to invest enough to make this happen.

You mentioned that your experience with compound functions is limited -- and by coming to Chandoo, you've certainly come to the right place. Chandoo promises to make you awesome in excel, and we're all working together toward that mutual goal...

As you build your dashboard, feel free to ask if you have specific challenges you want help to troubleshoot. I've always found good answers to my questions on this forum.

All best.

 

[Wai-Chung]

Hello Eibi,

I was wondering if in the offset/indirect formula that you wrote for me, is it possible to sum only a few lines in the spreadsheet, TL-Internal, such as "Brake" & "Drive Train" for example?

 

[MSC Bobs]

Wai-Chung,

I'm just throwing this out there, but why don't you call your supplier and ask them to send you a flat file table each month?

Your spreadsheet looks like a pivot table with the links broken, so why not ask them for the source data?

 

[Wai-Chung]

I tried to ask for the source data, but their data is nationwide, so i am only receiving the part that is related to our group of companies. Otherwise, it would easier to make my own pivot table.

 

[eibi]

...is it possible to sum only a few lines in the spreadsheet, TL-Internal, such as "Brake" & "Drive Train" for example?

My initial observation is that the formula I originally proposed is based directly on the "All Product Groups" line of the detailed report -- and it assumes that each location includes this summary line.

Your new request will actually require us to:

1. Test for the occurrence of a specific item at each given location (because a location should return a 0 if it doesn't list the item, correct?)

2. Sum the specific items in which you have interest, such as "Brake" & Drive Train" for example.

It's a logical next step, and it's certainly a move toward the dashboard functionality you initially suggested. But it will take some re-thinking of the formula I original proposed.

Let me work up something for your review.

 

[eibi]

Chung,

In attached file, note that cell A15 has a dropdown list (populated from on G1:G19) which allows you to selected a specific category you want to see subtotals for.

The formula is admittedly cumbersome, but by using some of the data manipulation tools such as dropdown boxes, we can avoid getting into rewriting formulas unnecessarily.

If you want to select multiple categories, we could accomplish that with a set of check boxes rather than a dropdown list...but we're about to a point where we will need to reconfigure the data before we'll be able to get the granularity you want...

The best next step (from my perspective) is for you to develop a "final report" template or sketch an "ideal dashboard" showing the options that you ultimately want, so that we can work incrementally toward that goal.

 

[eibi]

Chung --

I'm still working on this, and I've got it calculating the subtotals as you wanted.

See attached.

The "x" in H1:H19 determines which categories are included in the totals; try changing or deleting some of them; you'll see what I mean...

What do you think?

 

[Wai-Chung]

Eibi,

Thank you very much for the work that you have put in to help me with this spreadsheet. I am working with it now and will get back to you soon.

Thank you,

Chung