BPC320_EN_Col91_FV_Part_A4 (第2页)

第 125 页 / 共 377 页

Unit 4

Session 4

Unit Overview

Aftercompletingthisunityouwillunderstandbudgetingsystembestpractices;

knowhowtoutilizetheDataManager, transformation, andconversionfilesto

seed next year’s budget; add an MDX calculation to a script logic file; create

budgetinginputscheduleswithEVDRE;andcreateabudgetingBPF.

Unit Objectives

After completing this unit, you will be able to:

• understandbudgetingsystembestpracticesandknowhowtoutilizetheData

Manager,transformation,andconversionfilestoseednextyear’sbudget.

• addanMDXcalculationtoyourDefault.lgfscriptlogicfile.

• defineandcreatebudgetinginputschedules.

• understandviewabledataconsiderationswhencreatinganEVSNDoran

EVDRE input schedule.

• createandtestabudgetingBPF,aswellasreportonBPFs.

• create top down modeling that allows you to plan on a non-base level

member.

Unit Contents

Lesson: Budgeting Overview..................................................112

Lesson: MDX Calculation......................................................119

Lesson: Budgeting Input Schedules..........................................123

Lesson: Viewable Data.........................................................125

Lesson: Budgeting and BPFs.................................................128

Lesson: Top Down Modeling..................................................136

111

第 126 页 / 共 377 页

Unit4: Session4 BPC320

Lesson: Budgeting Overview

Lesson Overview

Lesson Objectives

Aftercompletingthislesson, youwillbeableto:

• understandbudgetingsystembestpracticesandknowhowtoutilizetheData

Manager,transformation,andconversionfilestoseednextyear’sbudget.

Business Example

Figure 151: Budget Approach Considerations

112

第 127 页 / 共 377 页

BPC320 Lesson: Budgeting Overview

Figure 152: Best Practice: Multiple iterations

Figure 153: Modeling Requirements

113

第 128 页 / 共 377 页

Unit4: Session4 BPC320

Figure 154: Level of Detail Options

Figure 155: Modeling Requirements

Figure 156: Budgeting Tips

Figure 157: Seeding Options

114

第 129 页 / 共 377 页

BPC320 Lesson: Budgeting Overview

Figure 158: Transformation Files

Figure 159: Transformation Files: *OPTIONS

115

第 130 页 / 共 377 页

Unit4: Session4 BPC320

Figure 160: Transformation Files: *MAPPING

Figure161: TransformationFiles: *CONVERSION

Figure 162: Conversion Files

116

第 131 页 / 共 377 页

BPC320 Lesson: Budgeting Overview

Figure163: ConversionFiles: ImportsandExports

Lab 33 – Seeding the Budget

(part of the last unit)

117

第 132 页 / 共 377 页

Unit4: Session4 BPC320

Lesson Summary

You should now be able to:

• understandbudgetingsystembestpracticesandknowhowtoutilizetheData

Manager,transformation,andconversionfilestoseednextyear’sbudget.

118

第 133 页 / 共 377 页

BPC320 Lesson: MDX Calculation

Lesson: MDX Calculation

Lesson Overview

Lesson Objectives

Aftercompletingthislesson, youwillbeableto:

• addanMDXcalculationtoyourDefault.lgfscriptlogicfile.

Business Example

Figure 164: Budget Method

Figure 165: Logic Types

119

第 134 页 / 共 377 页

Unit4: Session4 BPC320

Figure 166: Dimension Logic Considerations

Figure 167: Dimension Logic Example

Figure 168: Script Logic Considerations

Figure 169: Script Logic Example

120

第 135 页 / 共 377 页

BPC320 Lesson: MDX Calculation

Figure 170: MDX Considerations

Lab 34 – Creating the External Sales Calculation

(part of the last unit)

Lab 35 – Verifying the External Sales Calculation

(part of the last unit)

121

第 136 页 / 共 377 页

Unit4: Session4 BPC320

Lesson Summary

You should now be able to:

• addanMDXcalculationtoyourDefault.lgfscriptlogicfile.

122

第 137 页 / 共 377 页

BPC320 Lesson: Budgeting Input Schedules

Lesson: Budgeting Input Schedules

Lesson Overview

Lesson Objectives

Aftercompletingthislesson, youwillbeableto:

• defineandcreatebudgetinginputschedules.

Business Example

Figure 171: Budgeting Input Schedules

Figure 172: Built-in modeling functions

Lab 36 – Creating Additional Budgeting Input

Schedules

(part of the last unit)

123

第 138 页 / 共 377 页

Unit4: Session4 BPC320

Lesson Summary

You should now be able to:

• defineandcreatebudgetinginputschedules.

124

第 139 页 / 共 377 页

BPC320 Lesson: Viewable Data

Lesson: Viewable Data

Lesson Overview

Lesson Objectives

Aftercompletingthislesson, youwillbeableto:

• understandviewabledataconsiderationswhencreatinganEVSNDoran

EVDRE input schedule.

Business Example

Figure 173: Viewable Data Considerations

Figure174: ViewableDataConsiderations: EVGTS/EVSND

125

第 140 页 / 共 377 页

Unit4: Session4 BPC320

Figure175: ViewableDataConsiderations: EVDRE

Figure 176: Comparative Reporting: EVGTS

Lab 37 – Creating a Budget Template with Viewable

Areas

(part of the last unit)

126

第 141 页 / 共 377 页

BPC320 Lesson: Viewable Data

Lesson Summary

You should now be able to:

• understandviewabledataconsiderationswhencreatinganEVSNDoran

EVDRE input schedule.

127

第 142 页 / 共 377 页

Unit4: Session4 BPC320

Lesson: Budgeting and BPFs

Lesson Overview

Lesson Objectives

Aftercompletingthislesson, youwillbeableto:

