Client 1 is a Retail company that pays for a service.
Columns K – AQ contain recognized revenue numbers from an income statement (GAAP) perspective. There is a few nuances in the way that cash flow comes in the door as it relates to Accounts Receivables (an asset created when we bill a client, but they take 1-2 months to pay the bill in cash). This is where I need help and the section to look at is AT – BZ, starting with cell AT9 (April 2020).
Client 1 is a customer with the following characteristics:
• Input 1: We send a bill to them once every 3 months (i.e. every quarter) – as per column C or cell C9.
• Input 2: Their next bill date is May 1st, 2020 (this is the date that we send the bill to them – not the date they pay us in cash) – as per column D or cell D9.
• Input 3: Current payments terms are 2 months (this means if we send the bill May 1st, 2020, they will pay us in cash June 1st, 2020) – as per column E or cell E9.
Ask / Help:
Section AT – BZ is the accounts receivable ending balance for each month. A number will appear here between the time a bill is sent and not paid in cash. For example with Client 1, we are billing them May 1st, 2020, but are not receiving the cash until July 1st, 2020 (because the payment term is 2 months). This causes an A/R balance to show up in May and June as the account is not paid out. The amount that shows up in May 2020 is (in this case) 3 months-worth of revenue from cells L9, M9 and N9 (i.e. the sum of those 3 cells). If cell C9 changes to 4, the number would sum up 4 cells (L9, M9, N9 and O9).
We have figured out a formula that will work in the case of billing every 3 months (input cell C9) and payment terms of 2 months (input cell E9), however the formula doesn’t hold when I change C9 to 6 for example. When I change cell C9 to 6, that should mean the following:
• We bill them on May 1st, 2020 for 6 months-worth of revenue: $750k. Because its 2 months terms, we won’t get paid until July 1st, 2020.
• Cell AU is 750k, cell AV is 750k, cell AW is 0 as that’s when we get paid. Cell AX is also 0, along with AY and AZ (right now AY is picking up 750k which is wrong…). An additional billing is happening November 1st, 2020 (6 months after May 1st) – which is cell BA9. But because its 2 month payment term, the same number should show up on cell BB9 as well… which is currently doesn’t.
Hoping you can help fix this formula or create something new. Ultimately, I want to be able to change cells C9, D9 and E9 and have the formula work dynamically across the time periods.
50 freelancers are bidding on average $26 for this job
Dear client, I have sound knowledge in Excel and I have previously carried out a number of projects in excel using macros and formulas. I ensure you will be happy after hiring me. Kindly contact me. Roy
Being an expert in excel it is not a difficult task for me. I have seen calculations povided by you. It is quite interesting. Your desired calculations will be provided with appropriate functions.
I have already engaged with these types of work in my CA Articleship. Further, my knowledge in Finance and Accountshelp me really lot in my current job and the same will be highlighted in the this job forming the bid.
Dear client, I'm interest with your technique problem and I can give you the great idea to solve this one and the same in the future. Feel free to contact me. Hope to hear from you. Kindest regard.