Melhores praticas de modelagem em Excel

Prévia do material em texto

Designing Financial Models that Work design guidelines
Key idea: Models are broken up into “paragraphs”– a group of related ideas, surrounded by white space.
Each model paragraph begins with a clear title, which explains what the paragraph calculates, and ends with a clear conclusion, which shows the calculated result.
Excel techniques might prove useful:
Flip the sheet = CTRL ‐ ~. This toggles between formulas and values.
Show the blue arrows = click on the final result cell, and then click “Formulas” – “Trace Precedents” and keep clicking until no additional blue arrows appear. This shows computational flow. (Note – need ribbon un-minimized.)
See all input cells (should all be blue) = Go To – Special – Constants – Numbers
F5 – Go To – Special – Precedents – All levels 
5 guidelines for paragraph computational flow
Assumption numbers are shown in individual input cells, not “hard coded” into formula cells. (Input cells are cells containing only a number; formula cells are cells beginning with “=”.)
One operator per formula cell. “=SUM(B1:B5)” or “=B1*B2” or “=‐B3/B4” count as one operator; “=B1*B2*B3” does not.
Explain your math by indicating the operator at the beginning of the associated label cell, for example: “x Load factor”, “/ Hours per week”, “Less cost of goods sold”, “+ Cost of lemonade mix” and “= m Packaging cost per carton”. (You’ll need to put a single apostrophe in front so Excel interprets the operator as part of the label and not as a function or command.)
Computational flow is down and/or to the right only.
No FYI cells – all cells flow into the paragraph conclusion.
5 guidelines for paragraph formatting
On the Numbers tab of Format Cells, use only the following 3 formats: a) Number, with “Use 1000 separator (,)” checked and negative numbers shown as black and in parentheses, b) Currency, with symbol as “$” and negative numbers shown as black and in parentheses, and c) Percentage.
On the Alignment tab of Format Cells, use only the default of “General” (= left for text, right for #s.)
On the Font tab of Format Cells, use only the following 3 formats: a) Normal font + Bold, for paragraph titles and conclusions, b) Normal font + Color=blue, for input cells, and c) Normal font, for everything else.
On the Border tab of Format Cells, use only Bottom Border, for paragraph titles (stretching across all paragraph columns), and Top Border, for every result cell (stretching to the left to include the label cell.) Remove all other lines by unchecking Gridlines on the View tab of the Ribbon.
On the Fill tab of Format Cells, use only the default of No Color.
5 more design guidelines for tabs
Before closing, set up the spreadsheet to print one tab per page, landscape orientation.
Before closing, make the “active cell” something meaningful – for example, the page title.
Delete unused tabs.
Sequence model elements (paragraphs and calculations within each paragraph) intuitively – by size, by name, chronologically, etc.
Have a meaningful, consistent name for every variable calculated and a meaningful title for each paragraph and each tab. Avoid abbreviating, and if you must abbreviate, be absolutely consistent.
5 design guidelines for combining tabs into workbooks
Workbooks are made up of a small number (1‐6) of tabs that together tell a computational story leading into the Key Output Cell of the workbook.
Computational flow is as follows: the first tab is the “home page” showing the final computation of the KOC of the workbook. The home page draws some or all of its starting numbers from the other tabs, which are sequenced in an intuitive fashion, typically left to right in order of calculation flow.
Typically KOCs from the tabs flow either to subsequent tabs or directly to the home page – we highlight this linkage by using hyperlinks on the connected cells. (Reformat the hyperlinks by removing the underline.)
Repeat numbers as needed from previous tabs (“show local copies”) so the computational storytelling is extremely easy to follow.
All tabs should be visible at all times (hence the suggested limit of ~6 tabs) – if the workbook contents grows past this point, cleave it into two workbooks.
5 suggestions for finding your way around a foreign model
Click on File – Properties – Statistics to see when the file was created – expect that the older the file, the more convoluted it will have become.
See if you can identify the “Key Output Cell/s” for the spreadsheet – either by talking to people or by studying the format and architecture of the spreadsheet.
Once you’ve identified a KOC, try to eliminate everything in the model that isn’t part of that computational story. (The goal is to cleave the model up into independent chunks that can then be analyzed separately.)
Copy all parts of the remaining model onto one tab (using Cut & Paste) and then continue to focus only on relevant cells by clicking on the KOC and selecting Go To – Special – Precedents – All levels. You can then highlight these selected cells with a different cell background color, and delete all other cells, since they are not part of the computational story of the KOC.
Finally, select Go To – Special – Constants – Numbers to select all the input cells, and color them blue. Color all other cells black and remove any cell shading you added temporarily in step 4. You should now have only one main computational story, all on one tab, inputs in blue and calculations in black. You can use the blue arrows and Cut & Paste to rearrange model elements into an increasingly coherent computational flow that hopefully will be much easier to follow than the original model.
5 more design guideline suggestions
When a model is subtracting numbers, format them to be negative and use =SUM for the result formula.
Have an empty column in A and an empty row in 1 to provide a little white space around the tab title.
Make sure you are using royal blue (and not pale blue) for the inputs (this was a default color in Excel 2003 but is not in Excel 2007 so you’ll have it add it in) – you want them to really “pop”.
Do the simplest thing that works, part 1 – avoid spaghetti (= when you show the blue arrows, lots of arrows flowing from paragraph to paragraph) by locating numbers that depend on each other within the same paragraph, if possible.
Do the simplest thing that works, part 2 – it’s sometimes simpler to have your model user do some of the model’s calculations themselves on a calculator, and then enter the resulting number into one of your blue input cells (for example, entering in the days/high season rather than days/month and months/high season). This is simple, but it doesn’t “work” so it’s not the simplest thing that works – design the model such that the input cells are stated in the units that are most intuitive to your user.
Typographic Rules
No gridlines
No unneeded worksheets – and better names for the remaining sheets
No boxes or borders – w/ exception of a single underline underneath titles, subtitles, above totals
No color – except blue for inputs and pale gray background for key output cells
No double underlines, and no underlining using the “U” button
No “Accounting format” to indicate a number in dollars – use “Currency format” instead.
No unneeded numbers or spurious accuracy (e.g., don’t show .00 after every number – show only the digits that provide insight.)
Indicate negative using () not -.
No centering.
Numbers are right aligned, test is generally left aligned, column headings are aligned to match the column contents
Use Arial
Arial 10 point is our base font used for body text, Arial 10 bold is our next loudest “voice” used for “paragraph headings” and key output cells, and Arial 14 bold is our loudest voice, used for page titles
Use italics only to indicate an “FYI”
Xxx
Create hierarchy using the text size, bold – not using color, indents, colons, centering, capitalization, italics, double underlining
Capitalize only the first word in a name or title – nothingelse
Spell check every tab, and then manually spell check tab names
No wrapping of text
Use the 000’s separator
Create sharp right alignment by making sure that if there’s a blank space a the right end of some numbers for a right parenthesis if the number is negative, that everything else lined up with those number has a blank space at the right end as well
Change the default row height to 15 (for Arial 10)
Get it all in one “eye view” – if necessary, zoom down to 80% (but no smaller) and then if still doesn’t all fit on the screen (assuming a reasonable screen resolution) break the contents of that tab across multiple tabs
Use empty columns and rows to create white space around paragraphs, but don’t insert empty columns between the (monthly/annual/etc.) columns of a forecast
	
	Page 3