Re: Calculating A/R in a Financial Model

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Dick Kusleika (dickk_at_paragonUNMUNGEconstructioninc.com)
Date: 03/15/04 

Date: Mon, 15 Mar 2004 10:21:33 -0600

Sjt

Assume that DOS is in row 1, A/R is in Row 2 and Sales is in Row 5, for each
month. Assume the data starts in Col B with row headings in Col A. You'll
have to start this formula in Col C, because you won't have prior month's
sales if you start in Col B. Here's the formula for C2

=(MAX((C1/30)-1,0)*B5)+(MIN(C1/30,1)*C5)

Note that this will work for DOS from zero to 60. If you need to be able to
go higher than 60, let me know and we can adjust the formula. Also note
that a standard of 30 day months is used. If you have a row for dates, you
can use the actual number of days in the months.

In this formula
C1 = DOS for February
B5 = Sales for January
C5 = Sales for February 

-- 
Dick Kusleika
MVP - Excel
www.dicks-clicks.com
Post all replies to the newsgroup.
"Sjt" <anonymous@discussions.microsoft.com> wrote in message
news:3E7EA844-E445-4A31-BD75-B01ECD3BB3AE@microsoft.com...
> I am trying to calculate accounts receivable in a 12-month financial model
in the following manner.  Assume that cell A1 holds the assumption for days
outstanding and that DOS is currently 45.  In month 2, I would like to be
able to take the second half of the first month and the complete second
month and put them in A/R and in the third month I would want to take the
second half of the second month's sales and the entire third month's sales
and put them in A/R and then copy across.Obviously, days outstanding could
change so formula needs to be flexibe. Can this be done?  Thank you for your
help.

Relevant Pages

  • Combining Files - Think Outside the Box
    ... I daily get a file of sales on the product I ... I have been trying to avoid opening one file at a ... in the same folder (or Directory as DOS calls them), ... and using the DOS command: ...
    (microsoft.public.excel.misc)
  • Re: Combining Files - Think Outside the Box
    ... I daily get a file of sales on the product I ... I have been trying to avoid opening one file at a ... in the same folder (or Directory as DOS calls them), ... and using the DOS command: ...
    (microsoft.public.excel.misc)
  • RE: Limited vs full blown testing
    ... At this point, I'm dealing with the ... Here's what I just sent that sales rep a few hours ago: ... are to a DOS attack. ... talking just a single machine and the tests that can be accomplished ...
    (Pen-Test)
  • Re: [opensuse] Will there be a LTS version of OpenSuse?
    ... producing a lower cost version for personal use - that really would ... Focus on sales killed many good products, pushing customers to competition. ... I can remember that MS DOS was some 20% cheaper than IBM DOS, ...
    (SuSE)
  • Q: Cant get crosstab query to work right
    ... I have a crosstab query with row headings ... Region B has no sales, so I have to create a table with ... the row headings always show those 4 regions). ... 40 crosstab queries that need to be updated monthly). ...
    (microsoft.public.access.queries)