• createandtestabudgetingBPF,aswellasreportonBPFs.

Business Example

Figure 177: Business Process Flows

Figure 178: Business Process Flow Definitions

Figure 179: BPF Design

128

第 143 页 / 共 377 页

BPC320 Lesson: Budgeting and BPFs

Figure180: AddingaNewBusinessProcessFlow

Figure 181: Setup BPF: Define BPF

129

第 144 页 / 共 377 页

Unit4: Session4 BPC320

Figure 182: Setup BPF: Define Data Region

Figure 183: Setup BPF: Define Timing

130

第 145 页 / 共 377 页

BPC320 Lesson: Budgeting and BPFs

Figure 184: Setup BPF: Set Access

Figure 185: Define Steps/Sub-steps

131

第 146 页 / 共 377 页

Unit4: Session4 BPC320

Figure 186: Define Actions

Figure 187: BPF: BPC Web

132

第 147 页 / 共 377 页

BPC320 Lesson: Budgeting and BPFs

Figure 188: Resetting an Instance

Figure 189: Resetting a Step

Figure 190: Business Process Flow Reporting

133

第 148 页 / 共 377 页

Unit4: Session4 BPC320

Figure 191: Business Process Flow Reporting

Figure 192: Business Process Flow Reporting

Lab 38 – Creating a Budgeting Business Process Flow

(part of the last unit)

134

第 149 页 / 共 377 页

BPC320 Lesson: Budgeting and BPFs

Lesson Summary

You should now be able to:

• createandtestabudgetingBPF,aswellasreportonBPFs.

135

第 150 页 / 共 377 页

Unit4: Session4 BPC320

Lesson: Top Down Modeling

Lesson Overview

Lesson Objectives

Aftercompletingthislesson, youwillbeableto:

• create top down modeling that allows you to plan on a non-base level

member.

Business Example

Figure 193: Top Down Modeling

Figure194: TopDownModelingInputSchedule

136

第 151 页 / 共 377 页

BPC320 Lesson: Top Down Modeling

Figure 195: Top Down Modeling Calculation

Lab 39 – Top Down Modeling and Distribution

(part of the last unit)

137

第 152 页 / 共 377 页

Unit4: Session4 BPC320

Lesson Summary

You should now be able to:

• create top down modeling that allows you to plan on a non-base level

member.

138

第 153 页 / 共 377 页

BPC320 Unit Summary

Unit Summary

You should now be able to:

• understandbudgetingsystembestpracticesandknowhowtoutilizetheData

Manager,transformation,andconversionfilestoseednextyear’sbudget.

• addanMDXcalculationtoyourDefault.lgfscriptlogicfile.

• defineandcreatebudgetinginputschedules.

• understandviewabledataconsiderationswhencreatinganEVSNDoran

EVDRE input schedule.

• createandtestabudgetingBPF,aswellasreportonBPFs.

• create top down modeling that allows you to plan on a non-base level

member.

139

第 154 页 / 共 377 页

UnitSummary BPC320

140

第 155 页 / 共 377 页

第 156 页 / 共 377 页

第 157 页 / 共 377 页

Unit 5

Session 5

Unit Overview

After completing this unit you will understand foreign currency translation,

inter-companyeliminations, andjournalsfrombothanadministrativeandend

userperspective, aswellashowtoworkwithInsight.

Unit Objectives

After completing this unit, you will be able to:

• setupforeigncurrencytranslationinBPCAdministration,aswellasexecute

foreigncurrencytranslationinBPCforExcel.

• setupinter-companyeliminationsinBPCAdministration,aswellasexecute

inter-companyeliminationsinBPCforExcel.

• setupthejournaltemplateandjournalsecurityinBPCAdministration,as

wellascreateandreportonjournalsinBPCforExcel.

Unit Contents

Lesson: Foreign Currency Translation.......................................142

Lesson: Inter-company Eliminations .........................................151

Lesson: Journals................................................................157

141

第 158 页 / 共 377 页

Unit5: Session5 BPC320

Lesson: Foreign Currency Translation

Lesson Overview

Lesson Objectives

Aftercompletingthislesson, youwillbeableto:

• setupforeigncurrencytranslationinBPCAdministration,aswellasexecute

foreigncurrencytranslationinBPCforExcel.

Business Example

Figure 196: Foreign Currency Translation

Figure 197: FX Setup: Rate Application

142

第 159 页 / 共 377 页

BPC320 Lesson: Foreign Currency Translation

Figure 198: FX Setup: System Constants File

Figure 199: Updating System Constants

143

第 160 页 / 共 377 页

Unit5: Session5 BPC320

Figure200: FXSetup: CurrencyTypeDimension

Figure201: UtilizingMultipleCurrencyTypeDimensions

144

第 161 页 / 共 377 页

BPC320 Lesson: Foreign Currency Translation

Figure202: FXSetup: CurrencyPropertyinEntityDimension

Figure203: FXSetup: RateTypePropertyinAccountDimension

145

第 162 页 / 共 377 页

Unit5: Session5 BPC320

Figure204: ForeignCurrencyTranslationExecution: RealTime

Figure 205: INCLUDE

Figure 206: FXTrans Logic File

146

第 163 页 / 共 377 页

BPC320 Lesson: Foreign Currency Translation

Figure 207: System Library

Figure 208: MultiCurrencyTrans Logic File

147

第 164 页 / 共 377 页

Unit5: Session5 BPC320

Figure209: DisablingRealTimeForeignCurrencyTranslation

Figure210: ForeignCurrencyTranslationExecution: DataManager

Figure211: ForeignCurrencyTranslationProcess

148

第 165 页 / 共 377 页

BPC320 Lesson: Foreign Currency Translation

Figure212: ForeignCurrencyTranslationProcess(continued)

