1. science
2. social science
3. history

FINANCIAL GENOME CONSULTANT TUTORIAL
Sample Project Work Flow
A step-by-step guide for using Financial Genome
to build your custom financial forecasting model
This tutorial provides the basic workflow steps for building a forecasting model with Financial Genome
Consultant Edition using Excel 2007. The completed forecasting model will provide you with a
customized set of integrated financial statements (Income Statement, Balance Sheet, Cash Flow
Statement, Ratios Statement) based on GAAP (Generally Accepted Accounting Principles). The model
design provides full “cash closure” at all times by reflecting the cumulative impact of your assumptions
on the cash balance.
The Excel 2007 version of Financial Genome (FG) employs Microsoft’s new “fluent ribbon interface”
with tabs (designated here as {tab}) and buttons in groups. The Excel 2003 version has a different
interface using menus and toolbars, but operates on the same principles described here (see Appendix C).
To start Financial Genome, click the {Financial Genome} tab in the Microsoft Excel menu, then click
the “Financial Genome Environment” button. You will see the three Modernsoft tabs displayed: {Home},
{Model}, and {Format}. Additional tabs may appear if you have other addins installed (e.g., Acrobat).
.
NEED HELP?
Please consult the extensive help system for a detailed explanation of all Financial Genome
features. The Help button appears on the {Home} and {Model} tabs.
Provides a menu of help topics relevant to your key tasks. The “How Do I?” menu
appears on the {Home} and {Model} tabs.
Provides detailed definition and relationships on any given term. The “Explain Term” button
appears on the {Model} tab.
Page 1 of 41
2009-7-1
DOWNLOAD AND INSTALLATION
Requirements:
Excel 2007 (US Version)
Windows Vista (32-bit version) or Windows XP (US Version)
Single user stand-alone computers (non-server based machines)
Installation of FG application on C:\ drive (uninstall all previous versions before installing new version)
Download the correct version of FG depending on which version of Excel you are using. This tutorial
applies to FGSetup2007c. All versions are compatible with both Windows Vista and Windows XP.
When you install FG and run it for the first time, you are asked to enter the Free Trial Activation Code
to start your 30 day free trial (that’s the code sent to you in an email after you downloaded the software).
NOTE: FREE TRIAL ACTIVATION CODE is different from the PURCHASE REGISTRATION
CODE (see below).
During the trial period, the system will ask each time it is started whether you are ready to enter the
purchase registration code. If you have NOT purchased the product, indicate NO and skip this step.
After 30 days, you must purchase a code from our website www.modernsoft.com to use the product.
After you have purchased the product and received the Purchase Registration Code from us, you should
enter the code by clicking {Home/Guidance}>Registration to access the registration window.
Installation Guidance:
Our web site (http://www.modernsoft.com/Install.htm) has a Support tab, which allows you to view the
Readme file, with additional detailed installation instructions.
Page 2 of 41
2009-7-1
WORK FLOW STEPS
The tutorial will take you through the basic steps in creating and customizing your model, starting with a
“History Project” and “Data Entry,” and guide you on how to complete your forecasting model. Once
you have customized and finished your Financial Genome model, you can “export” (i.e., convert) your
Financial Genome project from the Financial Genome environment into the regular Excel environment as
a standard workbook. In this file format, you can share the model with others and make further
enhancements using Excel’s capabilities.
FINANCIAL GENOME PRO (FOR USERS OF QUICKBOOKS): Please refer to Addendum A:
Financial Genome Pro Guidance for special instructions for Financial Genome Pro.
Introduction:
The Financial Genome Environment: Image of completed model
The “Financial Dictionary Browser”
I. Getting Started
Creating a New Model: New History Project
Modify Time Periods
Update Required
II. Statement Design and Layout:
Duplicating and Itemizing Terms
Changing Label Names
Hiding and Moving rows
III. Data Entry
Income Statement
Balance Sheet
Cash Flow Statement
Ratios Statement
IV. Forecasting
Modify Time Periods for Forecasting
Forecast Drivers Statement
Data Entry
- Reset Forecast Values Tool
- View Inputs Tool
Cash (Funds) Closure
V. Export to Standard Excel Workbook
Appendices:
Appendix A: Sample Workflow Diagram
Appendix B: Sample Project Statements
Appendix C: Financial Genome Tools: Excel 2007 Ribbon Interface & Excel 2003 Toolbar
Appendix D: Financial Genome “INPUTS”
Appendix E: Terms That Cannot Be Duplicated or Itemized
Appendix F: Forecast Formulas with Corresponding Actual Equivalents
Appendix G: Using an External Database with Financial Genome – GenomeLink
- Formatting Requirements
- Map Database Using GenomeLink
- Use Database in Genome Project
- Create a Genome database workbook from the INPUT Sheet
Addendum A: Financial Genome Pro Guidance
Page 3 of 41
2009-7-1
INTRODUCTION:
The Financial Genome Project Environment
Before we take you through the detailed steps of creating a model, please review the layout of a
completed model in Financial Genome as shown below, which uses the Income Statement as a sample
display and pointers to identify key functions:
• Accessing the modeling tool buttons from the three ribbon tabs: {Home}, {Model}, {Format}
• Accessing statements on a separate sheets (the special Input Sheet contains only model Inputs)
• Customizing financial terms to match your chart of accounts
• Adjusting Time Periods to suit your requirements
• Entering actual(history) data values before creating a forecast model
Completed Financial Genome Model
Ribbon Tabs:
Home
Model (shown)
Format
Tool buttons
{Model} tab tool buttons
shown
Actual Periods
Forecast Periods
(History)
2004-2008 shown
2009-2010 shown
Duplicates of term
Time Periods:
(“Sales” shown)
Actual (History)
Base (History)
Forecast
Data Values:
Green = Input
Black = Calculated
Blue = Database
Magenta = Override database
Financial Terms
Income Statement
terms shown
Input Sheet and Statements (Income Statement - shown, Balance Sheet, Cash Flow Statement, Ratios Statement
and Forecast Drivers Statement - hidden)
Page 4 of 41
2009-7-1
The “Financial Dictionary Browser”
Now that you have become acquainted with the overall Genome project environment, you should take
another moment to review an important and unique feature of Financial Genome before we take you
through the detailed steps. Financial Genome has a built-in Financial Dictionary which contains all the
statements, terms and relationships required to create your model. You can access this Dictionary with
the “Financial Dictionary Browser,” which allows you to examine its contents as well as letting you insert
terms and statements from the Dictionary into your project. The browser is organized in a financial
statement hierarchy and allows you to find any term or statement by browsing hierarchically or searching
alphabetically and then obtaining an explanation.
When you choose to start a new model by selecting a New History Project or New Forecast Project on the
Model tab, you likely will not need to insert additional terms onto the statements provided for you.
However, should you wish to do so, you can insert additional terms or statements from the Financial
Dictionary Browser by clicking on any blank cell in the first column of your statement and hitting the
>Enter key to bring up the Browser:
When you insert terms / statements from the Dictionary, formulas are automatically written into your
Financial Genome Project and Inputs are automatically included on the Input Sheet (in alphabetical
order).
You can also use the {Model}>Insert Term tool button to open the Browser. Find the term in
the Browser (Search button provides an alphabetical listing) and click on the Insert Term button
at the bottom of the Browser. The term will be inserted onto a blank row above the row you are
currently on.
If you simply want more information on the highlighted term (e.g., input vs. calculated term,
formula for calculated term, definition, dependents), click on the Explain Term tool button.
(example shown: Explain Term for “Domestic Sales”)
Page 5 of 41
2009-7-1
I.
GETTING STARTED
Creating a New Model: New History Project
Load Financial Genome: click on > Start >Programs >Modernsoft >Financial Genome. Excel should
now load the Financial Genome addins automatically.
Click on the >{Financial Genome Environment} menu tab to begin.
Click on the >Financial Genome icon to enter the FG project environment.
Click on {Home}>New History Project tool button. Financial Genome will
automatically create a project with the basic statements (see appendices for sample statements): Income
Statement (shown below), Balance Sheet, Cash Flow Statement and Ratio Statement for a “history
project” (e.g., annual periods from 2004-2008). Note: Initial input values are set to “zero.”
“Actual” time periods:
2004-2008
Initial History Project with no
data values entered (zeroes)
Input values in green
Calculated values in black
Input Sheet
Basic statements
(Income Statement shown)
Page 6 of 41
2009-7-1
Modify Time Periods
Once the History Project has been opened, your first step is usually to adjust the time periods to suit your
needs. If you wish, you can modify the time periods at any time later on.
Click on {Model}>Time tool button if you wish to change the dates for “actual” (history) periods.
The time wizard will display the fields for entering your time modifications as shown. Use
Beginning through Base periods for entering “actual” periods. (Base year is the last “actual”
period). Because we are creating a history project, be sure that the Ending period matches the Base
period. Click on >OK.
A forecast project is created when the Ending period is greater than the Base period. Later we will extend
the Ending period when we perform Forecasting.
History Project: Base Period = Ending Period
Beginning Period
Base Period
Ending Period
Note: The default “Unit Time” is “Year” (annual). You can select “Quarter” or “Month” for your Unit
time. Once the Unit Time is selected for the project, do NOT change it during a project or else you will
have to re-enter your data values.
Update Required:
Whenever the UPDATE REQUIRED alert appears on your statement (above the time periods),
click on the {Model}>Update Required tool button. This step causes Financial Genome to
generate formulas for revised rows. The update process may take some time if your project
contains many time periods. If you are making many changes (e.g., changing row labels), you
can delay using the Update operation until you are done and ready to examine the calculations.
Page 7 of 41
2009-7-1
II.
Statement Design and Layout
The “history model” contains all the financial terms and relationships that you can adapt to create a
customized model that matches your chart of accounts. Formulas are automatically written into your
Financial Genome Project for all calculated terms, and required Inputs are automatically listed on
the Input Sheet (in alphabetical order). When you duplicate (or itemize) a dictionary term (see below),
the duplicate will inherit all the properties of the original term. All the statements are dynamically linked
– changes on one statement will be reflected on all related statements.
To adapt the model to your specific account structure and labels, you should use the following tools:
{Model}>DUPLICATE ACCOUNT (creates one or more instances of a term)
or >ITEMIZE ACCOUNT (creates one or more instances of a term with a total)
{Model}>CHANGE LABEL (change term label to match your accounts)
{Format}>MOVE ROW up/down (customize order of rows)
{Format}>HIDE ROW (hide any rows not used or needed)
IMPORTANT: When designing and customizing your FG Model, you must use the special Financial
Genome tools provided. The conventional Excel editing functions will NOT work while you are in the
Financial Genome environment. Please save you project work regularly. If you encounter an error
message, saving the workbook will not create a usable FG project. Instead, resort to your last saved
project.
Each of these tools is discussed in detail below.
Page 8 of 41
2009-7-1
{Model}>DUPLICATE ACCOUNT
The Duplicate Account operation creates duplicate instances of Input terms, allowing you to create, for
example, multiple Sales accounts. Only input terms can be duplicated - calculated terms cannot be
duplicated. A duplicate of a term inherits the properties of the original term duplicated, such as inclusion
in statements, sections, and formulas defined in the Dictionary for the original term. In general, this
means that duplicate accounts are automatically incorporated in formulas as sums (e.g., “Sales1 +
Sales2”) - your model is always fully integrated and dynamically linked as you customize your
statements.
To duplicate an input term, select a term in the first column on your analysis work sheet.
Example: “Selling, general and
administrative” term selected for
duplication.
Example: “Selling, general and
administrative” term duplicated
•
•
•
•
Click the >Duplicate Account tool button
You will now have a second input term called "<termname>2", and the original term is renamed
"<termname>1" (the actual numbering will depend on how many duplicates already exist).
Both new Input terms have the same properties as the original term that was duplicated.
You can create as many duplicates as you need with Duplicate Account. You can change the row
labels and move the rows anywhere you like on your project statement (see Change Label and Move
Rows tool).
{Model}>ITEMIZE ACCOUNT
The Itemize Account operation is the same as Duplicate Account operation, but it also creates a TOTAL
for itemizations on the statement (e.g., “Sales TOTAL = Sales1 + Sales2”).
Example: “Sales” term
itemized with “Sales TOTAL”
Note: Certain input terms cannot be duplicated/itemized. FG will warn you if an action cannot be
performed.
Page 9 of 41
2009-7-1
{Model}>CHANGE LABEL
The Change Label operation allows you to conveniently rename terms in your original statement or terms
that you have inserted into a work sheet from the Financial Dictionary or duplicated/itemized.
• Select the term in the first column and click the >Change Label button.
• The Change Name for Row dialog appears.
• Type in a new name (alias) for the term in the text input box.
• Click >OK. The term will be displayed with the new name both on your project statement(s) and on
the Input Sheet.
Example: “Sales1” term label
changed to “Domestic Sales”
TIP: You may also change the label of a term (which is usually an account name) by simply typing a new
name directly over the row label in the first column.
{Format}>MOVE ROW up/down
The >MOVE ROW operation will move any selected populated row, any selected itemized group, or any
selected complete section up/down the work sheet by one row at a time.
Example: “Sales TOTAL” row
moved down.
{Format}>HIDE ROW
The Hide Row operation will hide any row that is displayed on a Financial Genome work sheet.
• Select row and click on the >Hide Row tool button.
You can use the >Unhide Row tool button to make hidden rows visible again.
•
TIP: Do not hide rows you may need for forecasting later on. For example, do not hide the special term
“Funds Deficit” on the Balance Sheet (see “Forecasting”).
Page 10 of 41
2009-7-1
III.
Data Entry
Now that you have customized your model to match your business account structure, you are ready to
enter actual data for historical data (i.e., data for Beginning Period through Base Period). Enter all
historical / actual data for the Income Statement, Balance Sheet and Cash Flow Statement and selected
terms on the Ratios Statement. Make sure all statements reconcile to company data before proceeding to
the forecast mode: Net Income should reconcile; the Balance Sheet should “balance.”
There are several methods for data entry:
Type data values directly into project statement cells (recommended for first time users)
The most direct way to enter data values into a project is to type them into the data cells once the
statements have been created and formatted with the appropriate row labels. This process builds up a set
of historical values that propagate to all statements affected by them, and generates a complete data
record on the project’s INPUT Sheet. Calculated terms will be automatically calculated (black values).
Enter data directly on statement
for all inputs (green).
Calculated terms (black)
automatically updated.
Type data values into a project’s INPUT Sheet cells
Another direct way to enter data is to type the data values directly into the INPUT Sheet, by locating the
respective data rows that are displayed in alphabetical order (see Appendix).
Create a Genome database from external data records
You can use an external database that has been properly formatted in Excel and that has been mapped to
the Financial Dictionary using GenomeLink. Recommended only for advanced users (see Appendix re:
GenomeLink).
Reconcile all historic data to match company records
INCOME STATEMENT: “Net Income” should match actual company data, and discrepancies should
be resolved.
• Check sign +/- used for inputs.
• IMPORTANT: Special treatment of depreciation and
amortization:
o Financial Genome requires that the depreciation and
amortization component of costs and expenses be
explicitly shown on the Income Statement so as to be
able to reconcile to the Cash Flow statement.
o Depreciation can be shown either before Gross Profit
as Depreciation cost of sales and/or after Gross Profit
as Depreciation expense.
o Amortization should be shown separately as an
expense item: either as
Amortization–goodwill
and/or Amortization–other intangibles.
o Therefore, you may need to adjust other cost and expense accounts to exclude depreciation
and amortization, if they contain these “non-cash” items.
Page 11 of 41
2009-7-1
BALANCE SHEET: By definition, balance sheets must balance (Total Assets = Total Liabilities +
Equity).
• The indicator Balance Sheet Out-of-Balance term at bottom of your Balance Sheet should be zero.
• If the indicator Retained Earnings Out-of-Balance is not equal to zero, enter an equivalent value for
Retained Earnings Adjustment to reconcile the Balance Sheet. FG assumes “Retained Earnings =
Retained Earning Prior Period + Net Income – Dividends + Retained Earnings Adjustment.”
CASH FLOW STATEMENT: You should not change the basic layout of Cash Flow Statement,
although you can hide unused rows. This statement is automatically derived from the Income Statement
and Balance Sheet plus selected inputs. The components of the Cash Flow Statement (Operating,
Investing and Financing activities) may only correspond roughly to company statements, which often
contain accounting adjustments.
• If available, you should provide Input values for selected Cash Flow terms not contained in income
statements and balance sheets. These terms are particularly relevant for forecasting your company’s
future cash position.
- Capital expenditures
- Acquisitions (of Gross Property, Plant and Equipment)
- Dispositions (of Gross Property, Plant and Equipment)
- Repayment of Long-term Debt
- Proceeds from Long-term Debt
- Common Dividends
- Preferred Dividends
• Note: “Uses of funds” will be automatically recognized as negative numbers even though the value
is entered as a positive number. For example, “Capital expenditures” and “Repayment of Long-Term
Debt” will appear as negative numbers, even though you entered positive values on the statement.
• Make sure that the indicator Cash Out-of-Balance term at bottom of statement equals “zero.”
• First year cash flow figures may not meaningful because prior year Balance Sheet values are not
available; therefore “Cash Out-of-Balance” may not equal zero in first time period.
RATIOS STATEMENT: The formulas on the Ratios Statement cannot be changed, since they are
governed by the Financial Dictionary and values are automatically derived from the inputs on other
statements.
• If available, enter input values for:
- Employees
- Share Price Common
- Share Price Preferred
Congratulations! You have completed your History Project (see Sample Project Income Statement
provided below). You can now build your forecast model by extending your History Project into future
time periods and using the helpful forecasting features of Financial Genome described next.
Page 12 of 41
2009-7-1
SAMPLE HISTORY PROJECT FROM 2004-2008
(Income Statement shown; see Appendix for statement samples)
Time Periods:
Beginning = 2004
Base = 2008
Ending = 2008
Duplicates of “Sales”
with labels changed
“Depreciation”
broken out separately
“Amortization”
broken out separately
The values shown in this illustration were created by using the Sample Data Base provided with
Financial Genome. (See Appendix for how to use an existing database in a project.)
Page 13 of 41
2009-7-1
IV.
Forecasting
Financial Genome can automatically create a forecasting model which allows you to assume and project
values for future periods. The model has several powerful features, which you should review before we
guide you through the detailed steps of building your forecast:
•
•
•
•
•
•
Forecast Formulas, Forecast Drivers and Actual Equivalents: The forecasting model is built
around key forecast formulas (see Appendix F) based on operating, investing and financing
relationships most typically used in the financial analysis of a business. These relationships are
derived from the actual performance record and can be found on the Ratios Statement. For assistance
in your forecasting, Genome provides forecast drivers which are key forecasting assumptions that
you enter. Most forecast drivers are based on a corresponding historical ratio which is called an
actual equivalent.
Example:
o Forecast Formula for Sales = Sales_prior * (1+Sales_growth_percent_forecast)
o Forecast Driver for Sales = Sales_growth_percent_forecast
o Actual Equivalent for Sales_growth_percent_forecast = Sales_growth_percent
Forecast Drivers Statement: This statement displays all the forecast drivers and their actual
equivalents.
Automatic Initialization of Forecast Assumptions: Genome automatically initializes the input
values for the forecast periods based on the Base Period values. For the most part, the base period
values for the business are extended into the forecast periods. From this starting point, you modify
your forecasting assumptions as desired.
Reset Forecast Values Tool Button: This tool, found on the {Model} tab, allows you to select
among several forecasting methods (see below).
Show Inputs Tool Button: This tool, found on the {Model} tab, temporarily displays all the input
terms upon which a calculated term (black) depends. Data can be entered while input rows are
displayed.
Full cash (funds) closure: Financial Genome operates on the principle that “uses of funds” equals
“sources of funds.” Based on your forecasting assumptions, Genome automatically determines if a
funding gap exists. This funding gap is referred to as the Funds Deficit, which shows a positive value
when “uses” exceed “sources” of funds or a negative value (funds surplus) when “sources” exceed
“uses” of funds. The term is shown on the liability side of the Balance Sheet and is also referenced on
the Cash Flow and Forecast Drivers statements. To eliminate any funding gap (or surplus), you will
need to modify your operating, investing and financing assumptions (see below).
Page 14 of 41
2009-7-1
The following section provides the steps for creating your forecasting model.
Modify Time Periods for Forecasting
Forecasting is possible only if the Ending period is greater than the Base
period (last year of actual / historical).
Click on {Model}>Time tool button. Enter a value for the Ending
period (last forecast period) which is greater than Base period.
Click >OK.
You will find that forecast periods have been added to your project.
•
•
UPDATE REQUIRED! Click {Model}>Update Required tool button to populate your model
with all terms and formulas and recalculate project workbook.
Forecast input values in the forecast periods will be automatically initialized:
o “Sales growth percent – forecast” will be set to “zero” (i.e., “Sales” forecast to be flat).
o Most forecast drivers with actual equivalents will assume their Base Period value. For
example, “SG&A percent of sales - forecast” = “SG&A percent of sales” in the Base
Period.
o Key cash flow inputs that are set to “zero” (i.e., Capital expenditures, Acquisitions,
Dispositions, Repayment of long-term debt, Proceeds from long-term debt).
o Other forecast inputs will equal their Base Period values (e.g., “Other Assets”).
Forecast Drivers Statement
The Forecast Drivers Statement combines all the assumptions needed for forecasting on one statement.
This is particularly useful because many forecast assumptions (e.g., forecast drivers) are not typically
visible on the basic statements (Income Statement, Balance Sheet, Cash Flow Statement and Ratios
Statement).
You can only add the Forecast Drivers Statement after you have created a forecast project by
extending the ending time period beyond the base period.
To add the Forecast Drivers Statement to your project:
Click on the {Model}>Statement tool button
•
•
•
Select/highlight Forecast Drivers (last statement)
from the pull-down list of statements
Click >Add
Click >OK
The Forecast Drivers Statement has now been added to your project and can be accessed by clicking on
the sheet tab. If the Update Required! button appears, click it to regenerate formulas.
Page 15 of 41
2009-7-1
Sample Forecast Project: Forecast Drivers Statement with Initialized Forecast Values
Drivers Organized Into the Three Components of the Business System: Operating, Investing and Financing
Color rules for data values are the same as
in the History Project:
Input values in green.
Calculated values in black.
Sales growth percent – forecast: input
value initialized at “zero” percent for
forecast periods.
Sales (total) growth rate
SG&A percent of sales – forecast: input
value initialized with base period value of
actual equivalent – SG&A percent sales
Input terms: forecast input values
initialized with base period value.
Page 16 of 41
2009-7-1
Data Entry: Forecasting
There are several methods to enter your forecasting assumptions. As in entering historic (actual) data
values, you can enter future data values for all inputs directly on the project statements or on the Input
Sheet, where all inputs are listed alphabetically. Note that for forecasting, many terms are now calculated
with forecast formulas and therefore are no longer input terms as they were during the “actual” periods.
The various inputs for the forecast formulas can be found on the Forecast Drivers Statement.
ENTER VALUES FOR FORECAST DRIVERS ON THE FORECAST DRIVERS STATEMENT
The Forecast Drivers Statement includes all the forecast drivers (inputs) for forecast. The drivers/inputs
are organized into three groups: Operating, Investing and Financing Drivers. As mentioned earlier, their
values have been “initialized” for the forecast periods. For the most part, the drivers have been initialized
with the Base Period values of their actual equivalents. The actual equivalent terms and their calculated
values are also included below their corresponding forecast drivers.
OVERRIDE TERMS CONTAINING FORECAST FORMULAS WITH DIRECT INPUTS
You can enter dollar inputs directly on a statement (e.g., Income Statement, Balance Sheet, Cash Flow
Statement) or Input Sheet for terms containing forecast formulas (black). This action will override the
default forecast formulas found in the Dictionary. Your direct inputs will now appear in green.
REVERT TO FORECAST FORMULA
Even after overriding a forecast formula by entering a dollar value, you can revert back to the default
forecast formula by simply highlighting the cell and hitting the BACKSPACE key and then the ENTER
key.
RESET FORECAST VALUES TOOL BUTTON
The {Model}>Reset Forecast Values tool button allows you to choose among several
methods for inputting forecast values. Genome will have automatically filled in an initial set
of forecast values for all forecast inputs using the default method for each term.
- Find any row with an input term in the historic period (green or blue if using database) and select the
column you want to modify.
- Click the >Reset Forecast Values tool and Genome will provide you with options for initializing the
forecast values in the window shown below.
You can select one of several methods for setting
forecast values starting with the column you select and
applying the method across to the ending period:
o Use zeroes
o Copy across a value
o Apply a growth rate (percentage change from the
prior period)
o Apply a percent increase (decrease) to existing
forecast value
o Restore the dictionary's default method for
initializing forecast values
Page 17 of 41
2009-7-1
VIEW INPUTS TOOL BUTTON
The {Model}>View Inputs tool temporarily displays and highlights all input terms in a
statement that affect a calculated term to allow for data entry. This is most useful for terms
containing forecast formulas, since the inputs / forecast drivers in the forecast formula may be
hidden from view on the current statement.
Select any calculated term, and click on the {Model}>View Inputs tool button. If inputs are not already
being displayed for this statement, the following operation will occur:
• The system will unhide (or insert) and display with yellow highlighting all of the input terms that are
associated with and affect the selected calculated term.
• You can change the data values of the highlighted input terms by typing new values directly into the
appropriate cells on the statement. The actual equivalents associated with any forecast driver are also
displayed for reference.
• The example below shows the View Inputs tool applied to the term “Gross Profit” = Cost of Sales /
Sales
Inputs for Gross Profit
displayed and highlighted
in yellow.
Actual equivalent for each
forecast driver displayed
below driver.
To restore the statement to its original layout, select the calculated term and click on the {Model}>View
Inputs tool button. Select >Restore Entire Statement.
Income Statement restored
to original layout after
using View Inputs tool.
Page 18 of 41
2009-7-1
Cash (Funds) Closure: Closing the Funding Gap
Because your Genome model’s financial statements are fully integrated and dynamically linked, as you
enter your forecast assumptions, Genome continuously calculates whether a funding gap or surplus exists.
A funding gap exists when the “uses of funds” exceed the “sources of funds” and is reflected as a positive
value in the special term Funds Deficit. (A negative value in the Funds Deficit indicates that a surplus
situation exists where “sources of funds” exceed the “uses of funds.”)
Funds Deficit term:
The Funds Deficit term is automatically calculated by Genome and appears as a “plug” figure in the
Liabilities section of the Balance Sheet and on the Cash Flow Statement in order to provide cash closure
for the forecast periods. In addition, it appears in the Financing Drivers section of the Forecast Drivers
Statement. Note that for history periods “sources of funds” must equal “uses of funds” by definition, that
is, Funds Deficit = 0.
When forecasting, however, your initial forecasting assumptions will likely result in a funding gap or
surplus. To eliminate this funding imbalance you will need to adjust your forecasting assumptions to
achieve funding balance (i.e., Funds Deficit = 0).
Funding gap exists for forecast
periods. Shown as “plug
figure” on balance sheet.
Change in funds deficit term used to
provide full cash closure.
Funds deficit term also shown in
Financing Drivers section of
Forecast Drivers statement.
Page 19 of 41
2009-7-1
Closing the Funding Gap:
While there is no correct order to input your forecasting assumptions, we suggest you modify your
forecasting inputs roughly in the order displayed on the Forecast Drivers Statement: Operating, Investing
and Financing Drivers.
• Operating Drivers cover sales, cost and expense assumptions.
• Investing Drivers cover working capital (e.g., accounts receivable, inventories, accounts payable)
and fixed assets assumptions (e.g., capital expenditures, depreciation).
• Financing Drivers deal with debt and equity financing assumptions (e.g., changes in debt and
equity, dividends).
Modify your assumptions until you find the scenario that closes the funding gap, i.e., funds deficit = 0.
Because the funds deficit figure on the balance sheet is a cumulative value, you should reconcile forecast
period 1 first, then forecast period 2, etc.
Funding gap closed:
Funds deficit = 0.
You have now completed your forecasting model. Be sure to save your Financial Genome project. To
open your saved project, first run Financial Genome before opening the project workbook file.
You can now choose to export your completed Financial Genome project as a standard Excel workbook
file for sharing and additional analytical enhancements, as described next.
Page 20 of 41
2009-7-1
V.
Export to Standard Excel Workbook
Once you have built your model in the Financial Genome environment, you can convert your project into
the regular Excel environment as a standard workbook. In this file format, you can share the model with
others and make further enhancements using Excel’s capabilities. You can further customize your model
by adding detail worksheets you designed as desired and linking them to the input sheet, by adding new
user-defined terms, adding graphics, etc.
• To export your project, on the Excel menu, click the {Format}> Export to Excel
Workbook tool button. Make note of where you save the workbook. After exporting your
project, Financial Genome will automatically save and close your Financial Genome project.
•
•
•
•
•
Open the exported workbook file. This is a standard Excel workbook and functions as any other Excel
spreadsheet. However, all financial statements are protected. Furthermore, unlike the original
Genome project, you cannot enter data values directly on the financial statements. If you unprotect
these sheets and override a formula on the statement, the integrity of the analysis will be
compromised.
Entering Input Values: Your export model allows entering input values only on the Input Sheet. In
contrast to the Genome project, the exported Excel workbook’s Input Sheet now groups all inputs by
statement and in the order they appear there or are needed by the calculated terms on the statements.
This differs from the Input Sheet of the Genome project which lists inputs alphabetically. Forecasting
drivers/assumptions are listed along with their respective financial statement inputs where applicable.
While “inputs” will appear in green on the financial statements, their values must be entered on the
Input Sheet. All financial statements in the export model are protected for this reason.
Overriding Forecast Formula with Direct Dollar Input: Many of the terms use “forecast drivers”
to derive their value for the forecast periods. You can override the use of the forecast formula on the
statements if you enter a dollar value for these terms for the forecast periods on the Input Sheet. The
value for a direct dollar input will override the forecast formula and the forecast driver value will be
ignored. You can return to using the forecast formula by deleting the respective direct dollar input.
Adding User Defined Rows: To insert a user defined row(s) on the statements, you must first
unprotect the sheet ({Review}> Unprotect Sheet). You can add your own rows and formulas as you
would on any Excel spreadsheet, but you should avoid changing any of the existing rows and values.
Linking Detailed Worksheets to the Input Sheet: You can add worksheets to your workbook to
provide supporting details for building up the values of such terms as Sales; Capital spending; Gross
property, plant and equipment, Long-term debt, etc. These detailed worksheets must be referenced
and linked to the appropriate dependent term on the Input Sheet. For example, you could provide
product line sales detail (units sold and price per unit by product line) that would link to the
corresponding Sales row on the Input Sheet.
This concludes the tutorial guidance. Once you are in the Financial Genome environment, please consult
our extensive help system for detailed explanations of all Financial Genome features. Click on the
{Home} or {Model} tab to access the HELP tool button.
Page 21 of 41
2009-7-1
Appendix A: Sample Workflow Diagram
Enter Financial Genome Environment:
Create History Project:
•
{Model} >New History Project
•
Modify time periods: {Model}>Time
Actual: Beginning to Base periods
Base = Ending period
•
Customize statements:
{Model} >Duplicate Account
{Model} >Itemize Account
{Model} >Change Label
{Format} Move Row Up / Down
{Format} >Hide Row
•
Enter data for all history input values
•
Check results:
•
{Model} >Update Required!
23456
Net Income?
Balance sheet out-of-balance?
Retained earnings out-of-balance?
Build Forecast Model:
•
Modify ending time period: {Model}>Time (Ending > Base)
•
Add Forecast Drivers Statement: {Model} >Statements
•
Enter data for all forecast input values using Genome tools:
{Model} > Reset Forecast Values
{Model} >View Inputs
•
Close funding gap (Funds Deficit = 0)
•
{Format} >Export to Excel Workbook
Page 22 of 41
2009-7-1
Appendix B: Sample Project Statements
(For all sample statements, forecast period values shown based on automatic initialization of the input
values for the forecast periods.)
Sample Income Statement
Page 23 of 41
2009-7-1
Sample Balance Sheet
Page 24 of 41
2009-7-1
Sample Cash Flow Statement
Page 25 of 41
2009-7-1
Sample Ratios Statement
Page 26 of 41
2009-7-1
Sample Forecast Drivers Statement
Page 27 of 41
2009-7-1
Sample Financial Genome Project Input Sheet
(only portion of Input Sheet shown)
Page 28 of 41
2009-7-1
Sample Export to Standard Excel Workbook: Input Sheet
(only portion of Input Sheet shown)
Page 29 of 41
2009-7-1
{Home} Tab New Project New History Project New Forecast Project How Do I? Sample Project Annotated Project About Financial Genome Tutorials Videos Contact Us Enable Auto Start Registration Modernsoft Online Return to Excel Environment Appendix C: Financial Genome Tools – Excel 2007 vs. Excel 2003
Excel 2007: Financial Genome Ribbon Interface
{Model} Tab {Format} Tab Hide / Unhide Rows Move Row Up / Down Decrease / Increase Indent Color Pattern Bold/Italic/Underline/D‐Underline Toggle Headings Show Outlines Page Headings Export to Excel Workbook Time Statement Update Excel Sheet Explain Term Insert Term Blank Row Section Header Delete Row Duplicate / Itemize Account View Inputs Reset Forecast Values Assume Zero Values Convert to Input Use/Create/Map Database Refresh Input How Do I? Dictionary Help Excel 2003 (& earlier): New Project Control Panel & Financial Genome Toolbar
Financial Genome Toolbar (Excel 2003 & earlier) Control Panel Update New Project Statements Data Time Dictionary Insert Term Delete Row Alias Duplicate Input Itemize Reset Forecast Values Convert to Calculated Assume Zero Values View Inputs Hide Row Unhide Rows Page 30 of 41
Create Section Header Move Row Up / Down Show Outlines Bold Italic Underline / Double Underline Toggle Headings Insert Blank Row Decrease / Increase Indent Color Pattern Information 2009-7-1
Appendix D: Financial Genome “INPUTS”
INCOME STATEMENT INPUTS
Sales
Cost of sales excluding depreciation
Depreciation cost of sales
Selling, general and administrative
Research and development
Depreciation expense
Amortization - goodwill
Amortization - other intangibles
Other operating expense
Interest expense
Interest income
Other income (expense)
Special items
Provision for income taxes
Extraordinary items
Discontinued operations
Minority profits
Cumulative effect of accounting changes
Preferred dividends
Common dividends
Common shares outstanding - basic
Common shares outstanding - diluted
BALANCE SHEET ASSET INPUTS
Cash
Short-term investments
Accounts receivable
Inventories
Other current assets
Gross property, plant and equipment
Accumulated depreciation
Minority investments
Other investments
Goodwill
Other intangibles
Deferred charges
Other long-term assets
BALANCE SHEET LIABILITIES INPUTS
Notes payable
Accounts payable
Income taxes payable
Accrued expenses
Other current liabilities
Current portion of long-term debt
Long-term debt
Funds deficit
Deferred taxes
Other long-term liabilities
BALANCE SHEET EQUITY INPUTS
Minority interest
Preferred stock
Common stock
Capital surplus
Reserves
Retained earnings
Treasury stock
CASH FLOW INPUTS
Retained earnings adjustment
Other non-cash adjustments to net income
Capital expenditures
Acquisitions
Dispositions
Repayment of long-term debt
Proceeds from long-term debt
OTHER INPUTS
Employees
Share price preferred
Preferred shares outstanding
Share price common
Page 31 of 41
2009-7-1
Appendix E: Terms That Cannot Be Duplicated or Itemized
Only terms classified as Inputs in the Dictionary can be duplicated. The Duplicate Account tool button
creates duplicate instances of terms classified as Inputs in the Financial Genome Dictionary.
A duplicate of a term inherits the properties of the original term duplicated, including inclusion in
statements, sections, and formulas defined in the Dictionary for the original term.
CERTAIN INPUT TERMS CANNOT BE DUPLICATED/ITEMIZED due to the nature of their
linkages to other terms. Financial Genome will not allow these terms to be duplicated and will warn the
user with this message: “To maintain calculation integrity, this version does not allow duplicating or
itemizing [term].” or “Can’t duplicate a term that might be used as a forecast driver.”
-
Gross Property, Plant and Equipment
Accumulated Depreciation
Goodwill
Other Intangibles
Notes Payable
Current Portion of Long-term Debt
Long-term Debt
Funds Deficit
Retained Earnings
Share Price Common
Share Price Preferred
Preferred Shares Outstanding
“Forecast drivers” cannot be duplicated.
For example:
- Sales Growth Percent – forecast
- Interest Rate – forecast
Page 32 of 41
2009-7-1
Appendix F: Forecast Formulas with Corresponding Actual Equivalents
Dictionary Name
Forecast Formula (Forecast Drivers in bold)
This Column shows the "Actual
Equivalent" term matching the
Forecast Driver in formula.
Interest_Expense
(Notes_Payable_prior + Notes_Payable)*.5*Interest_Rate_forecast*
(Daysinperiod/360) + (Long_Term_Debt_prior + Long_Term_Debt
+Current_Portion_of_Long_Term_Debt_prior +
Current_Portion_of_Long_Term_Debt) * .5 * Interest_Rate_forecast
* (Daysinperiod/360)
Interest_Rate_Weighted_Average
Accumulated_Depreciation
Accumulated_Depreciation_prior + Depreciation_Cost_of_Sales +
Depreciation_Expense +
Accumulated_Depreciation_Adjustment_forecast
Common_Dividends
Common_Dividend_Payout_Percent_of_Income_Available_fore
cast * Net_Income_Available_for_Common_Dividends
Common_Dividend_Payout_Percent_of
_Income_Available
Common_Stock
Common_Stock_prior + Change_in_Common_Stock_forecast
Change_in_Common_Stock
Cost_of_Sales_Excluding_De
preciation
COS_Excluding_Depreciation_Percent_of_Sales_forecast *
Sales
COS_Excluding_Depreciation_Percent_
of_Sales
Accounts_Payable
Cost_of_Sales * (360/Daysinperiod) *
Accounts_Payable_Percent_of_COS_ending_forecast
Accounts_Payable_Percent_of_COS_e
nding
Inventories
Cost_of_Sales *
(360/Daysinperiod)/Inventory_Turnover_ending_forecast
Inventory_Turnover_ending
Provision_for_Income_Taxes
Effective_Tax_Rate_forecast * Income_Before_Taxes
Effective_Tax_Rate
Goodwill
Goodwill_prior - Amortization_Goodwill
Gross_Property_Plant_and_
Equipment
Long_Term_Debt
Gross_Property_Plant_and_Equipment_prior + Capital_Expenditures
+ Acquisitions - Dispositions
Long_Term_Debt_prior +
Current_Portion_of_Long_Term_Debt_prior +
Proceeds_From_Long_Term_Debt Repayment_of_Long_Term_Debt Current_Portion_of_Long_Term_Debt
Other_Intangibles
Other_Intangibles_prior - Amortization_Other_Intangibles
Other_Operating_Expense
Other_Operating_Expense_Percent_of_Sales_forecast * Sales
Other_Operating_Expense_Percent_of_
Sales
Preferred_Stock
Preferred_Stock_prior + Change_in_Preferred_Stock_forecast
Change_in_Preferred_Stock
Research_and_Development
R_and_D_Percent_of_Sales_forecast * Sales
R_and_D_Percent_of_Sales
Retained_Earnings
Retained_Earnings_prior + Net_Income - Preferred_Dividends Common_Dividends + Retained_Earnings_Adjustment
Cash
Sales * (360/Daysinperiod) *
Cash_Percent_of_Sales_ending_forecast
Cash_Percent_of_Sales_ending
Short_Term_Investments
Sales * (360/Daysinperiod) *
Short_Term_Investments_Percent_of_Sales_ending_forecast
Short_Term_Investments_Percent_of_S
ales_ending
Accounts_Receivable
Sales * Accounts_Receivable_DSO_ending_forecast /
Daysinperiod
Accounts_Receivable_DSO_ending
Sales
Sales_prior * (1+Sales_Growth_Percent_forecast)
Sales_Growth_Percent
Selling_General_and_Admini
strative
SG_and_A_Percent_of_Sales_forecast * Sales
SG_and_A_Percent_of_Sales
Funds_Deficit
TOTAL_Assets - TOTAL_Current_Liabilities TOTAL_Shareholders_Equity - Long_Term_Debt - Deferred_Taxes Other_Long_Term_Liabilities - Minority_Interest
Treasury_Stock
Treasury_Stock_prior + Change_in_Treasury_Stock_forecast
Page 33 of 41
Change_in_Treasury_Stock
2009-7-1
Appendix G: Using an External Database with Financial Genome - GenomeLink
You can use an external database to import data into your Genome project. The database file must meet
certain conditions:
• Database workbook must be in a particular Excel format.
• Database must be mapped to the Financial Genome dictionary using GenomeLink.
Formatting requirements
Financial Genome will access financial information only from Excel workbooks in a format where rows
contain the financial data and columns the identifying labels and time periods:
• The first column must contain a unique identifying label (account name, e.g., “Accounts payable”,
“Accounts receivable - net”) for each record.
• The second and successive columns must identify the time periods in the required format (e.g.,
MM/DD/YYYY, that is “12/31/2008”, etc.)
• Optional: Name the database sheet as “Data”.
The illustration below shows a portion of a formatted database (see also SampleDatabase.xlsx workbook
in the folder c:\Program Files\Modernsoft\Databases\).
Account labels Time periods (MM/DD/YYYY)
Data values
Data sheet GenomeLink sheet (see Map Database below)
Preformatted Database Template provided with Financial Genome
This template can be found in your file directory:
“C:\Program Files\Modernsoft\Extras\Databases\Database Template.xlsx”. You can use it as a guide to
configuring your data files on a spreadsheet for use in creating the database workbook for your project, or
you can simply enter your data directly on this template, rename it and save it for use as the data record in
the database workbook. If you make any changes to the data records on the template (e.g. renaming
record labels, adding or deleting record rows) you will need to remap the database from scratch before
using the workbook in your Genome project (see “Map Database Using GenomeLink” below).
Page 34 of 41
2009-7-1
Map Database Using GenomeLink
The Map Database operation (GenomeLink) allows you to define how the database record names in
your Excel workbook data file correspond to the terms in the Financial Genome Dictionary.
To run GenomeLink, close all open Genome projects and click on the {Model} >Map Database tool
button.
Map Database opens your database file, locates the worksheet entitled DATA, and adds a worksheet,
called GENOMELINK. The {Model} tab now enters the Map Database Operation mode. Your task is
to indicate how database record names on the DATA sheet correspond to terms in the Financial Genome
Dictionary.
First, notice the columns on the GenomeLink worksheet (an example is shown below).
- Database Record Name
- Genome Dictionary Print Name.
- Statement Alias (optional)
- Flip Sign
- Ignore
Second, notice that when the GenomeLink worksheet was created, the program automatically mapped
database record names that correspond to print names in the Financial Genome Dictionary by filling in the
names in the second column. Terms with no exact match are marked as “TRUE” in the Ignore column.
Exact match to Genome Dictionary name. No match to Genome Dictionary name. Initially marked as Ignore (TRUE). GenomeLink sheet automatically created. Your task is to handle any unmapped terms (i.e. terms with no exact match to Genome print names),
unnecessary records (e.g. sub-totals), and data sign problems. Each of these tasks is briefly discussed
below.
Page 35 of 41
2009-7-1
For more detailed explanations, once the {Model} >Map Database operation is initiated, click on the
>How Do I? button.
Mapping Unmatched Terms to Financial Dictionary
Financial Genome's power and flexibility comes from its Financial Dictionary. The Dictionary includes
common accounting terms and relationships, which are typically found in most financial statements. For
each financial term, the Dictionary contains information to identify it, to define it, to control how it is
displayed, and to define its relationship to the other terms in the Dictionary. When you open a data file
using Financial Genome, the program searches the file's record headers and matches them against the
Dictionary terms.
If the database record names (account names) in your data file do not match the generic print names
contained in the Genome Dictionary (or if you disagree with the exact match mapping), you need to
decide how to map the database record names to the Dictionary term names.
To resolve any of these issues, select the appropriate cell in the Genome Dictionary Print Name column
of the GenomeLink worksheet, and press the Enter key on your keyboard or select Show Browser from
the Map Database Operations . Tip: If you want to change the Dictionary Term that already appears in
the cell, simply press the DELETE key on your keyboard, then ENTER (to delete the term) and then
ENTER again (to open the browser). This brings up the Financial Dictionary Browser dialog in map
mode, as shown below.
Click on >Show Browser to enter map mode. Verify Mapping button
Example shown: Database record “Accounts Receivable‐Net” mapped to Genome Print Name “Accounts Receivable” Map Term, Flip Sign, Ignore Row, Up, Down, Close buttons Page 36 of 41
2009-7-1
There are several dialog buttons available to assist you in completing the database mapping process:
¾ Map Term - Map an unmatched database record to the appropriate term in the Dictionary. Browse
for the term in the Financial Dictionary. Select term and click >Map Term. You can map more than
one of your database records to the same Genome Dictionary term. Genome will automatically create
duplicates of the dictionary term when you use the data file in a project.
Example of multiple database records mapped to same Genome Dictionary term: e.g. Domestic Sales and International Sales mapped to Sales (Genome name). ¾ Ignore Term - Indicate (flag) a database record that you wish to ignore entirely. Sub-totals of terms
in the database file should be ignored. Your Genome project will calculate totals and sub-totals for
you (e.g. Gross Profit, Net Income, Total Assets, Total Liabilities). Enters a value (TRUE) in the
Ignore column.
¾ Flip Sign - Indicate (flag) a database record where the "sign" of the record value needs to be reversed.
Enters a value (TRUE) in the Flip Sign column.
¾ Up / Down –Move the selected cell in the Genome Dictionary Print Name column up or down one
cell. This will allow you to continue mapping another unmatched term without closing the Browser.
¾ Close - When you are finished mapping terms, click the Close button, and the Browser will close.
TIP: You can also type directly onto the GenomeLink worksheet without using the Browser. You can
change the terms in columns two (Database Record Name), three (Genome Dictionary Name), four (Flip
Sign) and five (Ignore) of the worksheet by directly entering terms yourself.
Finally, notice you that you are not allowed to modify the first column or delete rows on the GenomeLink
sheet. To modify the database labels, edit the DATA sheet and recreate the GenomeLink worksheet by
using Verify Mapping (see below). Whenever you reopen a database using MAP DATABASE, the
program will also ask you whether you want to recreate the mapping from scratch.
Statement Alias (Optional)
Except for duplicates, Genome will use the Dictionary Print Name in your project unless you choose to
enter an alias in the Statement Alias column. You can enter any term in this column. It does not have to
be the same as your record name. For duplicates of any Dictionary term, Genome will insert your record
name in the Statement Alias column when you verify the mapping (see below). You can override this
term.
Verify Mapping
After completing the mapping process, or changing a mapping, you should verify the mapping. This
operation will be invoked automatically when you close the database. However, it is convenient to verify
the mapping as you work, to be sure that the print names are valid Excel labels and that the terms you are
entering are consistent.
Verify, Save, and Close Database
Once you are satisfied with your mapping, click >Verify, Save, and Close Database button to exit the
Map Database Operations mode. Your database file is now ready to be used in a Genome project.
Page 37 of 41
2009-7-1
Use Database in Genome Project
If you have a database workbook you have either linked to the dictionary or which was created
automatically from the INPUT sheet (see below), you can use it with a new project.
Open Financial Genome and click on {Home} >New Project tool button.
Proceed through each step of the New Project Wizard until you
reach Database Information – Step 4 of 5. Check Use Database
and Browse to find your linked database.
Click Next to enter the Time Information dialogue and to
complete the New Project Wizard operation.
Please be aware that connecting an arbitrary database to an arbitrary project will cause inconsistencies if
terms are aliased or duplicated differently. Therefore, unless the database file was created from the
project’s Input sheet (see below), specify that you want to use a database workbook at the time you
create a new project using the New Project Wizard.
Create a Genome database workbook from the INPUT Sheet
You can create a database workbook from the data contained in the INPUT sheet of your active project
which you can save as a free-standing database for use with your current as well as other new projects.
Click on the {Model} >Create Database (from Input Sheet) tool.
Check Create database from INPUT sheet and
click OK to save the linked database workbook.
This action automatically creates a linked Excel
workbook with the data from your INPUT Sheet
and connects the database with your current
project.
Page 38 of 41
2009-7-1
Addendum A: Financial Genome Pro Guidance (for users of QuickBooks)
Financial Genome Pro contains all the capabilities of Financial Genome Consultant plus additional
features designed for users of QuickBooks only. The following features are unique to FG Pro:
ƒ Custom Financial Genome Dictionary designed for QuickBooks users
ƒ You can automatically import account names and data directly from your QuickBooks file
ƒ You can automatically map your QuickBooks accounts to the FG Dictionary
ƒ You can revise account mappings with the Account Mapping Editor
ƒ Your forecasting model and formulas are automatically generated using the FG Dictionary for QB.
1.
2.
Open QuickBooks .qbw File for Company
Import Data from QuickBooks and Create New Forecast Project
ƒ
Run Financial Genome Pro: Start > Programs > Modernsoft > Financial Genome
ƒ
Click on the HOME tab and select IMPORT FROM QUICKBOOKS. A dialog box will
automatically open. Select Create New Forecast Project, click OK.
ƒ
3 files will automatically be created by FG (create a
special folder to save these files in):
Report (QBCompanyReport.xls)
Database (DBFromCompany.xls)
FG Project (Company.xls)
Account Mapping Editor: Financial Genome will try to find the Financial Genome dictionary term that
most closely matches the account you have used in QuickBooks. This matching process is usually
automatic when you use conventional account names. In other cases, Financial Genome will attempt to
find the best match by looking for keywords that appear in the account name. When this process is
completed, you will receive the following message:
Click OK to continue.
Page 39 of 41
2009-7-1
Note: The default mapping will be financially
correct. When a term cannot be matched to a
FG dictionary term using keywords, it will be
mapped to the term “OTHER” of the same
financial type. We recommend accepting the
initial mapping and reviewing the results in the
Financial Genome’s statements.
Click HELP for detailed instructions.
Click DONE when finished mapping and
proceed to create project file.
When project file is successfully created (this
may take a few minutes) you will receive
message:
Import from QuickBooks is complete
> click OK
Time Periods: FG automatically finds the last fiscal year data by month plus this fiscal year to date
data. The base year in the FG Project is the last period with actual data. All periods after the base period
are forecast periods. FG will automatically create additional forecast periods for the balance of the
current fiscal year. Note: The forecast time periods can be modified using the Time Wizard.
Page 40 of 41
2009-7-1
Financial Statements: The FG project file includes an Input Sheet plus 6 statements
Operating Expenses Detail
Income Statement
Balance Sheet
Cash Flow Statement
Ratios Statement
Forecast Drivers Statement
( You
are now in a Financial Genome project mode. Please refer to Sections II-V of this Tutorial for
additional guidance.
3.
Reconcile All Historical Data Values for Income Statement and Balance Sheet
Financial Genome automatically imports the data values from your QuickBooks file into the FG model.
Nevertheless, you should check all data values to make sure they reconcile to your Income Statement and
Balance Sheet. You can enter data values directly on any statement or on the Input Sheet. All sheets are
dynamically linked.
ƒ
ƒ
Is Net Income correct?
o If sign (+/-) for term is reversed, reenter data on INPUT SHEET with opposite sign.
Does Balance Sheet balance?
NOTE: Financial Genome automatically initializes the first period value for “Net Income
YTD” on the Balance Sheet by entering the value as an input for the initial period.
o
This is ONLY REQUIRED FOR THE INITIAL TIME PERIOD! This is because QB has a
unique method for showing Retained Earnings as two separate components: “Retained Earnings –
beginning balance” and “Net Income – YTD”.
o
For all subsequent periods (after period 1), if a “Balance Sheet Out-Of-Balance” situation exists
as shown at the bottom of the Balance Sheet, simply enter a like amount for the “Retained
Earnings Adjustment” shown at the bottom of the Balance Sheet. FG will automatically enter
an adjustment value for the initial period for current fiscal year if needed.
ƒ
The Cash Flow and Ratio Statements are automatically generated from the Income Statement and
Balance Sheet.
ƒ
You may provide additional detail for Cash Flow inputs; if available, enter Capital Expenditures,
Proceeds from Long-term debt, Repayment of Long-term debt, etc.
Page 41 of 41
2009-7-1