Home | About Us | Careers | Products | Press | Seller Resources | Blogs | FAQ | Contact Us

The problem with Excel models and how proper templates can help

Posted on August 28, 2016 at 4:11pm

Virtually everyone uses Excel models at some point in their careers. Some a lot. Clearly, many of these are of better quality than others, but what I’ve found over the course of the years is that often, even the good ones fall short in some very fundamental ways.  

Each of these flaws by themselves can seriously undermine the end result of a seemingly good model and together they can render it not just meaningless, but dangerous, because they can point you to conclusions that are very wrong and misleading.

In highly summarized form, these are the five danger areas that I’m talking about:

  1. Cause: Reinventing the wheel every time you create a new model. Effect: Disproportionate waste of preparer’s time; Inconsistency with logic and formulas, resulting in a higher risk of error.
  2. Cause: Spending too much time with preparation and not enough on analysis of results. Effect: You prepared what you believe to be a great model, but you spent more time on making it work and look good than you did on studying the results. At best you gave up an opportunity to really optimize your outcomes and at worst you missed a fundamental error or passed over a less than obvious conclusion.
  3. Cause: Looking in the wrong direction when calculating simple dependencies. For example, calculating future inventory levels based on historic cost of sales. Effect: Inaccurate inventory and working capital projections, with knock-on impact on ordering/production and cash management.
  4. Cause: Using averages. Effect: Seasonality, growth, or contraction are all effectively ignored; Problem areas and outliers go unnoticed.
  5. Cause: Ignoring obvious relationships. Some relationships are easily seen (albeit not well accounted for – see #3 & 4 above), such as the one between sales and accounts receivable, or between cost of sales and accounts payable or inventory, but what about others, such as size of the sales team (including ramp-up time of new sales people) in relation to sales projections? Someone in sales management may have this covered to some extent, but if it isn’t factored into the sensitivity analysis inherent in your model, then it’s being ignored. Effect: Projected outcomes become unachievable because of lack of proper resource matching.

You can’t and nor should you run your business on an Excel spreadsheet, but the reality is that a very large proportion of key decisions are still based on exactly this kind of analysis and that situation is unlikely to change anytime soon. The source data may have come from your ERP or CRM system, but the final decision still ends up being based on a spreadsheet model.

There are many ways to overcome these challenges, but I’m going to talk about just one for now.

Use templates that work properly and that integrate with one another. It’s as simple as that.

Of course there are still a myriad of ways to build bad models, but this step imposes consistency, granularity and appropriate management of cause and effect relationships. It also saves time and reduces building errors.

Speaking personally, I was definitely not immune to the challenges I’ve listed and that’s why I finally chose to develop a library of high-end financial modeling templates that I have used with great success and which I have now decided to market. These 60-month templates (also automatically summarized into 5 years) are not all free, although one critical foundational template is and very soon there will be others. The rest are available at a price that translates into a fraction of the time it would take to create them yourself, not to mention the enormous risk mitigation that goes with using something that is battle tested. There’s a good chance that they’re also more precise than what you’ve been using in the past, plus they’re incredibly easy to integrate into existing models.

Templates currently available are:

  • Self-balancing, integrated Balance Sheet, Income Statement and Cash Flow Statement, with simplified data input tab. (This one is free)
  • Accounts Receivable calculator (With look-back function that precisely calculates AR based on estimated days' sales outstanding, using actual month-by-month sales data and not averages)
  • Accounts Payable calculator (With look-back function that precisely calculates AP based on days' COS, overheads and capex outstanding per month on the same basis as described above)
  • Inventory calculator (with look-forward function that precisely calculates inventory based on estimated days of inventory required to support projected cost of sales, using actual month-by-month forward COS data and not averages)
  • Fixed asset calculator (Complete month-by-month table including opening cost, additions/disposals, depreciation calculation and net book value for up to 10 asset categories, plus a summary. It also has a built in function to prevent over-depreciation, a not uncommon problem with other simplistic models)
  • Loan calculator (For up to 5 mortgage type loans with fixed repayment terms, regardless of payment intervals. Includes a comprehensive summary that includes details of loan amortization, interest charge and allocation between long and short term liabilities.)
  • Deferred revenue calculator (Comprehensive revenue recognition model, including Balance Sheet reconciliation, for all the most common deferred revenue use cases. This one will save you days, not hours!)
  • Salesforce optimization model (Compares forecast revenue with sales team’s delivery capacity, based on defined sales quotas, quota achievement percentage and new salesperson’s ramp up time and percentage quota achievement by month.)
  • Bundled package with all templates above fully integrated.

 

These are only a start, with more to follow shortly.

These templates are way more granular than the many free ones you’ll find on the Internet. Plus, they’re designed to integrate with one another.

Best of all, they represent the ultimate in plug and play. Literally copy and paste the entire tab into your own model, link the input cells (all with instructions) to your source data and you're done. They are all fully tried and tested with companies of all sizes across multiple industries. They can all be acquired stand-alone or bundled.

Please contact me at gsk@swbell.net for more details or even if you just want a free copy of the integrated Balance Sheet, Income Statement and Cash Flow model.

 


About the Author

DataOptimize

I leverage more than 30 years as a C-level executive to design and build complex models for custom applications, often as a basis for investment or M&A transactions. I also develop data driven models to...

Industry: Financial Services
Area of Expertise:Data Analysis, Modeling & sale of high-end Excel templates
Service Region:Global



You must be logged in to comment on this blog post.


Comments

There are 16 comments posted on this article.

Avatar

Posted by rousejack on December 7, 2017 6:53am

[b][url=http://da.topgradeiwcwatches.top/]replreplika IWC ure
replika IWC ure


Avatar

Posted by ltrogdonmigue on November 8, 2017 12:16am

[b][url=http://www.watchprice.top/]dadaytona
rolex basel


Avatar

Posted by cialuopatri on November 5, 2017 7:17am

scarpe MBT in vendita
scarpe MBT in vendita
sconto di mbt


Kesho Casual MBT Donna MJ Luminoso Black Shoes - €148.80 : mbtshoes , mbtoutlet.top
























US Dollar
Euro
GB Pound
Canadian Dollar
Australian Dollar
Jappen Yen
Norske Krone
Swedish Krone
Danish Krone
CNY





Categorie

Scarpe MBT
Scarpe MBT Donna
Casual Scarpe MBT Donna
MBT Donna Sandali Scarpe
MBT Donna Sport Scarpe




Vetrina -   [vedi]
Kisumu Casual Black & amp MBT Donna ; rosa Scarpe€164.07  €153.45Risparmi: 6% scontoMBT Chapa Caviar Black Casual Shoes€189.50  €155.31Risparmi: 18% scontoCasual Scarpe Kaya Mary Jane Alls Marrone MBT Donna€198.21  €159.96Risparmi: 19% sconto




a casa :: 
Scarpe MBT Donna :: 
Kesho Casual MBT Donna MJ Luminoso Black Shoes































.jqzoom{

float:left;

position:relative;

padding:0px;

cursor:pointer;
width:301px;
height:300px;
}































Kesho Casual MBT Donna MJ Luminoso Black Shoes


€160.85  €148.80Risparmi: 7% sconto




Fai la scelta:



Size


-- Please Select --
EU 35 2/3US 6UK 3234MM
EU 35US 5.5UK 2.5230MM
EU 36 1/3US 6.5UK 3.5239MM
EU 37 2/3US 7.5UK 4.5248MM
EU 37US 7UK 4243MM
EU 38 1/3US 8UK 5252MM
EU 39 2/3US 9UK 6261MM
EU 39US 8.5UK 5.5257MM
EU 40 1/3US 9.5UK 6.5266MM
























Da inserire:












Scarpe militari MBT Kesho MJ donne è una vera sintesi di funzionalità e stile . Non solo ha una buona perfprmance a migliorare la postura e comfort ad ogni passo , ma anche può abbinare con tutto il vostro abbigliamento in stile casual .
Caratteristica :
- Tomaia in morbida pelle con cinturino chiusura a gancio - e - loop per un facile on / off .
- Eccezionalmente morbido , fodera in microfibra calzino conduttore umidità e pelle sottopiede spaccato fornire usura traspirante .
- Un TPU e fibra di vetro gambo aggiunge fermezza alla costruzione unica ed assicura anche un movimento naturale rotolamento del piede per una distribuzione uniforme del peso .
- Ha una suola in gomma protetta che offre un supporto leggero e durata .
















Related Products




MBT Sandali Kisumu Liger pulsante - Symphony Fitnessv Black Shoes



/ Uomo MBT Donna Sport Blood Red Shoes



/ Uomo MBT Donna Sport Maliza Deep Black Shoes



Casual Kaya Cioccolato Scarpe MBT Donna

































Clienti che hanno acquistato questo prodotto hanno comprato anche ...Casual Scarpe Fora Bianco MBT Donna




















MBT scarpe in vendita con spedizione gratuita
MBT Shoes,Cheap MBT Shoes,MBT Shoes Clearance
Benvenuto nel nostro deposito di scarpe MBT, il nostro negozio offre una vasta scelta diMBT shoescon il prezzo più poco costoso e garanzia di alta qualità!


CATEGORIE

Scarpe Uomo
Scarpe per le donne




Servizio Clienti

Spedizione e ritorno
Contattaci
Mappa del sito




Modalità di pagamento100% SICURO






Copyright u0026 copia; 2014MBT Shoes OutletTutti i diritti riservati.

















scarpe MBT scarpe
scarpe MBT in vendita
MJ blog Scarpe About mbtoutlet.top blog


Avatar

Posted by cialuopatri on November 3, 2017 2:38pm

[b][url=http://www.moncler2018.co/mens-moncler-coats-c-7.html]Mens Moncler Coats columbia[/url][/b]
[b][url=http://www.moncler2018.co/mens-moncler-coats-c-7.html]Mens Moncler Coats for cheap[/url][/b]
red and normally DSL filters are also needed.It depends upon which method you have chosen for application.
The DSL service could be installed working with any one of two methods:divided, or with no splitter.Just in case the split method is place in use, you don't have for a DSL filter.In case you have chosen split strategy then you don't need a DSL narrow.However, a technician needs to come to your place to fit a splitter in the main telephone cable that will provide people the expert services.Two lines come out of the splitter.One of Mens Moncler Coats columbia
Mens Moncler Coats for cheap
red and normally DSL filters are also needed.It depends upon which method you have chosen for application.

The DSL service could be installed working with any one of two methods:divided, or with no splitter.Just in case the split method is place in use, you don't have for a DSL filter.In case you have chosen split strategy then you don't need a DSL narrow.However, a technician needs to come to your place to fit a splitter in the main telephone cable that will provide people the expert services.Two lines come out of the splitter.One of them feeds the actual RJ11 phone jacks, and also the other can be used solely for that DSL modem.You do not need a filter in this instance.The only disadvantage is how the technician needs to be called within.

Within the second approach to installation technician's visit isn't required.You are able to install it yourself.The company sends you a modem which has fitters in it.The DSL company goes via the primary service line and that means you only have to connect this DSL modem to be able to any old RJ11 retaining wall jack.But because the phone is servicing numerous areas for instance your fax appliance so there might be noise.Filters are then used in these cases to get rid off disturbances.

A DSL filter is really a small matchbox-like gadget that's connected concerning both finishes to RJ11 fittings.All how the subscriber needs to do is simply unplug the telephone line with the wall jack and stick in the filter to the RJ11 jack.He may then plug the telephone line back into the pool filter.It can also be required with regard to Fax system lines as well as answering piece of equipment lines

You might surf the web using DSL company and talk on the phone simultaneously.

DSL filters aren't costly devices and you should use them to protect on your own from disturbance and disturbance from cellular phone devices in your office or maybe house.Thus it is necessary for just about all phones to possess a line filtration system placed somewhere between them and the wall shop.


Avatar

Posted by rousejack on October 16, 2017 2:29am

[b][url=http://www.relicwatches.pw/es/news/] relo relojes falsos
relojes rolex barato


Avatar

Posted by cialuopatri on October 6, 2017 1:39pm

[b][url=http://www.montblanc-pen.net/es/montblanc-boheme-c-2.html]Montblanc bMontblanc boheme pluma<br>
Montblanc boheme pluma para la venta<br>


Avatar

Posted by sickesmike on September 26, 2017 7:19pm

[b]rorolex Yacht-Master II
replica watches


Avatar

Posted by cialuopatri on September 24, 2017 9:06am

[b][url=http://www.christianlouboutinmensshoes.top]christian lchristian louboutin online
cheap christian louboutin shoes


Avatar

Posted by cialuopatri on September 24, 2017 1:30am

[b][url=http://www.patekiwc.cn/pt/]IWC Spitfire ChIWC Spitfire Chronograph Mens Watch
Mens relógio IWC pilot


Avatar

Posted by cialuopatri on August 14, 2017 9:56pm

[b][url=http://www.timberlandsaleformen.top/da/]sort tisort timberland støvle
hvide timberland støvler


Avatar

Posted by cialuopatri on June 8, 2017 4:03am

[b][url=http://www.datejustrolexwatches.top/no/]rolerolex damer klokker
billige Rolex klokker


Avatar

Posted by ltrogdonmigue on May 20, 2017 10:37pm

[b]prada prada handbags sale
prada handbags outlet


Avatar

Posted by cialuopatri on April 24, 2017 12:29pm

[b]pandora 14k gold charms for necklaces[/b]
[b][url=http://www.pandorajewelry.me/pandora-14k-gold-charms-c-4.html]pandora 14k gold charms for bracelets[/url][/b]
the Interchange quotes.These include the fees in which companies for instance Visa, Master Card whilst others impose for payments processed by the merchant.There are a lot of sections or levels within the interchange speed and each visa or mastercard will possibly be charged good tier or simply level the application qualifies regarding.
Just like:
1.Qualified amount - It is the rate charged if your card is present physically inside the point from sale.
three.Mid-qualified fee - It is the rate charged if your card isn't present inside the point connected with sale; preferably, the vendor takes a great imprint belonging to the card.
3.Non-qualified charge - It is the rate incurred for "MOTO" trades (mail, The web or phone order).
What's more, there are numerous fees which can be attached to your merchant data upfront and / or as back-end prices.
Listed below are the most frequently found fees you could encounter:
1.Transaction Extra fees - this can be charged when a transaction might be completed.It's a flat price, usually from $0.10 to help you $0.25
three.Discount Amount - it's a flat-rate percentage that's taken out of each deal processed on the merchant profile.Discount rates cover anything from 0.95% for you to 7% plus, depending about the merchant service and your intended implement.
3.Authorizatiopandora 14k gold charms for necklaces
pandora 14k gold charms for bracelets
the Interchange quotes.These include the fees in which companies for instance Visa, Master Card whilst others impose for payments processed by the merchant.There are a lot of sections or levels within the interchange speed and each visa or mastercard will possibly be charged good tier or simply level the application qualifies regarding.

Just like:

1.Qualified amount - It is the rate charged if your card is present physically inside the point from sale.

three.Mid-qualified fee - It is the rate charged if your card isn't present inside the point connected with sale; preferably, the vendor takes a great imprint belonging to the card.

3.Non-qualified charge - It is the rate incurred for "MOTO" trades (mail, The web or phone order).

What's more, there are numerous fees which can be attached to your merchant data upfront and / or as back-end prices.

Listed below are the most frequently found fees you could encounter:

1.Transaction Extra fees - this can be charged when a transaction might be completed.It's a flat price, usually from $0.10 to help you $0.25

three.Discount Amount - it's a flat-rate percentage that's taken out of each deal processed on the merchant profile.Discount rates cover anything from 0.95% for you to 7% plus, depending about the merchant service and your intended implement.

3.Authorization Costs - this kind of fee is definitely charged whenever the transfer is ready-made.Even if your transaction will be denied, that fee is actually charged.

contemplate.Statement Rates - it's a fee that may be charged routinely for that generation from the monthly operation record.

5.Monthly The very least Fees - wish to have a minimum amount fee charged once the total fees of the particular month to get a given seller account never equal your minimum mandatory business operation level.Just like, if a minimum monetary fee is $15 plus the fees accrued from while using service are simply $10, then one other $5 are going to be charged for your month.

6.Termination Rates - it's a penalty which could range by $150 -- $275 if for example the merchant (potentially just one like you) ends their account ahead of the term expires, which is generally around 2 to 3 years.

7.100 % free Equipment - the terminals, machines along with other equipment actually are "given" just about always needs to be returned for the merchant bank account provider towards the end of the concept of a in most suitable condition.It expenses approximately $295 - $475 to restore (or pay back for) your damaged "free" airport.

8.Batch Costs - because a merchant forms their port a bill is priced.This generally is a flat-rate charge every time the critical is compensated.

As you possibly can see, there are a lot of expenses which will be incurred for just about any standard supplier account.There is absolutely no way everyone can supply you with a free merchant account, because your fees set in place because of the issuing greeting card companies have to be paid by way of someone, and some other person will not necessarily be prepared pay for you to use their system at their unique cost.

The truth is, these fees would be extracted around another manner, most which would always be indirect plus "hidden".And in that light, it's always better to choose a company that evidently discloses the nation's fee shape up-front, instead of being "lured in" in the premise of getting something with regard to "free" - just to find you the victim associated with an endless barrage for hidden prices...

Though they're hard to find, there undoubtedly are a few vendor services that include a superb pricing plan with a low-cost monetary fee structure.You'll be far greater off looking into those sorts of offers, instead of chasing after the "free offers".

Bottom line:Free reseller accounts tend not to exist.

It will cost you dollars to process debit card payments, one way or another.Always read the fine impress (the terminology and conditions), and realize that most merchant agencies that advertise something as being "free" will always make their revenue by locking you to a steep long term contract of some kind - most of which really are enforced which has a steep cancellation bill.

So do your due diligence, and look for reasonable, realistic business account fees that make sense for your personal business requires.

Because the unusually "cheap" reseller account will almost certainly end up as a possible incredibly highly-priced mistake...


Avatar

Posted by mblesdian on March 5, 2017 10:38am

[b][url=http://www.fakeluxurywatches.co/es/]réplicas deréplicas de relojes suizos aaa +
réplicas de relojes suizos


Avatar

Posted by ltrogdonmigue on February 26, 2017 8:23pm

[b][url=http://www.spyderwomensjacket.top/da/]spyder spyder sweater jakke
spyder jakke til kvinder


Avatar

Posted by ltrogdonmigue on December 20, 2016 2:04pm

[b]stylos[/b]
[b]stylos mont blanc[/b]
Montblanc | stylo Montblanc | Mont-Blanc

Meisterstuck roller : rabais stylos Mont-Blanc à vendre , Mont Blanc Stylos .






















US Dollar
Euro
GB Pound
Canadian Dollar
Australian Dollar
Jappen Yen
Norske Krone
Swedish Krone
Danish Krone
CNY





Catégories

MontBlanc Princesse Grace
MontBlanc Johannes Brahms
Meisterstuck roller
Montblanc Boheme
Montblanc Etoile
Montblanc Greta Garbo
Montblanc Meisterstück
Montblanc Starwalker




Les meilleures ventes



Montblanc Meisterstück Solitaire Or & Noir Rollerball Pen [792c] €625.89  €95.79Economie : 85% Montblanc Meisterstück Montblanc diamant Classique roller Pe [6a58] €618.45  €96.72Economie : 84%





A la une -   [plus]
Montblanc Starwalker Résine Fineliner [1ee3]€764.46  €105.09Economie : 86%Mont Blanc Starwalker Rouge Or Métal Stylo à bille [2a50]€872.34  €96.72Economie : 89%Montblanc Starwalker Doué Stylo à bille [dc96]€873.27  €106.95Economie : 88%




Accueil :: 
Meisterstuck roller









Meisterstuck roller




Filter Results by:
Articles commençant par…
A
B
C
D
E
F
G
H
I
J
K
L
M
N
O
P
Q
R
S
T
U
V
W
X
Y
Z
0
1
2
3
4
5
6
7
8
9






Affiche 1 à 15 (sur 24 articles)
 1  2  [Suiv >>] 


Montblanc Meisterstück carbone et Acier Rollerball Pen [af47]€622.17  €91.14Economie : 85%
Montblanc Meisterstück Céramique Noire Prisma Rollerball Pen [b8c2]€613.80  €94.86Economie : 85%
Montblanc Meisterstück Classique Rollerball Pen [980e]€618.45  €93.93Economie : 85%
Montblanc Meisterstück Diamant Rollerball Pen [c268]€616.59  €96.72Economie : 84%
Montblanc Meisterstück Doué Acier inoxydable Rollerball Pen [cf9a]€621.24  €96.72Economie : 84%




botChecker Link