Lab 40 – Foreign Currency Translation

(part of the last unit)

149

第 166 页 / 共 377 页

Unit5: Session5 BPC320

Lesson Summary

You should now be able to:

• setupforeigncurrencytranslationinBPCAdministration,aswellasexecute

foreigncurrencytranslationinBPCforExcel.

150

第 167 页 / 共 377 页

BPC320 Lesson: Inter-company Eliminations

Lesson: Inter-company Eliminations

Lesson Overview

Lesson Objectives

Aftercompletingthislesson, youwillbeableto:

• setupinter-companyeliminationsinBPCAdministration,aswellasexecute

inter-companyeliminationsinBPCforExcel.

Business Example

Figure 213: Inter-company Eliminations

Figure214: ICEliminationsSetup: IntCoDimension

151

第 168 页 / 共 377 页

Unit5: Session5 BPC320

Figure215: ICEliminationsSetup: ICElimScriptLogicFile

Figure 216: ICElimWithCurr Logic File

152

第 169 页 / 共 377 页

BPC320 Lesson: Inter-company Eliminations

Figure217: ICEliminationsSetup: DataManagerPackage

Figure218: ICEliminationsSetup: ELIMPropertyinEntityDimension

153

第 170 页 / 共 377 页

Unit5: Session5 BPC320

Figure219: ICEliminationsSetup: EliminationEntities

Figure220: ICEliminationsSetup: INTCOPropertyinEntityDimension

154

第 171 页 / 共 377 页

BPC320 Lesson: Inter-company Eliminations

Figure221: ICEliminationsSetup: EliminationAccounts

Figure222: ICEliminationsSetup: OptionalTEMPLATEProperty

Lab 41 – Inter-company Eliminations

(part of the last unit)

155

第 172 页 / 共 377 页

Unit5: Session5 BPC320

Lesson Summary

You should now be able to:

• setupinter-companyeliminationsinBPCAdministration,aswellasexecute

inter-companyeliminationsinBPCforExcel.

156

第 173 页 / 共 377 页

BPC320 Lesson: Journals

Lesson: Journals

Lesson Overview

Lesson Objectives

Aftercompletingthislesson, youwillbeableto:

• setupthejournaltemplateandjournalsecurityinBPCAdministration,as

wellascreateandreportonjournalsinBPCforExcel.

Business Example

Figure 223: Journals Introduction

Figure 224: Journals Introduction (continued)

157

第 174 页 / 共 377 页

Unit5: Session5 BPC320

Figure 225: Setting up Journals

Figure 226: Journal Wizard

Figure227: JournalWizard: HeaderDimensions

158

第 175 页 / 共 377 页

BPC320 Lesson: Journals

Figure228: JournalEntryTemplate: DetailDimensions

Figure229: JournalEntryTemplate: AdditionalHeaderItems

159

第 176 页 / 共 377 页

Unit5: Session5 BPC320

Figure 230: Journal Wizard: Summary

Figure 231: Journal Security

Figure 232: Security Best Practice: Journals

160

第 177 页 / 共 377 页

BPC320 Lesson: Journals

Figure 233: Creating a New Journal Template

Figure 234: BPC for Excel: Creating Journals

161

第 178 页 / 共 377 页

Unit5: Session5 BPC320

Figure 235: Creating Journal Entries

Figure236: CreatingJournalEntries(continued)

162

第 179 页 / 共 377 页

BPC320 Lesson: Journals

Figure237: CreatingJournalEntries(continued)

Figure 238: Posting Journal Entries

Figure 239: Unposting Journal Entries

163

第 180 页 / 共 377 页

Unit5: Session5 BPC320

Figure 240: Reopening Journal Entries

Figure241: ReopeningJournalEntries(continued)

164

第 181 页 / 共 377 页

BPC320 Lesson: Journals

Figure242: ReopeningJournalEntries(continued)

Figure 243: Journal Manager

165

第 182 页 / 共 377 页

Unit5: Session5 BPC320

Figure 244: Journal Reports

Figure 245: Journal Reports (continued)

Lab 42 – Journals

(part of the last unit)

166

第 183 页 / 共 377 页

BPC320 Lesson: Journals

Lesson Summary

You should now be able to:

• setupthejournaltemplateandjournalsecurityinBPCAdministration,as

wellascreateandreportonjournalsinBPCforExcel.

167

第 184 页 / 共 377 页

UnitSummary BPC320

Unit Summary

You should now be able to:

• setupforeigncurrencytranslationinBPCAdministration,aswellasexecute

foreigncurrencytranslationinBPCforExcel.

• setupinter-companyeliminationsinBPCAdministration,aswellasexecute

inter-companyeliminationsinBPCforExcel.

• setupthejournaltemplateandjournalsecurityinBPCAdministration,as

wellascreateandreportonjournalsinBPCforExcel.

168

第 185 页 / 共 377 页

第 186 页 / 共 377 页

第 187 页 / 共 377 页

Unit 6

Labs

Unit Overview

Unit Objectives

After completing this unit, you will be able to:

•

Unit Contents

Lesson: Labs....................................................................170

169

第 188 页 / 共 377 页

Unit6: Labs BPC320

Lesson: Labs

Lesson Overview

Lesson Objectives

Aftercompletingthislesson, youwillbeableto:

•

Business Example

Lab 1a – Converting a Worksheet for EVSND Input

Creating a New Application Set

Objective: UseSAPBusinessPlanningandConsolidationfunctionstoconvertan

existingExcelspreadsheetsoitmaybeusedinBPCforExcelasaninputschedule.

Description: BPCfunctionsallowyoutocreateworkbooksthatrequireminimal

ongoingmaintenance. TheEVSND(Send)functionreferencesanothercelland

sendsitsvaluetothedatabasewhenaSendisinitiated. Theargumentsspecified

