Best Practice - Design Question

[Alan]

Hi All,

Bear with me on this!

I have a large workbook that we use for calculating financial
forecasts for a fleet hire business.

The workbook contains a total of 300 vehicles, and I need to do
calculations of revenue across five years (60 monthly buckets).

Currently there are four revenue lines for each vehicle for each month
and I am doing the following calculation for each:

1) Check that the vehicle has been acquired (picked up from an asset
sheet) - Boolean
2) Check that the vehicle has not been disposed (also from asset
sheet) - Boolean
3) Occupancy rate - Percentage
4) Days in month - Days
5) Daily average rate - $ / day

Multiply those five together to get a $ figure for the month across
the 300 vehicles.

The first four of those are the same for each revenue line (consider
them to be Hire, Extras, Insurance Waiver, and Other Bookings).

Obviously each calculation grid is big (300 x 60 = 18,000
calculations).

I could add another calculation area to do just the first four items,
and then refer to that and bring in the fifth item.

If I did that, it would obviously create an additional 18,000
calculation cells, but it would (theoretically at least) reduce the
actual number of calculations for excel by 3 x 18000 = 54,000 making a
net saving of 36,000 calcs.

However, it would also increase the dependency chain for each of the
final calculations by 1.


Question: Is it better to pull the common calculation out of the
other ones, and refer to it (feels like the right thing to do) or
should I prefer a smaller number of calculation cells?

Thanks,
--
Alan.

The views expressed are my own, and not those of my employer or anyone
else associated with me.

My current valid email address is:

(e-mail address removed)

This is valid as is. It is not munged, or altered at all.

It will be valid for AT LEAST one month from the date of this post.

If you are trying to contact me after that time,
it MAY still be valid, but may also have been
deactivated due to spam. If so, and you want
to contact me by email, try searching for a
more recent post by me to find my current
email address.

The following is a (probably!) totally unique
and meaningless string of characters that you
can use to find posts by me in a search engine:

ewygchvboocno43vb674b6nq46tvb

 

[Anne Troy]

Hi, Alan. We discuss Best Practices at the link below if you'd like to pose
your question there:
http://www.proofficedev.com/ ...especially since I don't see that anyone has
yet responded here.

****************************
Hope it helps!
Anne Troy
www.OfficeArticles.com
****************************

 

[Roger Govier]

Hi Alan

Since, when you multiply Boolean values together, the True's are coerced
to 1's and the False's to 0's, then
=A1*B1*C1*D1*E1
doesn't seem too much of a problem to me, even if you do have many rows.

 

[Anne Troy]

Hi, Roger, darling.
So nice to SEE you!
****************************
Hope it helps!
Anne Troy
www.OfficeArticles.com
****************************

 

[Alan]

Hi Roger,

Individually, it is not much of an issue, but when multiplied up over
many sections it probably get magnified.

As is, the workbook is currently around 38Mb mainly in those
calculations.

Of course, there is an argument that we should migrate on from excel,
but for now that option is off the table and I need to optimise what I
have.

Thanks,
--
Alan.

The views expressed are my own, and not those of my employer or anyone
else associated with me.

My current valid email address is:

(e-mail address removed)

This is valid as is. It is not munged, or altered at all.

It will be valid for AT LEAST one month from the date of this post.

If you are trying to contact me after that time,
it MAY still be valid, but may also have been
deactivated due to spam. If so, and you want
to contact me by email, try searching for a
more recent post by me to find my current
email address.

The following is a (probably!) totally unique
and meaningless string of characters that you
can use to find posts by me in a search engine:

ewygchvboocno43vb674b6nq46tvb

 

[Roger Govier]

Hi Alan

Having slept, further thoughts on your problem.
I'm not absolutely sure that I understand your layout. If you have 4
revenue rows per asset, isn't your matrix 1200 rows by 65 columns?

Anyway, the way I think I would tackle it would be as follows.
On the sheet with Asset data, I would have a table with Asset name,
Acquisition Date and Disposal date in the form

AAAA 200408 200712
BBBB 200606 200901
This I would set up as a Named range, Insert>Name>Define> Name Asset
Refers to = OFFSET(Sheet2!$A$1,0,0,COUNTA(Sheet2!$A:$A),3)

On my Main sheet
A2: Asset Name B2:Utilisation C2: Daily Rate
D1 First Month of 60 as an Excel date 01/01/2007 formatted however you
wish e.g. Jan 07
Continue Calendar dates across row 1 for a further 59 columns.
In D2 Number of days per month, again continued across

I would then set up 3 named formulae, Insert>Name>Define>
Name xst1
Refers to =VLOOKUP(Sheet2!$A3,Asset,2,0)<=--(TEXT(Sheet2!D$1,"yyyymm"))
Name xst2
Refers to =VLOOKUP(Sheet2!$A3,Asset,3,0)>=--(TEXT(Sheet2!D$1,"yyyymm"))
Name Calc
Refers to =xst1*xst2*Sheet2!$B3*Sheet2!$C3

