# Planning and budgeting calculation examples for BI planning solutions and scenarios

Applies to: SharePoint Server 2010 Enterprise

## Planning and budgeting calculation examples

### Cube calculations

The following is an example of how we can do some simple calculations in MdxScript for driving the HR Budget model.

#### Base pay calculation

Calculate the base pay for hourly employees based on the number of hours worked and the hourly wage rate as determined by the pay grade.

```// All calculations on HR done at leaf level
SCOPE
(
[Employee].[All_Employee].members
, DESCENDANTS([Geography].[Geographies].[All], 1000, LEAVES)
, [Measures].[Value]
);

[Metric].[Metrics].[Base] =

ELSE NULL
END;

END SCOPE;
```

#### Benefit calculation

Calculation to determine the estimated the benefits dollars based on the base pay.

```// All calculations on HR done at leaf level
SCOPE
(
[Employee].[All_Employee].members
, DESCENDANTS([Geography].[Geographies].[All], 1000, LEAVES)
, [Measures].[Value]
);
// Benefit averaged out to 25% of base
[Metric].[Metrics].[Benefit] = [Metric].[Metrics].[Base] * 0.25;

END SCOPE;

```

#### Total compensation calculation

Total compensation calculation based on base compensation and benefit dollars.

```// All calculations on HR done at leaf level
SCOPE
(
[Employee].[All_Employee].members
, DESCENDANTS([Geography].[Geographies].[All], 1000, LEAVES)
, [Measures].[Value]
);

// Total = base + benefits
[Metric].[Metrics].[Total] = [Metric].[Metrics].[Base] + [Metric].[Metrics].[Benefit];

END SCOPE;

```

### Stored procedure calculations

Many planning solutions require a currency translation rule that converts financial data into multiple currencies. Here, we will explore an example of a currency conversion rule implemented by a stored procedure. To perform currency translation, we need the following:

• Exchange Rate table holding to the conversion rates from a source currency to destination currency and by time period.

• A fact table that holds all the values that require translation.

Because we have a model designed for storing exchange rates, we can use its fact table as the exchange rate table.

### T-SQL for currency translation

```SELECT
a.MemberName [Account]
,t.MemberId [Time]
,s.MemberName [Scenario]
,g.MemberName [Geography]
,c.MemberName [CurrencyType]
,g.[Input Currency]
,g.[Reporting Currency]
,Fact.[Value]
,ExchangeRate.Value [Exchange Rate]
,Fact.[Value]*ExchangeRate.Value [Calculated]
FROM [dbo].[F_Financial Consolidation_CoreMG_Writeback] Fact
INNER JOIN D_Account a
ON Fact.AccountID = a.MemberId
INNER JOIN D_Time t
ON Fact.TimeID = t.memberid
INNER JOIN D_Scenario s
ON Fact.GeographyID = s.MemberId
INNER JOIN D_Geography g
ON Fact.ScenarioID = g.MemberId
INNER JOIN d_currencyType c
ON Fact.currencyTypeID = c.MemberId
---
--- Currency Join
---
INNER JOIN
(SELECT
sc.MemberName [Source]
,dc.MemberName [Destinatation]
,t.MemberId [Time]
,[Value]
FROM [F_Exchange Rates_CoreMG_Writeback] ef
INNER JOIN D_SourceCurrency sc
ON sc.MemberId = ef.SourceCurrencyID
INNER JOIN D_DestinationCurrency dc
ON dc.MemberId = ef.DestinationCurrencyID
INNER JOIN D_Time t
ON t.MemberId = ef.TimeID
WHERE sc.MemberId <> dc.MemberId) ExchangeRate
ON ExchangeRate.Source = g.[Input Currency]
AND ExchangeRate.Destinatation = g.[Reporting Currency]
```

Show: