+ Reply to Thread
Results 1 to 13 of 13

To solve a matrix of Billing & Payment on FIFO basis

Hybrid View

  1. 01-19-2019, 08:28 AM #1
    Vivek2705
    Vivek2705 is offline
    Forum Contributor
    Join Date
    02-13-2016
    Location
    MUMBAI
    MS-Off Ver
    Microsoft 365
    Posts
    180

    To solve a matrix of Billing & Payment on FIFO basis

    Hiii..

    Attached is my query file..

    I want a formula in cell D3 such that when I copy it to all months Column Q & Row 15 all should reflect matched..
    The Column contains the billing amount & the rows contain the payment amount. They are to be matched on FIFO basis..i.e. payment in april month Rs.42000 should be 1st adjusted against opening balance of 79000 & if balance left (not in this case) should be adjsuted against april bill & so on...

    The formula to show blank if in any month no payment or bill is made..

    Basically it can be seen as total demand (362000) should equals total supply (362000) & allocations need to be done accordingly..

    I think solver can solve this matrix but don't know how to set my data as per the requirements of solver..

    Thanks..
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 01-19-2019, 09:32 AM #2
    Richard Buttrey
    Richard Buttrey is offline
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: To solve a matrix of Billing & Payment on FIFO basis

    Would you manually populate the matrix with the results you expect so that we can understand the goal
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.
    Reply With QuoteRegister To Reply
  3. 01-19-2019, 09:48 AM #3
    Vivek2705
    Vivek2705 is offline
    Forum Contributor
    Join Date
    02-13-2016
    Location
    MUMBAI
    MS-Off Ver
    Microsoft 365
    Posts
    180

    Re: To solve a matrix of Billing & Payment on FIFO basis

    hii Richard..

    My expected values of matrix are hereby attached...
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  4. 01-19-2019, 11:08 AM #4
    Bo_Ry
    Bo_Ry is offline
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,222

    Re: To solve a matrix of Billing & Payment on FIFO basis

    Please try at

    D3
    =IF(SUM($C$3:$C3)<D$2,$C3,MAX(0,D$2-SUMIFS($D$2:$D2,$C$2:$C2,">0")))

    E3:P3
    =IF(SUMIFS(E$2:E2,$C$2:$C2,">0")<E$2,MIN($C3-SUM($D3:D3),E$2-SUMIFS(E$2:E2,$C$2:$C2,">0")),)

    copy D3:P3 down
    Attached Files Attached Files
    Linkedin/Bo-Rydobon
    Youtube.com/XcWizard
    Facebook.com/XcWizard
    Reply With QuoteRegister To Reply
  5. 01-21-2019, 03:47 AM #5
    Vivek2705
    Vivek2705 is offline
    Forum Contributor
    Join Date
    02-13-2016
    Location
    MUMBAI
    MS-Off Ver
    Microsoft 365
    Posts
    180

    Re: To solve a matrix of Billing & Payment on FIFO basis

    Perfect Formula..Thanks a lot..!!
    Reply With QuoteRegister To Reply
  6. 01-23-2019, 02:51 AM #6
    Vivek2705
    Vivek2705 is offline
    Forum Contributor
    Join Date
    02-13-2016
    Location
    MUMBAI
    MS-Off Ver
    Microsoft 365
    Posts
    180

    Re: To solve a matrix of Billing & Payment on FIFO basis

    Hii..

    I have reversed the matrix that is the column headings are now row headings and vice versa for some reasons but the formula didn't work in the reversed matrix now.
    I request you to kindly update the above formula to work in the reversed matrix ..

    The expected values which I want by formula are mentioned in cell H10 to cell AE34..

    attacehd here is the matrix..

    Thanks..
    Reply With QuoteRegister To Reply
  7. 01-23-2019, 02:55 AM #7
    Bo_Ry
    Bo_Ry is offline
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,222

    Re: To solve a matrix of Billing & Payment on FIFO basis

    Kindly upload the new workbook with the expected result.
    Reply With QuoteRegister To Reply
  8. 01-23-2019, 06:36 AM #8
    Vivek2705
    Vivek2705 is offline
    Forum Contributor
    Join Date
    02-13-2016
    Location
    MUMBAI
    MS-Off Ver
    Microsoft 365
    Posts
    180

    Re: To solve a matrix of Billing & Payment on FIFO basis

    Find the attached file..
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  9. 01-23-2019, 06:45 AM #9
    Bo_Ry
    Bo_Ry is offline
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,222

    Re: To solve a matrix of Billing & Payment on FIFO basis

    Please try

    H9
    =IF(SUM($G$10:$G10)<H$9,$G10,MAX(0,H$9-SUMIFS($H$9:$H9,$G$9:$G9,">0")))

    I9:AE9
    =IF(SUMIFS(I$9:I9,$G$9:$G9,">0")<I$9,MIN($G10-SUM($H10:H10),I$9-SUMIFS(I$9:I9,$G$9:$G9,">0")),)
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  10. 01-23-2019, 07:02 AM #10
    Vivek2705
    Vivek2705 is offline
    Forum Contributor
    Join Date
    02-13-2016
    Location
    MUMBAI
    MS-Off Ver
    Microsoft 365
    Posts
    180

    Re: To solve a matrix of Billing & Payment on FIFO basis

    Awesome!!..Thanks again..!!
    Reply With QuoteRegister To Reply
  11. 01-28-2019, 09:10 AM #11
    Vivek2705
    Vivek2705 is offline
    Forum Contributor
    Join Date
    02-13-2016
    Location
    MUMBAI
    MS-Off Ver
    Microsoft 365
    Posts
    180

    Re: To solve a matrix of Billing & Payment on FIFO basis

    Hii..

    The matrix is solved but when I copy the same matrix (E1:AF28) below I want the the constant range in the formula to catch from the copied matrix (E30:AF57) & not from the 1st matrix..what is the solution??
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  12. 01-28-2019, 11:56 AM #12
    Bo_Ry
    Bo_Ry is offline
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,222

    Re: To solve a matrix of Billing & Payment on FIFO basis

    Please try

    G3
    =IF(SUM(INDEX($F:$F,MATCH("z",$F$1:$F2)+2):$F3)< INDEX($G:$G,MATCH("z",$F$1:$F2)+1),$F3,MAX(0,INDEX($G:$G,MATCH("z",$F$1:$F2)+1)-SUMIFS(INDEX($G:$G,MATCH("z",$F$1:$F2)+1):$G2,INDEX($F:$F,MATCH("z",$F$1:$F2)+1):$F2,">0")))

    H3:AD3
    =IF(SUMIFS(INDEX(H:H,MATCH("z",$F$1:$F2)+1):H2,INDEX($F:$F,MATCH("z",$F$1:$F2)+1):$F2,">0")< INDEX(H:H,MATCH("z",$F$1:$F2)+1),MIN($F3-SUM($G3:G3),INDEX(H:H,MATCH("z",$F$1:$F2)+1)-SUMIFS(INDEX(H:H,MATCH("z",$F$1:$F2)+1):H2,INDEX($F:$F,MATCH("z",$F$1:$F2)+1):$F2,">0")),)
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  13. 01-31-2019, 05:41 AM #13
    Vivek2705
    Vivek2705 is offline
    Forum Contributor
    Join Date
    02-13-2016
    Location
    MUMBAI
    MS-Off Ver
    Microsoft 365
    Posts
    180

    Re: To solve a matrix of Billing & Payment on FIFO basis

    amazing!! Works Perfectly Fine....
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Share inventory & capital gains on fifo basis
    By pulvi in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 04-20-2017, 09:42 PM
  2. [SOLVED] How to calculate debtors ageing amount in days on FIFO basis
    By King_BD in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 06-20-2015, 10:57 PM
  3. match FIFO (first in first out) basis on stock
    By Irvine Kinneas in forum Excel General
    Replies: 2
    Last Post: 03-10-2015, 07:02 AM
  4. Multi year invoicing schedule on the basis of billing term and billing period.
    By ca.ashishagrawal in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-29-2013, 05:19 PM
  5. Weighted Average Cost of Inventory on FIFO basis
    By srigane in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-25-2012, 07:32 PM
  6. Billing and Payment Dates
    By CubedSphere in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 08-22-2012, 07:38 PM
  7. How to prepare Multi Stock Valuation on Fifo Basis in Excel
    By md12nov in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 04-12-2006, 04:09 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1