Grant Account Analysis Report
Pivot Macro Instructions
Purpose: to outline instructions for the pivot macro available as a supplement to the Account Analysis Report (AAR). Instructions to generate and download the AAR are linked here.
Helpful tips for using a macro:
Once you have generated your report, save the pivot macro as something other than the original name to ensure that you do not experience issues using the macro in the future. It is recommended that you save the original download to your desktop or a folder that you access regularly and that you save the file with a different name when saving the report to keep the information that you need (e.g. save the file as AccountAnalysisPivot - OCI FY23).
If you want to download the macro to use on a regular basis (not recommended), DO NOT rename it.
You are able to upload new information into the macro if you have not renamed it. For example, if you want to generate a new pivot with updated AARs, as long as you have not changed the name, you can go to the 'Process' tab and select the button to import the new information.
Step 1: Save Account Analysis Report & Open Pivot
Begin by saving the AAR(s) you downloaded.
Download or locate the pivot table you will be working with. You can find links to available pivot files on the Job Aids & Resources page of the Bronco Hub Training Portal.
Open the pivot and confirm accuracy of the file name. Important Note: If you have a previously downloaded version of this pivot stored on your computer, the file will download with a 1 or another number at the end and this is incorrect. For example, rather than GrantAccountAnalysisPivot the file may download with the name GrantAccountAnalysisPivot(1). The pivot will not work if the file name is incorrect. To move forward, you must ensure the file has the correct naming convention.
Correct pivot file names for financial analysis:
AccountAnalysisPivot
BudgetOfficePivot
DetailTransactionPivot
GrantAccountAnalysisPivot
Step 2: Add Budget Rollup Line(s)
Select the GrantBudgetData Tab to enter or edit grant budget(s).
Select Insert to add a line for each budget rollup line.
Enter Fund, Department, Cost Center, Supplemental, and Project Segments.
Enter Natural Account as the Budget Rollup
Salaries – 410000
Fringe – 420000
Other Expense – 500000
Travel – 530000
F&A – 529500 or 895000
Student Costs – 891300 through 893200
Revenue – Enter Total Budget 334100, 335100, 338100, or 336100
Enter Net Amount as Negative Number for Expenses and Positive Number for Revenue
Step 3: Select the Number of Reports to Process
Navigate to the Process Tab of the pivot spreadsheet.
Select Generate Pivot Account Analysis Report-1 to analyze one AAR or select Generate Pivot Account Analysis Report-2 to analyze two AARs.
Navigate to the AARs saved in a prior step.
The report will take a few minutes to run.
Step 4: Review Results
Select the Report_Criteria_Information tab to view the accounting periods included in the report and the date the AAR was generated.
Select the CombinedData tab to view all data generated in the AAR(s). DO NOT change the data on this tab; this data is used in the pivot table.
Select the PvtTblRpt tab to view budget and actual expenses.
You can use the filters to isolate key pieces of information like funding sources.
You can filter by accounting Period_Name, Balance_Type, Fund_Department_Supplemental_Project. To sort by a particular field, select the chevron to the right of the filter and select the values to include or exclude in your search.
Double click on dollar amounts on the PvtTblRpt tab to drill to transaction details in a new tab.
Copy and paste the pivot table to additional tabs and use filters to set up specific funding sources. Update tab names to reflect filter selections.
Refresh data as often as you would like by completing step 2 with new reports
Need help with the steps above?
For support with running this report (e.g. you received an error message) submit a Help Desk Self-Service ticket.
Visit Reporting at Boise State for more information about reporting support at the university.