Closed

I need an Excel Expert that can think of creative formulas to make an accounts receivable schedule very dynamic.

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).

Example:

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.

Skills: Excel, Accounting, Finance, Data Processing, Data Entry

See more: upwork, online accounting jobs from home without investment, online freelance jobs, online data entry jobs, freelancing it jobs online, freelancer, online data entry jobs work from home, freelancer jobs, need excel expert, need excel expert match columns, need excel expert create macro, i need excel data entry, i need excel expert, i need seo expert for my, i need seo expert, where can i hire excel expert, any company need excel expert from home, how do i find excel expert in sri lanka, i need excel freelancer, i need excel help

About the Employer:
( 0 reviews ) Toronto, Canada

Project ID: #24780162

50 freelancers are bidding on average $26 for this job

elqueabandona

Hello, my name is Cristian, i am Excel/VBA expert. I am in the top 5 of freelancers in this area and i have more than 260 projects successfully completed here. I am available to help you create a dynamic formula. Con More

$30 CAD in 1 day
(251 Reviews)
7.0
TonmoyRoy111

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

$50 CAD in 1 day
(380 Reviews)
7.0
mtdevil369

Hi there, Cash [login to view URL] complete within 24 hrs .. I will make Visual Basic code as per your requirement with good and professional quality without any error. We can work together as earlier. If you want to award More

$30 CAD in 1 day
(208 Reviews)
6.4
finecodervw

Hi, Keen to work on this project. Please suggest suitable time to discuss further on this requirement. I am well versed with Excel and its various features like Userforms, Pivot tables, HLookup, Vlookup, Countif, SUM More

$25 CAD in 2 days
(42 Reviews)
5.3
akritisood94

Hi I am an excel [login to view URL] can have a look over my reviews.I will wait for your response to discuss further . Regards

$30 CAD in 1 day
(82 Reviews)
5.5
camayur2000

About your project requirement: I read through your project requirements to make the spreadsheet dynamic and can return value of Receivables over a period of time. I can help you fix this sensitivity in the model. Ab More

$56 CAD in 1 day
(4 Reviews)
3.2
graj22

Hi, I understand your requirement regarding an Excel expert and i feel it aligns with my area of expertise. I am a Data Scientist by profession with over 5 years of experience in Excel (formulas and VBA), R , Python , More

$30 CAD in 1 day
(4 Reviews)
2.9
(3 Reviews)
2.3
ravibach

Accuracy and quick TAT guaranteed. payment can be made only if satisfied with the result. please connect on messanger to take this forward.

$35 CAD in 1 day
(1 Review)
1.7
johnwanjiku

Hi, I'm a STATISTICS Masters writer for the last 6 years. I have expertise in Statistical Analysis. I can show you some of my previous analysis. I have excellent concepts of Random variables, Probability Distribution, More

$20 CAD in 7 days
(2 Reviews)
1.4
HassanJavaid14

In the name of ALLAH, I am qualified accountant. I am real apotheosis, a perfectionist with insight on contemporary accounts and finance profession. I have experience of external and internal audit of some renowned bus More

$10 CAD in 1 day
(1 Review)
0.6
kars1010

Quite interesting task, am from finance background and have good hands in excel.I can complete your task.

$25 CAD in 7 days
(0 Reviews)
0.0
pbhusalonline7

I read job description and I can do your project. I can even work on urgent basis and complete your project soon..contact me for more information

$20 CAD in 10 days
(0 Reviews)
0.0
PGM1975

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.

$20 CAD in 1 day
(0 Reviews)
0.0
Atul307

Hi, I am Abhishek, I work at BDO India LLP one of the Big 5 Professional Service Firm in the world, as Articled Assistant. i hold strong grip over Data Analytical Tools and am proficient with all the functions of Excel More

$20 CAD in 3 days
(0 Reviews)
0.0
$24 CAD in 1 day
(0 Reviews)
0.0
alifkhan12

Dr sir I'm expect in Data entry operating urgent assessment Microsoft office Microsoft Excel sheet Microsoft power point and Microsoft office 2007 all working export my work experience with a 12 year my toping speed mi More

$25 CAD in 1 day
(0 Reviews)
0.0
Astirini

Currently i am working as the Sales person and technical person of Tally Solutions and my background in accounting

$15 CAD in 5 days
(0 Reviews)
0.0
SREEJITHVJ100

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.

$20 CAD in 7 days
(0 Reviews)
0.0
hoatv2009

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.

$80 CAD in 2 days
(0 Reviews)
0.0