intheEVSNDfunctionidentifywherethedatavalueistobesentinthedatabase.

AnydimensionthatisnotspecifiedinthefunctionwillbetakenfromtheCurrent

View. EVSND also returns the value from the database to the referenced cell.

This is useful for two reasons:

1. YoudonothavetoaddanyEVGTSfunctionstothesheetinordertoreturn

values to the cell;

2. Becausethevaluesarereturnedtothereferencedcell,BPConlysendsthose

cellsthathavechangedsincethelastsendinthecurrentsession.

System Data

System: CITRIXCommonTrainingenvironment

Client: Start/Programs/Accessories/RemoteDesktopConnection

User ID: CPM-##(with##=monitornumber)

Password: training

Logon Information

YouareworkingintheCITRIXCommonTrainingenvironmentandyourtrainer

will explain how to access this environment.

170

第 189 页 / 共 377 页

BPC320 Lesson: Labs

Withinthisenvironment,clickStart/Programs/Accessories/RemoteDesktop

Connection.

ForComputer,enterthesystemgiventoyoubythetrainerandclickConnect.

In the next pop-up fill in the following:

UserName: CPM-## (where##isyourgroupnumber)

Password: training (all lower case)

Logonto/Domain ADTWDFVM2001

Note: The password cannot be changed.

Note: If for any reason the third field does not appear be sure to add

ADTWDFVM2001\atthebeginningofyourUserNameinstead.

Click OK.

1. FromyourWindowsdesktop, double-clickonthe

BusinessPlanningandConsolidationshortcuticon.

Note: Ifpromptedforausernameandpasswordenter:

UserName: CPM-##(where##representsyourgroupnumber)

Password: training

2. From the Launch Page, click on the

BPC Administration shortcut icon.

3. FromtheActionPane,clickManageApplicationSets.

4. FromtheBPCLogonwindow,clicktheConnectionWizardbutton,asshown

below:

171

第 190 页 / 共 377 页

Unit6: Labs BPC320

Figure 246: Connection Wizard 1

5. FromtheWelcometotheConnectionWizardwindow, leavethedefaultsas

is, and click Next.

6. From the BPC Credentials window, enter the Logon information as shown

below:

Field Value

UseadifferentuserIDandpassword Select

User ID cpm-##

Password training

Domain name ADTWDFVM2001

172

Field	Value
UseadifferentuserIDandpassword	Select
User ID	cpm-##
Password	training
Domain name	ADTWDFVM2001

第 191 页 / 共 377 页

BPC320 Lesson: Labs

Figure 247: Logon Information

Hint: Thedomainnamewillbegiventoyoubyyourinstructor.

7. Click Next.

8. FromtheApplicationsetinformationwindow,selectApShellasshownbelow:

9. Click Next.

10.Click Finish.

11.Verifyyourscreenlookssimilartothefollowing:

Figure 248: Select AppSet

12.Click OK.

13.Verifyyourscreenlookssimilartothefollowing:

173

第 192 页 / 共 377 页

Unit6: Labs BPC320

Figure 249: Item Select

14.FromtheActionPane,selectAddanewapplicationset.

15.Inthe“Whatisthenewapplicationsetname?”field,typePETDEPOT_##,

where ## represents your group number.

16.Inthe“Whatisthenewapplicationsetdescription?”field,typePetdepotfor

Group##,where##representsyourgroupnumber.

17.In the “Which application set would you like to duplicate?” field, click the

downarrowandselectPETDEPOTfromthedropdownlist.

18.VerifyyourActionPanelookssimilartothefollowing:

174

第 193 页 / 共 377 页

BPC320 Lesson: Labs

Figure 250: Action Pane 1

19.Click Go to Next Step 2 of 2.

20.InStep2of2,leavethedefaultsasis,asshownbelow:

Figure 251: Action Pane 2

21.Click Add a new application set.

22.Whenprompted“Thetaskhasbeensuccessfullycompleted”clickOK.

175

第 194 页 / 共 377 页

Unit6: Labs BPC320

Lab 1b – Converting a Worksheet for EVSND Input

1. FromtheActionPane, underAvailableInterfaces, clickBPCforExcel, as

shown below:

Figure 252: Available Interfaces

2. FromtheLogonscreen,selectyourappsetPETDEPOT_##andtheApplication

Finance as shown below

Figure 253: Connection Wizard 3

Hint: Ifyouareunabletoseeyourapplicationsetyouneedtorefreshthe

screenusingtheconnectionwizardasbefore

3. Click OK.

4. Verifyyourscreenlookssimilartothefollowing:

176

第 195 页 / 共 377 页

BPC320 Lesson: Labs

Figure 254: Example 1

5. CreatingtheEVSNDInputScheduleTemplate

1. Copy the BPC320 files to your folder.

Select

Start→Programs→Accessories→WindowsExplorer

From the explorer select My Computer → Database →

CPM310&CPM320_Training Files → BPC70M Training Files →

Admin II

CopythecontentsoftheAdminIIfoldertoyourfolderCPM320-##,whichis

locatedintheCPM310&CPM320_Training→CPM320folder.

Caution: Becarefultocopyandnottomovethefiles

2. Exit Windows Explorer.

6. CreatingtheEVSNDInputScheduleTemplate

1. UsingtheExcelFilemenu,opentheEVSND.xlsCPM320-##folder.

File→Open→MyComputer→Database→CPM310&CPM320_Training

Files→CPM320-##. SelecttheEVSND.xlsspreadsheetandOpen.

Note: IgnoreanymessagesthatappearincolumnO.

177

第 196 页 / 共 377 页

Unit6: Labs BPC320

2. OntheInputsheet,selectcellA1andenterthewordsApplicationSet.

3. Select cell B1.

4. FromtheActionPane, selectInsertfunction.

