Convert Turn Rate to Weekly Supply

J

Joe

A1 = Monthly Inventory Value of 12
A2 = Monthly Cost of Goods Sold of 3
A3 = =A1*12/A2 [this give me the turn rate of: 3

To calculate the weeks supply of product I would use the following formula:
=A1/SUM(SUM(A2*12)/52) [this gives me 17.333 weeks supply]

It would be helpful to have a calculation that could arrive at the weeks
supply without having to have access to both the Inventory Value or the Cost
of Goods Sold.

Is it possible to calculate the Weeks supply with only the turn rate and one
of the other two? In other words if all you had was the turn rate and the
inventory or just the turn rate and the cost of goods sold, could you come up
with the weeks supply?
 
D

David Biddulph

Perhaps, Joe, you could give us a clue what you intended the two SUM
functions to achieve in
=A1/SUM(SUM(A2*12)/52) when you haven't given the function a number of
arguments to sum?
Why not just =A1/((A2*12)/52) ?
If you don't understand what the SUM function does, look it up in Excel
help.
 
D

David Biddulph

David said:
Perhaps, Joe, you could give us a clue what you intended the two SUM
functions to achieve in
=A1/SUM(SUM(A2*12)/52) when you haven't given the function a number of
arguments to sum?
Why not just =A1/((A2*12)/52) ?

or, of course, =A1/(A2*12/52)
If you don't understand what the SUM function does, look it up in
Excel help.

Joe wrote: ....
To calculate the weeks supply of product I would use the following
formula: =A1/SUM(SUM(A2*12)/52) [this gives me 17.333 weeks supply]
....
 
J

Joe

I goofed and had one of the formulas backwards
Original: A3 = =A1*12/A2 [this give me the turn rate of: 3
Corrected: A3 = =A2*12/A1 [this give me the turn rate of: 3

I'm not sure if that had anything to do with it or not but weeks supply
using these numbers is 17.333. Your formula =A3/144*52 returns 1.0833.


Bob Phillips said:
=A3/144*52

--
__________________________________
HTH

Bob

Joe said:
A1 = Monthly Inventory Value of 12
A2 = Monthly Cost of Goods Sold of 3
A3 = =A1*12/A2 [this give me the turn rate of: 3

To calculate the weeks supply of product I would use the following
formula:
=A1/SUM(SUM(A2*12)/52) [this gives me 17.333 weeks supply]

It would be helpful to have a calculation that could arrive at the weeks
supply without having to have access to both the Inventory Value or the
Cost
of Goods Sold.

Is it possible to calculate the Weeks supply with only the turn rate and
one
of the other two? In other words if all you had was the turn rate and the
inventory or just the turn rate and the cost of goods sold, could you come
up
with the weeks supply?
 
J

Joe

Thank you for your help in simplifing =A1/SUM(SUM(A2*12)/52) but that really
is not the problem I am trying to solve.

I am trying to come up with a formula that will allow me to back into the
weeks supply without have to have both the inventory and the cost of goods
sold at the same time by using the turn rate as a substitute for one of them.

Thanks

David Biddulph said:
David said:
Perhaps, Joe, you could give us a clue what you intended the two SUM
functions to achieve in
=A1/SUM(SUM(A2*12)/52) when you haven't given the function a number of
arguments to sum?
Why not just =A1/((A2*12)/52) ?

or, of course, =A1/(A2*12/52)
If you don't understand what the SUM function does, look it up in
Excel help.

Joe wrote: ....
To calculate the weeks supply of product I would use the following
formula: =A1/SUM(SUM(A2*12)/52) [this gives me 17.333 weeks supply]
....
 
B

Bob Phillips

You will need to spell it all out again because none of it makes any sense
now. The formula you give now returns 3 not 17.333.

--
__________________________________
HTH

Bob

Joe said:
I goofed and had one of the formulas backwards
Original: A3 = =A1*12/A2 [this give me the turn rate of: 3
Corrected: A3 = =A2*12/A1 [this give me the turn rate of: 3

I'm not sure if that had anything to do with it or not but weeks supply
using these numbers is 17.333. Your formula =A3/144*52 returns 1.0833.


Bob Phillips said:
=A3/144*52

--
__________________________________
HTH

Bob

Joe said:
A1 = Monthly Inventory Value of 12
A2 = Monthly Cost of Goods Sold of 3
A3 = =A1*12/A2 [this give me the turn rate of: 3

To calculate the weeks supply of product I would use the following
formula:
=A1/SUM(SUM(A2*12)/52) [this gives me 17.333 weeks supply]

It would be helpful to have a calculation that could arrive at the
weeks
supply without having to have access to both the Inventory Value or the
Cost
of Goods Sold.

Is it possible to calculate the Weeks supply with only the turn rate
and
one
of the other two? In other words if all you had was the turn rate and
the
inventory or just the turn rate and the cost of goods sold, could you
come
up
with the weeks supply?
 
J

Joe

Ok here it is:

A B C D
1
2 12 3 3 17.333

Cells A2 and B2 are values entered:
A2: 12
B2: 3

Cells C2 and D2 are formulas:
C2: =B2*12/A2 [which returns 3]
D2: =A2/(SUM(B2*12)/52) [which returns 17.333]

A2 = Inventory
B2 = Cost of Goods Sold
C2 = Turn Rate
D2 = Weeks Supply

Is it possible to come up with D2 (Weeks Supply) by using only

A2 (Inventory) and C2 (Turn Rate)

or by using only

B2 (Cost of Goods Sold) and C2 (Turn Rate)?


Bob Phillips said:
You will need to spell it all out again because none of it makes any sense
now. The formula you give now returns 3 not 17.333.

--
__________________________________
HTH

Bob

Joe said:
I goofed and had one of the formulas backwards
Original: A3 = =A1*12/A2 [this give me the turn rate of: 3
Corrected: A3 = =A2*12/A1 [this give me the turn rate of: 3

I'm not sure if that had anything to do with it or not but weeks supply
using these numbers is 17.333. Your formula =A3/144*52 returns 1.0833.


Bob Phillips said:
=A3/144*52

--
__________________________________
HTH

Bob

A1 = Monthly Inventory Value of 12
A2 = Monthly Cost of Goods Sold of 3
A3 = =A1*12/A2 [this give me the turn rate of: 3

To calculate the weeks supply of product I would use the following
formula:
=A1/SUM(SUM(A2*12)/52) [this gives me 17.333 weeks supply]

It would be helpful to have a calculation that could arrive at the
weeks
supply without having to have access to both the Inventory Value or the
Cost
of Goods Sold.

Is it possible to calculate the Weeks supply with only the turn rate
and
one
of the other two? In other words if all you had was the turn rate and
the
inventory or just the turn rate and the cost of goods sold, could you
come
up
with the weeks supply?
 
F

Fred Smith

To calculate weeks' supply from turn rate, use:

=52/a3

Where a3 is your turn rate. You don't need either inventory or COGS.

Regards,
Fred.
 
B

Bob Phillips

Try

=52/C2

--
__________________________________
HTH

Bob

Joe said:
Ok here it is:

A B C D
1
2 12 3 3 17.333

Cells A2 and B2 are values entered:
A2: 12
B2: 3

Cells C2 and D2 are formulas:
C2: =B2*12/A2 [which returns 3]
D2: =A2/(SUM(B2*12)/52) [which returns 17.333]

A2 = Inventory
B2 = Cost of Goods Sold
C2 = Turn Rate
D2 = Weeks Supply

Is it possible to come up with D2 (Weeks Supply) by using only

A2 (Inventory) and C2 (Turn Rate)

or by using only

B2 (Cost of Goods Sold) and C2 (Turn Rate)?


Bob Phillips said:
You will need to spell it all out again because none of it makes any
sense
now. The formula you give now returns 3 not 17.333.

--
__________________________________
HTH

Bob

Joe said:
I goofed and had one of the formulas backwards
Original: A3 = =A1*12/A2 [this give me the turn rate of: 3
Corrected: A3 = =A2*12/A1 [this give me the turn rate of: 3

I'm not sure if that had anything to do with it or not but weeks supply
using these numbers is 17.333. Your formula =A3/144*52 returns 1.0833.


:

=A3/144*52

--
__________________________________
HTH

Bob

A1 = Monthly Inventory Value of 12
A2 = Monthly Cost of Goods Sold of 3
A3 = =A1*12/A2 [this give me the turn rate of: 3

To calculate the weeks supply of product I would use the following
formula:
=A1/SUM(SUM(A2*12)/52) [this gives me 17.333 weeks supply]

It would be helpful to have a calculation that could arrive at the
weeks
supply without having to have access to both the Inventory Value or
the
Cost
of Goods Sold.

Is it possible to calculate the Weeks supply with only the turn rate
and
one
of the other two? In other words if all you had was the turn rate
and
the
inventory or just the turn rate and the cost of goods sold, could
you
come
up
with the weeks supply?
 
J

Joe

Wow that is much simpler then I thought. You don't need the inventory or the
cost of goods sold as long as you have the turn rate. Thanks a Bunch!

Bob Phillips said:
Try

=52/C2

--
__________________________________
HTH

Bob

Joe said:
Ok here it is:

A B C D
1
2 12 3 3 17.333

Cells A2 and B2 are values entered:
A2: 12
B2: 3

Cells C2 and D2 are formulas:
C2: =B2*12/A2 [which returns 3]
D2: =A2/(SUM(B2*12)/52) [which returns 17.333]

A2 = Inventory
B2 = Cost of Goods Sold
C2 = Turn Rate
D2 = Weeks Supply

Is it possible to come up with D2 (Weeks Supply) by using only

A2 (Inventory) and C2 (Turn Rate)

or by using only

B2 (Cost of Goods Sold) and C2 (Turn Rate)?


Bob Phillips said:
You will need to spell it all out again because none of it makes any
sense
now. The formula you give now returns 3 not 17.333.

--
__________________________________
HTH

Bob

I goofed and had one of the formulas backwards
Original: A3 = =A1*12/A2 [this give me the turn rate of: 3
Corrected: A3 = =A2*12/A1 [this give me the turn rate of: 3

I'm not sure if that had anything to do with it or not but weeks supply
using these numbers is 17.333. Your formula =A3/144*52 returns 1.0833.


:

=A3/144*52

--
__________________________________
HTH

Bob

A1 = Monthly Inventory Value of 12
A2 = Monthly Cost of Goods Sold of 3
A3 = =A1*12/A2 [this give me the turn rate of: 3

To calculate the weeks supply of product I would use the following
formula:
=A1/SUM(SUM(A2*12)/52) [this gives me 17.333 weeks supply]

It would be helpful to have a calculation that could arrive at the
weeks
supply without having to have access to both the Inventory Value or
the
Cost
of Goods Sold.

Is it possible to calculate the Weeks supply with only the turn rate
and
one
of the other two? In other words if all you had was the turn rate
and
the
inventory or just the turn rate and the cost of goods sold, could
you
come
up
with the weeks supply?
 
J

Joe

Thanks Fred that is more simple then I thought. I appreciate your help.

Fred Smith said:
To calculate weeks' supply from turn rate, use:

=52/a3

Where a3 is your turn rate. You don't need either inventory or COGS.

Regards,
Fred.


Joe said:
A1 = Monthly Inventory Value of 12
A2 = Monthly Cost of Goods Sold of 3
A3 = =A1*12/A2 [this give me the turn rate of: 3

To calculate the weeks supply of product I would use the following
formula:
=A1/SUM(SUM(A2*12)/52) [this gives me 17.333 weeks supply]

It would be helpful to have a calculation that could arrive at the weeks
supply without having to have access to both the Inventory Value or the
Cost
of Goods Sold.

Is it possible to calculate the Weeks supply with only the turn rate and
one
of the other two? In other words if all you had was the turn rate and the
inventory or just the turn rate and the cost of goods sold, could you come
up
with the weeks supply?
 
F

Fred Smith

Yes. Basically the supply period is the reciprocal of the turn rate. Glad it
worked for you. Thanks for the feedback.

Regards,
Fred.

Joe said:
Thanks Fred that is more simple then I thought. I appreciate your help.

Fred Smith said:
To calculate weeks' supply from turn rate, use:

=52/a3

Where a3 is your turn rate. You don't need either inventory or COGS.

Regards,
Fred.


Joe said:
A1 = Monthly Inventory Value of 12
A2 = Monthly Cost of Goods Sold of 3
A3 = =A1*12/A2 [this give me the turn rate of: 3

To calculate the weeks supply of product I would use the following
formula:
=A1/SUM(SUM(A2*12)/52) [this gives me 17.333 weeks supply]

It would be helpful to have a calculation that could arrive at the
weeks
supply without having to have access to both the Inventory Value or the
Cost
of Goods Sold.

Is it possible to calculate the Weeks supply with only the turn rate
and
one
of the other two? In other words if all you had was the turn rate and
the
inventory or just the turn rate and the cost of goods sold, could you
come
up
with the weeks supply?
 
B

Bob Phillips

You just have to reverse engineer it.

--
__________________________________
HTH

Bob

Joe said:
Wow that is much simpler then I thought. You don't need the inventory or
the
cost of goods sold as long as you have the turn rate. Thanks a Bunch!

Bob Phillips said:
Try

=52/C2

--
__________________________________
HTH

Bob

Joe said:
Ok here it is:

A B C D
1
2 12 3 3 17.333

Cells A2 and B2 are values entered:
A2: 12
B2: 3

Cells C2 and D2 are formulas:
C2: =B2*12/A2 [which returns 3]
D2: =A2/(SUM(B2*12)/52) [which returns 17.333]

A2 = Inventory
B2 = Cost of Goods Sold
C2 = Turn Rate
D2 = Weeks Supply

Is it possible to come up with D2 (Weeks Supply) by using only

A2 (Inventory) and C2 (Turn Rate)

or by using only

B2 (Cost of Goods Sold) and C2 (Turn Rate)?


:

You will need to spell it all out again because none of it makes any
sense
now. The formula you give now returns 3 not 17.333.

--
__________________________________
HTH

Bob

I goofed and had one of the formulas backwards
Original: A3 = =A1*12/A2 [this give me the turn rate of: 3
Corrected: A3 = =A2*12/A1 [this give me the turn rate of: 3

I'm not sure if that had anything to do with it or not but weeks
supply
using these numbers is 17.333. Your formula =A3/144*52 returns
1.0833.


:

=A3/144*52

--
__________________________________
HTH

Bob

A1 = Monthly Inventory Value of 12
A2 = Monthly Cost of Goods Sold of 3
A3 = =A1*12/A2 [this give me the turn rate of: 3

To calculate the weeks supply of product I would use the
following
formula:
=A1/SUM(SUM(A2*12)/52) [this gives me 17.333 weeks supply]

It would be helpful to have a calculation that could arrive at
the
weeks
supply without having to have access to both the Inventory Value
or
the
Cost
of Goods Sold.

Is it possible to calculate the Weeks supply with only the turn
rate
and
one
of the other two? In other words if all you had was the turn rate
and
the
inventory or just the turn rate and the cost of goods sold, could
you
come
up
with the weeks supply?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top