innovative way to display data

[hussein_elsayed]

Dears,

Need your expert advice for the below,

Is there an innovative way to display the attached data model in a clear and understandable way (customer indebtedness and its payments)

Thanks in advance

[#2]

@hussein_elsayed 

Is it first possible for you to make clearer what data is relevant.

You've removed client names, which is thoughtful, but more thoughtful would be to insert false client names, so those of whom you're asking for help would not have to guess at where one client stops and another picks up.

And maybe spell out a little more completely what data you want to be highlighted. You say "customer indebtedness and its payments" but what specifically does that amount to? A history of debt balance and individual payments; only current debt balance and payment due; something else? You may think it's obvious, but there are many ways to interpret your words in practice; these are questions I'd be asking if we were sitting down face-to-face.

[#3]

@mathetes 

First of all i'd like to thank you for your valuable points. 

- i have a large data i just sent only sample and i will attach the whole data sheet

- i need to make the attached data to be clear for any one to use

- i need to creat some reports for aging and customer balances and any other usful reports to tracking the outstanding client dues.

could you please help in this issue and thanks in advance

[#4]

@hussein_elsayed 

First let me ask, since I don't read Arabic, whether those are real names: if they are, since there are other people who read Arabic (presumably including you), you should remove that file from the site.  Please also, put in names of movie characters (Luke Skywalker, Mickey Mouse, Minnie Mouse, etc)

Second, you've not really  answered my question. I fully understand the desire for clarity, I know what that means. But I'm not familiar with this kind of data, or the kind of report you're charged with creating. Your answer, "i need to creat some reports for aging and customer balances and any other useful reports to tracking the outstanding client dues" assumes that I would know what those terms mean. I have some ideas, but again, you're asking me to guess, especially with "any other useful reports"--what is "useful"??  Not the meaning of the word--I know that--what would be an "other useful report" in this field?

As I said, if we were sitting down face-to-face, I'd be asking these questions and we'd be able to move forward quickly. You need to assume the person you're writing to (on a site such as this) is knowledgeable about Excel, but an utter novice when it comes to the specific kind of data and report that you're working with. That's your expertise; you need to do some translating.

[#5]

@mathetes 

Dear Mr. Mathetes

 i'm sorry for the inconvienient, let me explain more to make that issue clear.

I have a list of different units’ column (B) that sold with list of amounts column (D) with different payment date column (E)                                                                                     

my input is entering the payments only and for the first columns (A: E) without change still as it is                                                                              

Here under explanation of the attached sheet I hope to be clear for you                                                                            

1st          I have a list of dues per date per unit                                                                  

               Unit       Due Date                                                         

               NE04-B-1702      25/01/2019                                                     

               NE04-A-0506      16/03/2019                                                     

               NE04-A-0506      16/06/2019                                                     

               NE04-A-0506      16/09/2019                                                     

2nd        I have a list of collection in columns that covered the above dues that paid in different date                                                               

                Payment_1                                       Payment_2                      

               COLLECTED         CLEAR DATE        Rec#      COLLECTED         CLEAR DATE        Rec#

               160,000               02/04/2019         5006      61,598   09/04/2019         5008

3rd         calculated field (BE) that refer to the outstanding dues which = amount in column (D) - total collected in column (AW)                                                                     

My question is                                                                             

Is there any way to present the above date to be clear or understandable for anyone?

I hope you got my point.

Thanks

[#6]

@hussein_elsayed 

You wrote: 

My question is                                                                             

Is there any way to present the above date to be clear or understandable for anyone?

I hope you got my point.

And yes, you made your point. But the thing I don't understand is why you--the specialist in this field--need to ask "is there a way to present the above data to be clear for anyone."  You would be expected to lay out the data in a way that's clear, not just pointing to the pieces of data. I can tell you how to use excel to take data elements from your spreadsheet and array it in a manner that's clear, but I need YOU to tell me how that data could be arrayed so as to be clear and understandable to anyone. Not just point to the data and ask if it can be done.

So my question back to you: is there any way for YOU to create a dummy display demonstrating how the data could be arrayed so as to be clear and understandable. I'm NOT asking you to come up with the Excel instructions, that the job of the Excel experts. But I am asking you to create a clear and understandable display of the relevant data, doing it manually....I assume you want this to be for your clients, or for a customer service rep or the like. What would be helpful to them, how would it be arranged. I think the Excel part is going to be fairly easy.....but you need to be the one to come up with at least an initial creative and clear display of the information you want to show.

[#7]

@mathetes 

i put some reports in the attached file 

- Sheet (Dues) i need to add formulas to sum the total collected (F) from sheet collection details and settle the first dues based on earliest dues till consume the total collection, also i need to calculate the culumns from (H:L) based on the due dates and the monthly closing date lets say 31-03-2022

- Sheet aging for ar balances, i need to calculate the Ar balances (sheet dues Culumn G) and the critira range will be project culumn (A) and the due date (E)

- Sheet Delayed AR balances to calculate the delayed dues (AR balance in sheet dues_G) based on the due date in sheet dues 

- sheet unit reprt i need to filter by unit to show all details related to the unit.

I know I'm bothering you, but I need help, even with an idea

Thanks You

[#8]

@hussein_elsayed Let me know if this set of results makes sense, if the numbers are what you'd expect.

I am using the new FILTER function which requires the most recent version of Excel.

My guess is that you'd be well served in the other reports as well with the FILTER function. Here's a YouTube Video that explains it. https://www.youtube.com/watch?v=9I9DtFOVPIg

[#9]

Is there any way to consume the dues amount based on total collection from the collection details sheet to be consumed from earliest due date till consuming the total collection in the collection details sheet

[#10]

@hussein_elsayed 

I'm sorry: I don't understand the question. I realize English may not be your first language, but I'm not really able to figure what you mean by "consume" and its variations.

May I ask if the work I did send you made any sense. Or is it there that you're looking for total dollars owed in the various categories of delayed payment?