Note: TheFunctionWizardcanalsobeaccessedviapull-downmenuby

selecting eTools/Function Wizard.

5. FromtheActionPane, selectEVASTandclickthe

green check button.

6. FromtheFunctionArgumentsdialogbox,clickOKtoinsertthefunctionin

cell B1.

7. SelectcellA2andenterthewordApplication.

8. Select cell B2.

9. FromtheActionPanewindow,selectEVAPPandclickthe

green check button.

10.FromtheFunctionArgumentsdialogbox,clickOKtoinsertthefunctionin

cell B2.

11.SelectcellA3andenterthewordsApplicationDescription.

12.Select cell B3.

13.FromtheActionPanewindow,selectEVAPDandclickthe

green check button.

14.FromtheFunctionArgumentsdialogbox,clickOKtoinsertthefunctionin

cell B3.

15.SelectcellA4andenterthewordsUserName.

16.Select cell B4.

17.FromtheActionPanewindow,selectEVUSRandclickthe

green check button.

18.FromtheFunctionArgumentsdialogbox,clickOKtoinsertthefunctionin

cell B4.

19.Select cell A5.

20.FromtheActionPanewindow,selectEVDIMandclickthe

green check button.

21.IntheFunctionArgumentsdialogbox,fortheAppNameargument,pointto

cell$B$2. FortheDimTypeargument, enterR.

22.Select OK to insert the formula in cell A5.

23.CopytheformulaincellA5tocellsA6andA7.

24.EdittheformulaincellA6to=EVDIM(Input!$B$2;“E”)andhitEnter.

178

第 197 页 / 共 377 页

BPC320 Lesson: Labs

25.EdittheformulaincellA7to=EVDIM(Input!$B$2;“C”)andhitEnter.

26.Select cell B5.

27.FromtheActionPane,selectEVCVWandclickthe

green check button.

28.IntheFunctionArgumentsdialogbox,fortheAppNameargument,pointto

cell $B$2. For the Dimension, enter $A5.

29.Select OK to insert the formula in cell B5.

30.CopytheformulaincellB5tocellsB6andB7.

31.Select cell G13.

32.FromtheActionPane, selectEVDESandclickthe

green check button.

33.IntheFunctionArgumentsdialogbox,fortheMemberargument,pointtocell

$B6. (Leave the AppName argument blank.)

34.Select OK to insert the formula in cell G13.

35.Copy the formula in cell G13 to cell G14.

36.Verifythatyourscreenlookssimilartothefollowing:

Figure 255: Example 2

37.Select cell R17.

38.FromtheActionPane, selectEVSNDandclickthe

green check button.

39.EntertheFunctionArgumentsasshowbelow:

Figure 256: Function Arguments

179

第 198 页 / 共 377 页

Unit6: Labs BPC320

40.Select OK to insert the formula in cell R17.

41.CopytheformulaincellR17acrossandthendown,throughcellAC38.

42.

Clickonthe RefreshWorkbookiconontheBPCforExceltoolbar.

43.From the Action Pane, click the

back arrow.

44.FromtheActionPane,clickSetworkbookoptions.

Note: TheCurrentWorkbookOptionsdialogboxcanalsobeaccessedvia

pull-downmenubyselectingeTools→WorkbookOptions.

45.FromtheTypedrop-downlistbox,selectInputSchedule.

46.IntheRefreshOptionssection, selectthecheckboxforRefreshafterdata

send.

47.Click OK.

48.Highlight columns R through AC and select Data → Group and Outline

→ Group.

49.Clickontheminus(–)signabovecolumnADtohidetheEVSNDcells.

50.HighlightcolumnAandselectData→GroupandOutline→Group.

51.Highlightrows1through10andselectData→GroupandOutline→Group.

52.Clickontheminus(–)signabovecolumnBandtotheleftofrow11tohide

the report definition area.

53.FromtheeToolsmenu,selectSaveDynamicTemplates.

54.

Clickonthe Companybuttonontheleftsideofthedialogbox, and

thenselecttheeEXCEL→InputSchedules→Managerfolder.

55.IntheFileNamefield,enterLab1–BudgetInputSchedule.

56.Click Save.

57.FromtheExcelFilemenu,selectClosetoclosethetemplate.

7. UsingtheEVSNDInputScheduleTemplate

1. Changeyourcurrentviewsettingstomatchthefollowing:

180

第 199 页 / 共 377 页

BPC320 Lesson: Labs

Figure 257: Current View Settings

2. From the Action Pane, click Data Input.

3. FromtheActionPane,clickOpenanexistingschedule.

Note: Youcanalsoopenaninputschedulefromtheserverviapull-down

menubyselectingeSubmit→OpenScheduleLibrary.

4. Open Lab 1 – Budget Input Schedule located in the Input Schedules →

Manager folder.

5. SelectRefreshandtheninputtheExternalSalesvaluefirst. ThenSendData

→ Active Worksheet → Yes.

ThenproceedbyinputtingthefollowingdataintotheJAN2009column:

Figure 258: JAN 2009 column

181

第 200 页 / 共 377 页

Unit6: Labs BPC320

6. SelectcellD17andenteraformulaof=C17*1,1

7. CopytheformulafromcellD17acrossandthendown,throughcellN38.

8. Verifythatyourschedulelookssimilartothefollowing:

Figure 259: Schedule

Note: Ifthereisdatatherealready,justoverwriteit.

Note: IgnoreanymessagesthatappearincolumnO.

9. From the Action Pane, click Send Data.

Note: You can also send data to the server via pull-down menu by

selectingeSubmit→SendandRefreshSchedules.

10.FromtheActionPane,selectSendactiveworksheet.

11.Whenprompted,“nrecord(s)ofDatawillbesent. Doyouwanttocontinue?”

click Yes.

