CSV Import Instructions – Revised 9/14/2017

TheCSV Import utility allows a user to import model data from a prepared CSV excel file into the Foresight software. Unlike other import functions in Foresight, you will not create row match rules. This download uses the names of the accounts in your chart.

If the utility cannot find the matching account by name, it does not import that row of data.

Foresight Model Preparation

  1. Back-up support package before beginning the process under Manage Database

Connections

-Select Close after back-up is completed

2.Create budget or forecast in Edit Forecast / Budgets

- Select currentBase Forecast

- Insert New Item

-Complete Steps 1 – 5

1 – Select Clone

- In Clone From: select down arrow and pickBase Forecast

2 – Type in name of budget/policy (i.e. 2018 Budget)

3 – Leave the Dates alone

4 – Choose Time Period set that will take you through end of budget year

If you are updated through Sept, recommend15 months (3 months

remainder of current year plus 12 months budget year)

5 – Choose Default Rate Environment (recommend Flat Rates)

6 – Select Done – model will create budget forecast/policy

3.Select Update Account Assumptions

- Selectnewly created budget forecast

-Walk the chart and make sure all the accounts you want to import data in are set

to the following:

- Method – Enter Values

- Method Options – BalancesorOriginations for balance sheet accounts

- Method Options – Balances for Income Expense accounts

- IMPORTANT - UncheckUse same values for all forecast periods

- Accounts cannot have a formula on it

Data File Preparation

As a starting point we recommend:

- Run a forecast balance sheet and income statement in Foresight and save to

CSV format as a worksheet for budget preparation

- This file must be in a CSV format

- 1st column – MUST BEthe exact name of the account in Foresight

****Critical – please review all accounts and make sure you DO NOT include

any balancing accounts or system assigned accounts. Below is a screenshot from the Institutions tab, Update the setting for this Institution. Any accounts listed

under Account Assignedcannot be included in your spreadsheet to change target method.

- 2nd column – must start with first month of forecast

- IMPORTANT - if you do not want to update balances in the month’s prior to theBudgetstart date,or any month in budget year,you mustleave the current balance(s) there or enter zero values – the cell cannot be left blank, have a dash or say N/A. We recommend deleting all lines that will not be imported using the same target method.

Income Statement

Important – delete the loan and investmentIncome accounts along with deposits and borrowings and interest expense accounts.

Theseaccounts will be calculated based on the related balance sheet accounts and forecasted yield.

Sample Income Statement CSV – Enter Values –Income/Expense

Budget
Forecast / StartMo.
Statement of Income / Oct-17 / Nov-17 / Dec-17 / Jan-18 / Feb-18 / Mar-18 / Apr-18
Origination Fees / 0 / 0 / 0 / 8795.5 / 8809.00 / 8836.0 / 8925.0
ATM Fees / 0 / 0 / 0 / 8175.0 / 8084.9 / 7627.3 / 6551.70
Credit Card Fees / 0 / 0 / 0 / 4400.0 / 4400.0 / 4400.0 / 4400.0.0
Salaries / 0 / 0 / 0 / 136500 / 1365000 / 136500 / 136500
Benefits / 0 / 0 / 0 / 10000.0 / 10000.0 / 10000.0 / 10000.0

Balance Sheet

Note – for balance sheet accounts create a separate CSV file for each target method (i.e. originations, balances).

- Run a balance sheet to CSV file

- Open file in excel

- Select File, Save As (CSV (Comma delimited) (*.csv) and name file (i.e.

Originations 2018.csv)

- Input originations in the appropriate accounts and months

- Delete accounts that are using other target methods

- Delete all sub-totals if you like

- Select File, Save, Yes

- Select File, Close, No

- Open original balance sheet CSV file

- Input ending balances on appropriate accounts

-Delete accounts using originations

- Delete accounts that are using other target methods

- Delete all sub-totals if you like

- Select File, SaveAs (CSV (Comma delimited) (*.csv) and name file (i.e.

Balance Targets 2018.csv)

- Select File, Close, No

Sample Balance Sheet CSV – Enter Values - Balances

Budget
Forecast / StartMo.
Statement of Condition / Oct-17 / Nov-17 / Dec-17 / Jan-18 / Feb-18 / Mar-18 / Apr-18
New Vehicle Loans / 98884.76 / 98884.76 / 98884.76 / 98884.76 / 95245.68 / 91739.85 / 88330.7
Signature Loans / 648286.9 / 648286.9 / 648286.9 / 648286.9 / 659356.1 / 670425.4 / 681494.6
Visa Loans / 5110637 / 5110637 / 5110637 / 5110637 / 5101871 / 5093105 / 5084339

Sample Balance Sheet CSV – Enter Values - Originations

Budget
Forecast / StartMo.
Statement of Condition / Oct-17 / Nov-17 / Dec-17 / Jan-18 / Feb-18 / Mar-18 / Apr-18
Home Equity Loans / 0 / 0 / 0 / 10000.00 / 0.00 / 25000.00 / 5000.00
Fixed Rate Mtg Loans / 0 / 0 / 0 / 100000.00 / 100000.00 / 0.00 / 0.00
Heloc Prime / 0 / 0 / 0 / 0.00 / 5000.00 / 0.00 / 5000.00

Using the Import

Select Load Recent Historyfrom All Tasks

Clicking on the link will take you to the Load Recent History screen and you will click on the link Import CSV File:

You will then get the followingscreen:

Below are the general descriptions for each option:

Import File: Browse to the file location of your prepared CSV file

History/Forecast: Choose the forecast/policy you want to import the data into

Start of Import: Indicate the starting period of this budget/policy

***Special Note – model will not let you select a forecast period beyond firstmonth of forecast

Balance Type: Choose appropriate balance type to import, Amortized Costor Originations for the Balance Sheet or Income/Expense for the Income Statement.

Ignore Header Lines: Optional

Ignore Footer Lines: Optional

Import Type: Choose either Balance Sheet or Income Statement

Select theStart button to run the import

When import is done it will say Finishedat the top of screen

Screen Image of Balance Sheet Balances Import

Import File: Browse to the file location of your prepared CSV file

History/Forecast: 2018 Budget (or the name of your budget)

Start of Import: Sep-2017

***Special Note – model will not let you select a forecast period beyond first month of forecast

Balance Type: Amortized Cost

Ignore Header Lines: Optional

Ignore Footer Lines: Optional

Import Type: Balance Sheet

Select the Start button to run the import

When import is done it will say Finished at the top of the screen

Screen Image of Balance Sheet Originations Import

Import File: Browse to the file location of your prepared CSV file

History/Forecast: 2018 Budget (or the name of your budget)

Start of Import: Sep-2017

***Special Note – model will not let you select a forecast period beyond first month of forecast

Balance Type: Originations

Ignore Header Lines: Optional

Ignore Footer Lines: Optional

Import Type: Balance Sheet

Select the Start button to run the import

When import is done it will say Finished at the top of the screen

Screen Image of Income/Expense Import

Import File: Browse to the file location of your prepared CSV file

History/Forecast: 2018 Budget (or the name of the your budget)

Start of Import: Sep-2017

***Special Note – model will not let you select a forecast period beyond first month of forecast

Balance Type: IncomeExpense

Ignore Header Lines: Optional

Ignore Footer Lines: Optional

Import Type: Income Statement

Select the Start button to run the import

When import is done it will say Finishedat the top of the screen

Go to Update Account Assumptions, select the Forecast/Policy you loaded data to and verify the balances. This can also be done by running a forecast balance sheet or income statement.

Should you need to make corrections to the CSV file, simply do that in excel, save the file again and repeat the import process. The balances will be replaced every time you import.