Creating Column Sets
Budget administrators often must create different views of the company budget to allow decision makers to see the bigger picture. Not only can creating multiple column layouts be time-consuming, serious accuracy concerns arise when data from multiple sources must be rekeyed into a spreadsheet.

Microsoft Forecaster solves this problem by allowing administrators to create custom column sets with up to 100 columns to facilitate specialized views for easier analysis. An administrator can build any number of column sets to provide budget information, such as budget only, actuals vs. budgets, or rolling forecasts.

 Note
A column Set is a required component of the input set (discussed in next section).

The following steps walk you through using the Column Set wizard to create a column for a 2006 budget input screen.

1.	Click the Setup link on the left side of the screen. You will automatically be in the Views submenu. Click the Column Set Wizard (the magic wand) icon.
2.	Name your column set INPUT_B6. Change the radio button to Input and click Next.
3.	Select the Monthly Periods radio button and click Next.
4.	Select your B6 – 2006 Budget version. Hold down the Shift key click on the JAN and DEC periods (highlighting all), and click the Add button. Select the Click to add a total column option. (Figure 1 shows how your screen will look.) Click Next. Figure 1 Column Set wizard
5.	The following step allows you to either lock a period or define human resources (HR) details in this period. We will be utilizing the HR details within our input screen, so accept the HR PERIOD selection for each of the columns shown. Click Next.
6.	Click Finish, and then click Return to Setup.
7.	Click the Column Worksheet (looks like a spreadsheet) icon. Select the INPUT_B6 column from the drop-down box in the upper-left corner of your screen. Your completed column will match that shown in Figure 2. Notice your TOTAL column is a column type T. This column will automatically add all type M (monthly) columns. Figure 2 Completed INPUT_B6 column set

 Note
Column sets can support up to 100 columns, while versions contain up to 13 periods. To create an input set with more than 13 periods, you must use multiple versions within your column set.

Top

Creating input sets
Every budget administrator has experienced the pain of creating individual spreadsheets so that business managers can input data for their individual cost centers. When doing so, extra steps must be taken to ensure that expenses are captured in the relevant cost center, there are no duplicate entries across cost centers, and all data is accounted for. Naturally, this process is time-consuming for everyone involved.

But with Microsoft Forecaster, you can quickly create customized input sets for each line-of-business manager or cost center to ensure all data is accounted for. This means each budget contributor knows exactly what he or she is responsible for. In addition, the budget administrator saves a significant amount of time by not having to audit each budget version.

To create an input set, we will use the column set we just created as a basis. This input set will then be available for data entry by clicking the Input link on the left side of your screen.

1.	Click the Administration link on the left menu. Click the Input SetWorksheet (looks like a spreadsheet) icon.
2.	Click the Insert (plus sign) icon to create a new record. (You may also right-click and select Insert from the menu.)
3.	Assign the INPSET ID of BUD2006, with a label of Budget 2006. Place an uppercase X in the Input field and INPUT_B6 in the COLSET field. Finally, select the B6 version from the primary version drop-down menu. Figure 3 shows what your input set will look like. Figure 3 BUD2006 input set specifications
4.	Click the Save (the floppy disk) icon.
5.	Click the Input link on the left menu. You will be prompted to select a center. Select the 000 Corporate center from the selection window. Figure 4 shows what your screen will look like. Notice there are no rows available for data entry. Figure 4 Budget 2006 Input screen
6.	Click the Insert icon to insert an account. You will see a selection window listing all posting level accounts within your account worksheet. You may add accounts one at a time using this method, although most users prefer to define a line set with the accounts they want to use for budgeting purposes.

Top

Performing Advanced Calculations
In a traditional budgeting world, organizations create budgets based on drivers or key factors. These calculations can be as simple as budget less actuals to variance, or more complex, such as number of units sold multiplied by certain percentages for each line item. Or they can be as complex as tying a bill of material to the units sold. Then when the number of units sold is input, you can see how much raw material, labor hours, and other resources are needed to keep up with the units projected to be sold.

Budget administrators spend a lot of time creating macros and complex calculations in the spreadsheets so that budget contributors can quickly prepare a baseline budget. Business line managers (or budget contributors) are often unaware of the complexity of these macros and calculations and can easily, inadvertently cause inaccurate totaling by adding new rows or columns to the spreadsheet.

But Microsoft Forecaster allows administrators to quickly define custom calculations that drive the numbers. And because of the application’s controlled environment, the custom calculations are protected, and do not require constant auditing. This helps everyone in the organization quickly input their budget numbers, set more accurate goals and expectations, and benefit from more analysis and reforecasting.

In Microsoft Forecaster, calculation sets allow you to create a line, a column, or an element (single cell) calculation. Figure 5 shows the calculation set fields.


Figure 5 Calculation set fields

Calculation Set Types

Column Calculation (C). Use to calculate columns of data. Most commonly used for variances. Note that for column calculations, you must reference the IDs in your column set.
Example: C XVAR = BUD - ACT

This formula calculates column XVAR as the difference between column BUD and column ACT.

Line Calculation (L). Use to calculate lines (rows) of data.
Example: L GROSS = '4000 - ' 4500

This formula calculates line GROSS (gross margin) as the difference between account 4000 and account 4500. Note the use of a single quote (‘) to denote an account number. If you forget the single quote, Microsoft Forecaster will calculate the result based on natural numbers, in this case -500.

Element Calculation (E). Use to calculate single cells of data. Type E formulas work like cross-sections. To reference a cell, enter the column ID first, followed by a period, followed by the line ID.
Example: E A31.200 = A31.300 / 5

This formula calculates account 200 for column A31 as account 300 for column A31, divided by 5.

Calculation Set Rules

1. Your formulas must reference IDs that you use in your line sets and column sets.



2. You must include an apostrophe before numeric codes; otherwise, Microsoft Forecaster will consider the code to be a number.

Example:
TOT = ‘100 + ‘200
TOT = 100 + 200

The first formula calculates Account TOT as the sum of accounts 100 and 200. The second formula calculates Account TOT as 300.


3. Microsoft Forecaster computes your formulas sequentially—so put them in order accordingly.


Note
Right:
SUB1 = ‘1000 + ‘2000
SUB2 = ‘3000 + ‘4000
TOTL = SUB1 + SUB2

Wrong:
TOTL = SUB1 + SUB2
SUB1 = ‘1000 + ‘2000
SUB2 = ‘3000 + ‘4000


4. When you have formulas that extend more than one line, you must extend "Type", and end continuing lines with an operator (+, -, and so on).

Example:
L TOTAL = '11010 + '11020 + '11030 +' 11040 + '11050 + '11060
+
L '11070 + '11080 + '11090 + '11200 + '11210 + '11220


5. Ranges may be used for groups of accounts by entering two periods (..) between the two account IDs. All accounts within the physical range in the line set (not the numeric range of account IDs) will be summed. Accounts in the beginning and ending positions will automatically display on the input screen.


Example: L‘11010..’11220

Next Step: Contact Brittenford Systems to discuss your unique needs.