12.Onceyourdatahasbeensuccessfullysent, clickClosetoclosetheRefresh

Schedule Results dialog box.

13.

FromtheActionPane, clicktheback arrow.

14.From the Action Pane, click Park N Go.

15.FromtheActionPane,clickSettooffline(noconnectivity).

16.FromtheeSubmitmenu,selectSaveMySchedule.

17.IntheFileNamefield,enterStore1InputScheduleandclickSave.

Note: Overwritethefileifitalreadyexistsplease.

182

第 201 页 / 共 377 页

BPC320 Lesson: Labs

18.FromtheExcelFilemenu,selectClosetoclosethefile.

19.IfpromptedtoSavechangesagain, clickNo.

Lab 2 – Creating an EVGTS Report

Objective: Utilize BPC reporting functions to create a report template from a

blank Excel workbook.

Description: TheEVGTS(GetValueandScale)functionretrievesadatavalue.

The arguments specified in the EVGTS function identify which data value to

retrievefromthedatabasebyspecifyingthedimensionmembersthatdescribe

thatdatavalue. Anydimensionthatisnotspecifiedinthefunctionwillbetaken

fromtheCurrentView. Theretrievedvaluewillbescaledifthescalepropertyis

enabled for the specified account.

System Data

System: CITRIXCommonTrainingenvironment

Client: Start/Programs/Accessories/RemoteDesktopConnection

User ID: CPM-##(with##=monitornumber)

Password: training

1. Creating the Report Template

1. FromtheActionPane,selectReporting&Analysis.

2. FromtheActionPane,selectOpenablankworkbook.

3. SelectcellA1andenterthewordApplication.

4. Select cell B1.

5. FromtheActionPane, selectInsertfunction.

6. FromtheActionPanewindow,selectEVAPPandclickthe

green check button.

7. FromtheFunctionArgumentsdialogbox,clickOKtoinsertthefunctionin

cell B1.

8. Select cell A2.

9. FromtheActionPane, selectEVDIMandclickthe

green check button.

183

第 202 页 / 共 377 页

Unit6: Labs BPC320

10.IntheFunctionArgumentsdialogbox,fortheAppNameargument,pointto

cell$B$1. FortheDimTypeargument, enterE.

11.Click OK to insert the formula in cell A2.

12.CopytheformulaincellA2tocellsA3andA4.

13.EdittheformulaincellA3to=EVDIM(Sheet1!$B$2;“T”)andhitEnter.

14.EdittheformulaincellA4to=EVDIM(Sheet1!$B$2;“R”)andhitEnter.

15.Select cell B2.

16.FromtheActionPane,selectEVCVWandclickthe

green check button.

17.IntheFunctionArgumentsdialogbox,fortheAppNameargument,pointto

cell $B$1. For the Dimension, enter $A2.

18.Click OK to insert the formula in cell B2.

19.CopytheformulaincellB2tocellsB3andB4.

20.Select cell A10 and type CostOfGoods.

21.Select cell A11 and type TotOpExp.

22.Select cell A12 and type TotalDeptExp.

23.SelectcellA13andtypeTotalPersonnelExp.

24.Select cell A14 and type Salaries.

25.Select cell A15 and type NonDeptExp.

26.Verifythatrow10–15ofcolumnAlooksimilartothefollowing:

Figure 260: Example 1

27.Select cell B10.

28.FromtheActionPane, selectEVDESandclickthe

green check button.

29.IntheFunctionArgumentsdialogbox,fortheMemberargument,pointtocell

$A10. (LeavetheAppNameargumentblank.)

30.Select OK to insert the formula in cell B10.

31.Format the text in cell B10 to bold.

184

第 203 页 / 共 377 页

BPC320 Lesson: Labs

32.CopytheformulafromcellB10throughcellB15.

33.Resize column widths as needed.

34.SelectcellC5andusetheEVDESfunctiontobringtheentitydescriptioninto

thetitlessectionofthespreadsheetbypointingtotheentitymemberincell$B2.

35.FormatthetextincellC5tobecenteredandbold.

36.CopytheformulaincellC5tocellsC6andC7toreturnthefulldescriptionof

thetimememberandthereportingcurrencymemberintothetitlessection.

37.Select cell C9 and enter the word ACTUAL. Format the text for bold and

underline.

38.Select cell D9 and enter the word BUDGET. Format the text for bold and

underline.

39.Verifythatyourscreenlookssimilartothefollowing:

Figure 261: Example 2

40.Select cell C10.

41.FromtheActionPane, selectEVGTSandclickthe

green check button.

42.EntertheFunctionArgumentsasshowbelow:

Figure 262: Function Arguments

185

第 204 页 / 共 377 页

Unit6: Labs BPC320

43.Select OK to insert the formula in cell C10.

44.FormatcellC10toshowthevalueswithacommaforathousandsseparator,

negativevaluesenclosedinredparentheses,andnodecimalplaces.

45.CopytheEVGTSformulalocatedincellC10acrossandthendown,through

cell D15.

46.

Clickonthe RefreshWorkbookiconontheBPCforExceltoolbar.

47.ChangethecurrentviewentitytoCONREGION,thecurrentviewtimeperiod

to2008.TOTALandthecurrentviewreportingcurrencytoUSD.

48.Resize column widths as needed.

49.Verifythatyourscreenlookssimilartothefollowing:

Figure 263: Example 3

50.HighlightcolumnAandselectData→GroupandOutline→Group.

51.Highlightrows1-4andselectData→GroupandOutline→Group.

52.Clickontheminus(–)signsabovecolumnBandtotheleftofRow5tohide

the report definition area.

53.FromtheeToolsmenu,selectSaveDynamicTemplates.

54.

Clickonthe Companybuttonontheleftsideofthedialogbox, and

thenselecttheeEXCEL\Reports\Managerfolder.