Then in D3 the formula would be
=Calc*D$2
Copied across and down as required.

 

[Roger Govier]

Hi Anne

Nice to see you back in the Ng's again.

 

[Alan]

Hi Roger,

My fault - I didn't go into much detail about the layout.

We have 300 individual assets, all of which are financially material
so we need to model them all separately.

That gives me 300 rows per 'table'.

The tables are all layed out with the 300 rows and 60 columns (5 years
x 12 months). Each table therefore contains about 18,000 cells.

There is one table for each required calculation (purchase price,
disposal value, depreciation, revenue1, revenue2, revenue3, revenue4,
cost1, cost2, finance raised, finance cleared, finance - repayments,
finance interest, .... etc)

Each of the tables yields figures that are needed separately (in total
for the month) elsewhere.

However, if we just look at the revenue tables (four tables of out of
about 20 altogether) then the calculuation for revenue1 looks like
this (in English):

(Acquired by this month)*(Disposed of after this month)*(Utilisation %
in this month)*(Days in this month)*(Revenue1 Rate)

Revenue2 looks like this:

(Acquired by this month)*(Disposed of after this month)*(Utilisation %
in this month)*(Days in this month)*(Revenue2 Rate)

....

Revenue4 looks like this:

(Acquired by this month)*(Disposed of after this month)*(Utilisation %
in this month)*(Days in this month)*(Revenue4 Rate)


These are generally all array formulae, but I don't think that matters
for this discussion (but I mention it in case it does!)

As you'll notice, there is commonality up to the point of multiplying
by the daily rate.


Therefore, I could add another (master revenue) table that does this:

(Acquired by this month)*(Disposed of after this month)*(Utilisation %
in this month)*(Days in this month)

to give me effective revenue days in the months.

I could then amend each of the four revenue tables to look like this:

(Master Revenue Table Effective Days)*(Revenue1 Rate)
(Master Revenue Table Effective Days)*(Revenue2 Rate)
....


Downside = I now have five tables instead of four, and therefore
18,000 more cells to calculate.

Upside = The core calculation is done once, rather than four times.


Which is better practice? I am coming down on the side of the five
tables, since each individual calculation is now simpler (prefer more
calcuation cells, each of which is simpler).

A simplistic example would be:

Prefer this:

A1 = 12 (Units)
A2 = 3 ($ Rate per each)
A3 = 10% (Discount for bulk of 10 or more)
A3 = A1 * A2 (Gross Extension)
A4 = If(A1>10,A1*A3,0) (Discount)
A5 = A3-A4 (Net after any discount)

Over this:

A1 = 12
A2 = 3
A3 = 10%

A5 = If(A1>10, (A1 * A2* (1-A3)) , A1*A2)

on the grounds that it is easier to understand each calculation and
less likely to go wrong.

Obviously at such a simple level the issue is not significant, but
writ large it matters much more.


Thanks for walking with me on this!

--
Alan.

The views expressed are my own, and not those of my employer or anyone
else associated with me.

My current valid email address is:

(e-mail address removed)

This is valid as is. It is not munged, or altered at all.

It will be valid for AT LEAST one month from the date of this post.

If you are trying to contact me after that time,
it MAY still be valid, but may also have been
deactivated due to spam. If so, and you want
to contact me by email, try searching for a
more recent post by me to find my current
email address.

The following is a (probably!) totally unique
and meaningless string of characters that you
can use to find posts by me in a search engine:

ewygchvboocno43vb674b6nq46tvb

 

[Roger Govier]

Hi Alan

Thank you for the detailed explanation.
I had not realised that the utilisation varied by month, and by Asset.

You mention

These are generally all array formulae, but I don't think that matters
for this discussion (but I mention it in case it does!)

Array formulae are notoriously processor hungry, and would have a huge
impact upon calculation speed.
There is no need for array formulae. None of those I have posted are
array formulae, and neither do they need to be.
Post an example of one of the array formulae you are using, as it can
almost always be done in a less "processor hungry" way.

I think I would therefore name each table of 300 x 60
I would have one called
Present, which is basically a truth table populated with 1's and 0's
utilising the method I suggested for determining whether the asset was
purchased before month and disposed after month.
Monthdays with the number of working days for the months for each asset
Utilised
Prices

I would have named formulae such as
Days = INDEX(Present,Row(),Column())*INDEX(Monthdays,Row(),Column())
Price = INDEX(Prices,Row(),Column())
Utilisation =INDEX(Utilised,Row(),Column())

The formula then becomes
=Price*Days*Utilisation
where days are only applicable if the asset is present.

All the values are being plucked off the various matrices relative to
Row (Asset) and Column (Month)
Nothing complicated, and just as simple as the first formula in my first
posting.

Easy to read, easy to maintain.

Effectively, by using Named formulae, you will have created your extra
calculation Table, without physically doing so.

If you have difficulty with the concept, mail me direct (remove NOSPAM
from my email address) and I will send you a small sample file.