[Lnc-business] incorrect reserve calculations in monthly financials

[Alicia Mattson]

Colleagues,

It looks to me like the February financial report we just received
overstates our reserve amount by about $37k.

I am trying to make sense of the reserve calculations published in our
monthly financial statements (p. 3, Reserve Adequacy Trend chart), and not
just for this one most recent month.

Over the past two years, the FORMULA used to calculate the blue number in
that chart (called "Curr Month Resv" in the header) has changed several
times without our knowledge.  As I will show you, the several different
formulas used at different times give very different answers when applied
to the same month's data, often portraying our financial position as being
better than I think it really is.

It seems that for some LNC members, the reserve is the main number they
look at for a quick snapshot of our current position, so it's important to
get that number right.

First, a big-picture concept: what does our reserve level calculate?  Why
is it an important measure?  It calculates whether we have enough
unrestricted cash on hand to pay the next month's worth of occupancy,
labor, and governance expenses.  The amount of cash we need to meet that
goal is listed as the "Resv Target" in that p. 3 chart on our monthly
financials.  The blue number is the available cash we currently have
relative to that target number.  The 2019 goal is to have $57,225 at the
end of each month.  The February 2019 chart shows us at -$11,529, but I
think we're really at -$48,839.

Please refer to the attached spreadsheet, and below I will walk you through
it.  You won't be able to follow this well on a phone...get your full-sized
screen.  :-)

The numbers in rows 4 - 15 are copied/pasted directly from the monthly
financial reports sent to the LNC on this email list.  I have listed the
page and line numbers where those figures can be found in each month's
report.  These are the data points that (at various times) have been (or
ought to be) used in the reserve calculation.

Row 21 is each month's reported reserve amount, also copied/pasted directly
from the blue "Curr Month Resv" in the p. 3 Reserve Adequacy Trend chart in
the monthly financial reports.

Row 16 contains the formula that represents how I believe the reserve
should be consistently calculated.

Rows 17-20 show four of the several different formulas that seem to have
been used at different times over the past two years to calculate row 21.
You'll quickly notice that the results of the four different formulas
applied to the same month's data aren't even similar figures.

MAR - SEPT 2017
>From March 2017 to September 2017, it seems that the formula in Row 17 was
used to calculate the published number in Row 21.  This formula is close to
my proposed formula in Row 16 for that time frame.  For three of those
seven months I noticed manual errors were made in calculating the published
number, as described in rows 25 - 27, but otherwise that formula was used
consistently for those seven months.

OCT - DEC 2017
I have no theory about how the October to December 2017 figures in Row 21
were derived.  The numbers don't track with any of the other formulas I
identified here.

JAN - FEB 2018
It seems the formula in Row 18 was used to calculate the published number
in Row 21, but only for two months.

MAR - MAY 2018
It seems the formula in Row 19 was used to calculate the published number
in Row 21, but only for three months.

JUN - JULY 2018
I have no theory about how the figures in Row 21 were derived.  The numbers
don't track with any of the other formulas I identified here.

AUG - DEC 2018
It seems the formula in Row 20 was used to calculate the published number
in Row 21 (with the exception of a $1000 manual error described in Row 28).

Whatever the correct formula is, it should be the same formula from month
to month, not changing at least five times within a two-year period, with
such wildly different results which overstate our position.

I had an email discussion with Mr. Hagan and Mr. Kraus about these
fluctuations just prior to our March 9-10 LNC meeting, and Mr. Hagan
intended to work with Mr. Kraus during that weekend to establish an
approved formula.  Now we have a new financial statement, and I can't
figure out how this one was calculated, either.

It may well be that Mr. Hagan and Mr. Kraus didn't have a chance to work on
it during the weekend of that meeting, but since the reserve is the lead
number that was referenced when the February report was sent to us, it
seems this should be sorted out sooner rather than later so that the LNC
can rely on consistent data.

-Alicia
-------------- next part --------------
A non-text attachment was scrubbed...
Name: Reserve Calculations.xls
Type: application/vnd.ms-excel
Size: 23552 bytes
Desc: not available
URL: <http://hq.lp.org/pipermail/lnc-business/attachments/20190403/eba681e8/attachment-0002.xls>