55.IntheFileNamefield,enterLab2–ActualvsBudgetandthenclickSave.

56.FromtheExcelFilemenu,selectClosetoclosethetemplate.

Lab 3 – Creating a Variance Calculation with EVBET

Objective: CreateavariancecalculationusingtheEVBETfunction.

186

第 205 页 / 共 377 页

BPC320 Lesson: Labs

Description: The EVBET (Better/worse) function performs a better or worse

comparisonoftwovalues,basedontheaccounttype(ACCTYPE)propertyofthe

accountmember. TheACCTYPEpropertymayhaveoneofthefollowingvalues:

INC(Income),EXP(Expense),AST(Asset),orLEQ(Liability&Equity).

ThislabisperformedusingyourassignedPETDEPOT_##applicationset.

System Data

System: CITRIXCommonTrainingenvironment

Client: Start/Programs/Accessories/RemoteDesktopConnection

User ID: CPM-##(with##=monitornumber)

Password: training

1. Adding a Report Calculation

1. FromtheActionPane,clickReporting&Analysis.

2. FromtheActionPane,clickOpenanexistingreport.

3. OpenLab2–ActualvsBudgetlocatedintheReports\Managerfolderon

the BPC server.

4. Changeyourcurrentviewsettingstomatchthefollowing:

Figure 264: Current View Settings

5. Clickontheplus(+)signstodisplaythereportdefinitionarea.

6. SelectcellE9andenterthewordVARIANCE.

7. FormatthetextinE9asboldandunderline.

8. In cell A16 type NetIncome.

9. Select cells B15:D15 and copy the formulas in those cells down through

B16:D16.

10.

Clickonthe RefreshWorkbookiconontheBPCforExceltoolbar.

11.Select cell E10.

12.FromtheActionPane, selectInsertfunction.

13.

FromtheActionPane,selectEVBETandclickthegreencheck button.

14.EntertheFunctionArgumentsasshowbelow:

187

第 206 页 / 共 377 页

Unit6: Labs BPC320

Figure 265: Function Arguments

15.Select OK to insert the formula in cell E10.

16.FormatcellE10toshowthevalueswithacommaforathousandsseparator,

negativevaluesenclosedinredparentheses,andnodecimalplaces.

17.CopytheformulalocatedincellE11downthroughcellE16.

18.Verifythatyourscreenlookssimilartothefollowing:

Figure 266: Example

Note: Change your formatting to numeric, no decimals and red

parentheses if necessary.

19.Clickontheminus(–)signsabovecolumnBandtotheleftofRow5tohide

the report definition area.

20.FromtheeToolsmenu,selectSaveDynamicTemplates.

21.

Clickonthe Companybuttonontheleftsideofthedialogbox,then

selecttheeEXCEL→Reports→Managerfolder.

22.IntheFileNamefield,enterLab3–VarianceandthenclickSave.

23.FromtheExcelFilemenu,selectClosetoclosethetemplate.

188

第 207 页 / 共 377 页

BPC320 Lesson: Labs

Figure 267: Current View Settings

Lab 4 – Creating a Trend Report with EVTIM

Objective: CreateatrendreportusingtheEVTIMfunction.

Description: TheEVTIM(TimePeriodOffset)functionspecifiesanoffsetfroma

given time period.

ThislabisperformedusingyourassignedPETDEPOT_##applicationset.

System Data

System: CITRIXCommonTrainingenvironment

Client: Start/Programs/Accessories/RemoteDesktopConnection

User ID: CPM-##(with##=monitornumber)

Password: training

1. Creating the Trend Template

1. FromtheActionPane,clickReporting&Analysis.

2. FromtheActionPane,clickOpenanexistingreport.

3. OpenLab2–ActualvsBudgetlocatedintheReports\Managerfolderon

the BPC server.

4. Clickontheplus(+)signstodisplaythereportdefinitionarea.

5. SelectcellA3andchangetheEVDIMformulato=EVDIM($B$1;“C”)

6. SelectcellsD9:D15andselectEdit→Clear→Contents.

7. Select cell C9 and enter the following:

=EVCVW($B$1;“TIME”)

8. Changethecurrentviewtimeperiodto2008.JAN.

9. Select cell D9.

10.FromtheActionPane, selectInsertfunction.

11.

FromtheActionPane,selectEVTIMandclickthegreencheck button.

12.EntertheFunctionArgumentsasshowbelow:

189

第 208 页 / 共 377 页

Unit6: Labs BPC320

Figure 268: Function Arguments

13.Select OK to insert the formula in cell D9.

14.CopytheformulaincellD9acrossthroughcellN9.

15.CopyanyEVGTSformulaslocatedincolumnCtothenewdatacellsinthe

remaining columns and rows.

16.

Clickonthe RefreshWorkbookiconontheBPCforExceltoolbar.

17.Resize columns as needed.

18.Verifythatyourscreenlookssimilartothefollowing: (Yourvaluesmayvary)

Figure 269: Example 1

19.Changethecurrentviewtimeperiodto2008.MAYandnoticethechangeto

the columns.

20.Changethecurrentviewtimeperiodbackto2008.JAN.

21.ChangethecurrentviewmeasurestoYTDandnoticethechangetothedata.

22.ChangethecurrentviewmeasuresbacktoPeriodic.

23.Clickontheminus(–)signsabovecolumnBandtotheleftofRow5tohide

the report definition area.

24.FromtheeToolsmenu,selectSaveDynamicTemplates.

190

第 209 页 / 共 377 页

BPC320 Lesson: Labs

25.

Clickonthe Companybuttonontheleftsideofthedialogbox,then

selecttheeEXCEL→Reports→Managerfolder.

26.IntheFileNamefield,enterLab4–MonthlyTrendandclickSave.

