• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Comparative Balance Sheet Data - Lookup Previous Date Values

CMMaliniJoshi

New Member
Dear All
I have got sample balance sheet data in CSV files for two separate dates. Based on these CSVs I have to provide comparative data of increase / decrease in selected parameters of the balance sheet. The sample data is as per CSV files attached. I need output as per excel file attached with the name Comparative.xlsx Sheet named "Output". I shall be getting CSV file for each date separately. I shall keep adding new CSV file in the dedicated folder for the purpose. However, I shall have to present comparative data of any two dates only on any given point of time.

I could manage to achieve data for all the parameters. But one more parameter is required in my output i.e. Working Capital. I cannot figure out how to achieve the same in output. Now, Working Capital is to be derived as under:
Total of Balance Sheet - (Minus) Branch Adjustment.
Now, branch adjustments can appear on both side of Balance Sheets (Assets / Liability) and I have to nullify the effect of Branch Adjustment i.e. Branch Adjustment on one side of Balance Sheet will be deducted from total of Balance Sheet and Branch Adjustment on Second Side will be added to total of Balance Sheet.
Finally, I have to show Working Capital of each month as a separate parameter in the output alongwith increase / decrease in two dates.

I have achieved output of the rest of the parameters as per Excel Attached named "Comparative.xlsx" which is also having Queries prepared by me. But I am yet to work out working capital. I request you all provide a solution to workout Working Capital as desired in output. Besides, if anyone can give better idea on how to present other parameters alongwith Working Capital, I shall be thankful.

Regards
 

Attachments

  • 30-08-2022.csv
    1.6 KB · Views: 1
  • 30-09-2022.csv
    1.6 KB · Views: 2
  • Comparative.xlsx
    25.4 KB · Views: 2
Not having anything to do with finance/accounting I'm struggling with the Working Capital calculation. Does it involve all the categories?
Could you post your file again but include in your desired output sheet additional manually calculated rows and values for the actual csv files you attached? Then I'll have something to aim for.

In the meantime, in the attached is some of the other things you want; At cell I1 there's a table of dates derived from your Data query. This table could get longer if you have more csv files in the folder.
In the cells J2 and J3 are the two dates you want to compare. The queries look at these two dates. You can enter dates in these cells in several ways:
1. Manually type in a date
2. Use the data validation dropdowns in those cells
3. If you've allowed macros to run, double-click one of the cells in the Dates Available table in column I and that date will get transferred to the cells in column J as follows:
If cell J2 is empty, the double-clicked date will go into that cell. If it's not empty it will go into cell J3.

I have one more query. The first step of the Comparitive query does a nested join (left outer)which gives a match for all rows in the first table and matching ones in th second. Shouldn't this be a full outer join which is all rows from both tables? It may not make a difference if you're certain that all the csv files have a full complement of data.
 

Attachments

  • Chandoo49507ComparativeOrigDataFolder.xlsm
    31 KB · Views: 2
Last edited:
Dear p45cal
Sorry for the late revert. I am enclosing herewith an Excel File named WC.xlsx on how to calculate Working Capital alongwith desired output sheet. You may base your working on it. This is just for rough reference as I shall have to amend it as per my needs as I have to work on multiple locations' Balance Sheets.
The idea given by you regarding dates is cool. This is definitely helpful. The idea of Macro, too, is cool (Actually I do not have knowledge of Excel VBA).
Regarding Full Outer Join: You are right. It is my logical error. If at any point of time, any one or more of the categories is / are not there in any particular balance sheet, the outer join will still show Increase / Decrease. If it is Left Outer Join it will show Categories from First Table Only. Hence, we should go ahead with Full Outer Join only.
 

Attachments

  • WC.xlsx
    25 KB · Views: 3
I will look at this later.
In the meantime, can you tell me whether you tested my last offering with your data? Do you have the folder with many csv files in? Did the dates list get longer? Were you able successfuly to load and compare different pairs of dates?
 
Dear p45cal
The solution provided is working correctly. I added some dummy data CSV and dates got updated correctly and comparative figures worked out correctly, too.
Thanks
 
In the WC file you include Nominal Member Fee and Entrance Fee in the calculation towards the Working Capital, however you exclude these two rows from the final report. Do I still include these rows in the Working Capital calculation?

ps. your worked examples are very clear; very refreshing!
 
Dear p45cal

Que:In the WC file you include Nominal Member Fee and Entrance Fee in the calculation towards the Working Capital, however you exclude these two rows from the final report. Do I still include these rows in the Working Capital calculation?

Ans: Yes. Pl include them because they are not contra items. I have to exclude contra items in WC calculations only. (Although my actual calculations on real data will somewhat differ because in real data contra items will spread across our locations and some items will be added in exclusion. Hence pl include them in Working Capital calculation.)

ps. your worked examples are very clear; very refreshing!
Thanks
 
Dear p45cal
This is super cool. The solution provided is working correctly. The steps added of myDate and steps added for WC are awesome. They are concise and precise. Thanks a lot for the support.
Sorry for the late revert.
 
Back
Top