Formula Error or Data Error?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Kevin Labore - 07 May 2008 15:08 GMT

I have the following spreadsheet which I attempting to calculate the Prior
Years Inventory Level in actual Retail $
I have a database query that retrieves the following Information:(
DeptDate( week end date(Saturday), OH Retail$, % Chg Prior Year), DeptNum
DeptName, CatName

(Dept = Abbreviation for Department)
DeptDate = (the date(Saturday) week ending (i.e. 5/10/08_
OH Retail$ = The Retail $ of Inventory for that department
OH %Chg = % chg from Prior Year (Don't have access to Original Data for a
certain Period of Time)
Deptnum = Department #
DeptName = Department Desc
CatName = Category Name (where different sets of deptnums are grouped)

The problem I am having is it doesn't seem to cross check the math.
I have a pivot table to display the OH$ , I then have 2 cols on the same
work sheet that calculates the prior years OH$
but when I cross check it doesn't seem to be right.

Col B (Calculated) has the following formula =G3/(1-(C3)*-1)
Col C = Contains the OH %CHG for each Dept/Cat for that week
COL D (Begins Pivot Table) = Contain Category Name
COL E = DeptNum
COL F = DeptName
COL G (Being Data) = Date on Column headers, and value of Inventory Level
The Problem occurs which I cross check the math.
COL A Contains =SUM(B3:B13)
If I summarize(COL A) the value in the range for each category (All Deptnum
in Category A for example it doesn't match)
am I doing something wrong Mathematically or is the data possibly bad? See
below for data sample
The issue is $104,007 with a (21.9)% chg = $133,182 not $126,785 the sum of
the individual changes
I have confirmed thet query data matches the database data

Thanks for any Help

A B C D
E F G
LY OnHand Retail$ DeptDate
5/3/08 Diff Category Dept# DeptDesc 5/3/2008
(row 3) $50,071 -40.90% Consumables 110 Food $29,592
$909 71.90% 111 DSD $1,563
$4,509 -45.40% 114 Speciality Food $2,462
$0 890.00% 115 Frozen Food
$43,248 -18.90% 120 HBC $35,074
$0 121 Control Sub
$5,110 -8.30% 130 House Keeping $4,686
$7,561 -14.90% 140 Household Chemicals $6,434
$4,811 -14.30% 150 Paper $4,123
$10,081 31.60% 160 Pet $13,266
$6,883 -1.10% 170 Home Organization $6,807
$133,182 $126,785 -21.90% Consumables Total $104,007

Reply to this Message

Kevin Labore - 07 May 2008 15:17 GMT

sorry data didnt format well , this is a little better

> A B C D E F
> G
[quoted text clipped - 20 lines]
> $6,883 -1.10% 170 Home Organization
> $6,807

$126,785 -21.90% Consumables Total
$104,007

>I have the following spreadsheet which I attempting to calculate the Prior
>Years Inventory Level in actual Retail $
[quoted text clipped - 50 lines]
> $6,883 -1.10% 170 Home Organization $6,807
> $133,182 $126,785 -21.90% Consumables Total $104,007

Reply to this Message

joeu2004 - 08 May 2008 06:11 GMT

> The issue is $104,007 with a (21.9)% chg = $133,182
> not $126,785 the sum of the individual changes

I don't see how you compute $126,785 as "the sum of
individual changes". But I do note that $126,792 is
about 104007*(1-(-21.91%)), where the total declined
by 21.91% from $133,182 to $104,007.

I assume that is the gist of your mistake.

For example, if Food declined by 40.90% from $50,071
to $29,592, you can compute $29,592 by 50071*(1-40.90%).
You can compute $50,071 by 29592/(1-40.90%), not
29592*(1-(-40.90%)).

I suspect you used the sum of the latter to compute
"the sum of the individual changes".

But you wrote:

> Col B (Calculated) has the following formula
> =G3/(1-(C3)*-1)

That's a perverse way of writing simply =G3/(1+C3). That seems to be
correct, if G3 is $29,592 and C3 is -40.90%, using my example above.

----- original posting -----

> I have the following spreadsheet which I attempting to calculate the Prior
> Years Inventory Level in actual Retail $
[quoted text clipped - 50 lines]
> $6,883 -1.10% 170 Home Organization $6,807
> $133,182 $126,785 -21.90% Consumables Total $104,007

Reply to this Message

Kevin Labore - 08 May 2008 11:37 GMT

Hi

Thanks for the reply , I changed the formulas to use PV/FV, since basically
that is what I am looking for. The cross check is pretty close ($200 or so
at times) but with the rounding factor that would be expected with #'s over
$100K. Your formula should give the same results. For some reason I
thought I had to do it the other.

Kevin

On May 7, 6:08 am, "Kevin Labore" <kev...@somewhere.net> wrote:

> The issue is $104,007 with a (21.9)% chg = $133,182
> not $126,785 the sum of the individual changes

> Col B (Calculated) has the following formula
> =G3/(1-(C3)*-1)

That's a perverse way of writing simply =G3/(1+C3). That seems to be
correct, if G3 is $29,592 and C3 is -40.90%, using my example above.

----- original posting -----

On May 7, 6:08 am, "Kevin Labore" <kev...@somewhere.net> wrote:

> I have the following spreadsheet which I attempting to calculate the Prior
> Years Inventory Level in actual Retail $
[quoted text clipped - 52 lines]
> $6,883 -1.10% 170 Home Organization $6,807
> $133,182 $126,785 -21.90% Consumables Total $104,007

Reply to this Message

joeu2004 - 08 May 2008 18:05 GMT

> I changed the formulas to use PV/FV, since basically
> that is what I am looking for.

If you want the percentage change, the formula should
be PV/FV - 1 (%change from FV to PV) or FV/PV - 1
(%change from PV to FV), formatted as Percentage. The
latter is consistent with the table you posted.

PV/FV and FV/PV yield muliplier factors (normally
formatted as Number or General), not percentage
change.

----- original posting -----

> Hi
>
[quoted text clipped - 93 lines]
> > $6,883 -1.10% 170 Home Organization $6,807
> > $133,182 $126,785 -21.90% Consumables Total $104,007- Hide quoted text -

Reply to this Message