27.FromtheExcelFilemenu,selectClosetoclosethetemplate.

Lab 5 – Retrieving Data from Multiple Applications

with EVGTS

Objective: Createareportthatwillretrievedatafromtwoapplications.

Description: Youcancreatereportsthatretrievedatafrommultipleapplications

by utilizing the AppName argument of the EVGTS function accordingly. The

applicationsmustresidewithinthesameapplicationset.

ThislabisperformedusingyourassignedPETDEPOT_##applicationset.

System Data

System: CITRIXCommonTrainingenvironment

Client: Start/Programs/Accessories/RemoteDesktopConnection

User ID: CPM-##(with##=monitornumber)

Password: training

1. CreatingtheMultipleApplicationTemplate

1. UsingtheExcelFilemenu,opentheMultiApplications.xlsworkbooklocated

inMyComputer→Database→CPM310&CPM320_Training→CPM320

→ CPM320-## folder.

2. SelectcellA1andchangethetexttoApplication1.

3. Select cell C1 and type Application2.

4. Select cell C2 and type RATE.

Note: InsurethereisnospaceaftertheapplicationnameincellC2.

191

第 210 页 / 共 377 页

Unit6: Labs BPC320

5. Select cell C16.

6. FromtheActionPane, selectInsertfunction.

7.

FromtheActionPane,selectEVGTSandclickthegreencheck button.

8. EntertheFunctionArgumentsasshowbelow:

Figure 270: Function Arguments 1

Note: Member7onlyhasthecolumnlockedandMember8onlyhasthe

row locked.

9. Select OK to insert the formula in cell C16.

10.CopytheformulaincellC16acrossandthendown,throughcellE17.

11.

Clickonthe RefreshWorkbookiconontheBPCforExceltoolbar.

12.Select cell C23.

13.

FromtheActionPane,selectEVGTSandclickthegreencheck button.

14.EntertheFunctionArgumentsasshowbelow:

Figure 271: Function Arguments 2

192

第 211 页 / 共 377 页

BPC320 Lesson: Labs

15.Select OK to insert the formula in cell C23.

16.CopytheformulaincellC23acrossandthendown,throughcellE24.

17.

Clickonthe RefreshWorkbookiconontheBPCforExceltoolbar.

18.Verifythatyourscreenlookssimilartothefollowing: (yourvaluesmayvary)

Figure 272: Example 1

19.HighlightcolumnAandselectData→GroupandOutline→Group.

20.Highlightrows1–9andselectData→GroupandOutline→Group.

21.Click on the minus (–) signs above Column B and to the left of Row 10 to

hide the report definition area.

22.FromtheeToolsmenu,selectSaveDynamicTemplates.

23.

Clickonthe Companybuttonontheleftsideofthedialogbox,then

select the eEXCEL\Reports\Manager folder.

24.IntheFileNamefield,enterLab5–MultiApplicationsandclickSave.

25.FromtheExcelFilemenu,selectClosetoclosethetemplate.

Lab 6 – Adding Conditional Formatting to an EVGTS

Report

Objective: Incorporate Excel Conditional Formatting into an existing report

template.

Description: Excel’sConditionalFormattingfeaturecanbeusedtoautomatically

format certain cells in your reports and input schedules. This feature can be

usedtoapplyfonts/styles,borders,andcellshadingtocellsthatmeetaspecified

193

第 212 页 / 共 377 页

Unit6: Labs BPC320

criteria. Youcancomparethevaluesofselectedcellstoaconstantortotheresults

ofaformula. Itisimportanttoutilizeconditionalformattinginordertomakeyour

reportsandinputschedulesasuser–friendlyaspossible.

ThislabisperformedusingyourassignedPETDEPOT_##applicationset.

System Data

System: CITRIXCommonTrainingenvironment

Client: Start/Programs/Accessories/RemoteDesktopConnection

User ID: CPM-##(with##=monitornumber)

Password: training

1. UpdatingtheAccountsintheRowsoftheReport

1. Changethecurrentviewtimeperiodto2008.TOTAL.

2. FromtheActionPane,clickReporting&Analysis.

3. FromtheActionPane,clickOpenanexistingreport.

4. Open Lab 3 – Variance located in the Reports → Manager folder on the

BPC server.

5. Clickontheplus(+)signstodisplaythereportdefinitionarea.

6. Select row 11 and insert two rows.

7. Select cell A11 and type 3rdParty.

8. Select cell A12 and type RawMaterials.

9. Select cells B10:E10 and copy the formulas in those cells down through

B12:E12.

10.Select row 17 and insert two rows.

11.Select cell A17 and type Salaries.Cat1.

12.Select cell A18 and type Salaries.Cat2.

13.Select cells B16:E16 and copy the formulas in those cells down through

B18:E18.

14.

Clickonthe RefreshWorkbookiconontheBPCforExceltoolbar.

15.Verifythatyourscreenlookssimilartothefollowing: (yourvaluesmayvary)

194

第 213 页 / 共 377 页

BPC320 Lesson: Labs

Figure 273: Example 1

2. Adding the Conditional Formatting

1. HighlightcolumnAandselectData→GroupandOutline→Ungroup.

2. WithcolumnAstillselected,selectrightmouse→Insert.

3. SelectcellA9andtypeCalc. Boldandunderlinethetextinthiscell.

4. Select cell A10.

5. FromtheActionPane, selectInsertfunction.

6.

FromtheActionPane,selectEVPROandclickthegreencheck button.

7. EntertheFunctionArgumentsasshowbelow:

Figure 274: Function Arguments

8. Select OK to insert the formula in cell A10.

9. CopytheformulaincellA10downthroughcellA20.

10.Select cell D10.

11.FromtheExcelFormatmenu,selectConditionalFormatting.

12.EntertheConditionalFormattingasshowbelow:

195