Jennifer Dalzell

PowerOlap User Guide 7/14/2023 1 of 16 Table of Contents ACCESSING THE REPORTS ........................................................................................................................................... 3 OLAP BOARD BOOK - .................................................................................................................................................. 6 OLAP BOARD BOOK - (CONT.) ...................................................................................................................................... 7 MAKE ADJUSTMENTS ................................................................................................................................................... 8 GL MAIN ACCOUNT REPORT ........................................................................................................................................ 9 GL MAIN ACCOUNT REPORT (CONT.) .......................................................................................................................... 10 GL MAIN ACCOUNT REPORT (CONT.) .......................................................................................................................... 11 MODIFY REPORTS ..................................................................................................................................................... 12 MODIFY REPORTS (CONT.) ......................................................................................................................................... 13 7/14/2023 2 of 16 Accessing the Reports 1. Open Microsoft Excel 2. From the menu, choose File/Open. 3. Browse to the \\dynshare\OLAP Financial Reports\IPM Reports location on the server. 4. Select the file you want to run:  board_book_summary  trend_report_  Olap_Board_Book_  adjustmt_sheet_new  gl_main_adjustments  change_request_forms 5. Click Open. 7/14/2023 3 of 16 Notes Board Book Summary The board_book_summary.xls workbook displays a summary of the divisional activity, per period, per year. 1. 2. 3. 4. From the menu, choose File/Open. Browse to the \\OLAP Financial Reports\IPM Reports location on the server. Select board_book_summary.xls. Click Open. Drop down list/settings 5. From the drop down lists at the top of the worksheet, select the Month and Year you would like to view. 6. The third drop down list shows the adjustments made, original amount and Total of the original Amount and any adjustments. You can select each item separately. 7. After making your selections, hit the F9 key to refresh. 8. Select File/Print to print the report. 7/14/2023 4 of 16 Notes Trend Report The trend_report_.xls workbook displays a summary of the divisional activity for the entire year. This report shows each of the allocated cost reports under separate worksheet tabs. These reports offer grouping to minimize each of the quarters. Click the + sign to expand, the – sign to collapse. 1. From the menu, choose File/Open. 2. Browse to the \\dynshare\OLAP Financial Reports\IPM Reports location on the server. 3. Select trend_report_.xls. 4. Click Open. Select a different panel tab to see the yearly allocated report detail 5. 6. 7. 8. 7/14/2023 After opening this report, hit the F9 key to refresh the data. Select File/Print to print the report. Select another tab at the bottom to see a different allocated report. Hit the F9 key on each page to refresh the data. 5 of 16 Notes Olap Board Book The Olap_Board_Book_.xls workbook displays all of the reports from the Board Book Financial Reports. Each report is located under a separate worksheet tab. 1. From the menu, choose File/Open. 2. Browse to the \\dynshare\OLAP Financial Reports\IPM Reports location on the server. 3. Select Olap_Board_Book_.xls. 4. Click Open. Each page of the board book is numbered on a separate sheet of the workbook The TOC worksheet tab is an interactive Table of Contents. Click on the report you want to view and the page will open automatically. It makes the navigation of the reports easier. The TOC worksheet tab is an interactive Table of Contents. Click on the report you want to view and the page will open automatically. It makes report navigation easier. 7/14/2023 6 of 16 Notes Olap Board Book (cont.) Notes 5. Use the arrow key at the bottom of the window to navigate to the last worksheet. 6. Select the Olap Info Sheet to select the date and year to run the reports. Olap Info Sheet tab 7. From the drop down lists at the top, select a month and a year. Select the time period and the year from the drop down lists 8. Hit the F9 key to refresh the entire workbook based on the settings you have selected. 9. From the File menu, select print. Select Entire workbook to print the whole book 10. Click OK. 7/14/2023 7 of 16 Make Adjustments 1. From the menu, choose File/Open. 2. Browse to the \\dynshare\OLAP Financial Reports\IPM Reports location on the server. 3. Select adjustmt_sheet_new.xls. 4. Click Open. Select month, year and Adjustment from the drop down lists 5. From the drop down lists at the top of the sheet, select the month, year and be sure to select “Adjustments”. 6. Enter the values in the summarized accounts for each of the divisions. 7. Hit Enter after each entry. 8. Hit the F9 key to send the adjustments back to Olap. Because of the formulas in this worksheet, if an adjustment needs to be reset, do not hit the delete key. Replace the value with a 0. 7/14/2023 8 of 16 Notes GL Main Account Report A report has been created that displays which GL Main accounts make up each of the summarized account in the IPM database. 1. From the menu, choose File/Open. 2. Browse to the \\dynshare\OLAP Financial Reports\IPM Reports location on the server. 3. Select gl_main_adjustments.xls. 4. Click Open. 5. Select the following information from the drop down lists: a. Summarized account from IPM/Board Book b. Month c. Year d. Company e. Department 7/14/2023 9 of 16 Notes GL Main Account Report (cont.) All of the accounts from the hiearchy is available in this list. 6. From the list of the Summarized accounts, make a selection: 7. Hit the F9 key to refresh the report. All of the accounts will display, even the ones without data. This could make for a large report with a lot of empty pages. In order to minimize the size of this report, a filter has been added to hide all rows that have no data. 8. From the drop down filter, select 0 to hide all accounts without data. Filter the accounts without any data Select the Show all option in the filter to reset the list before making changes to the account list. 7/14/2023 10 of 16 Notes GL Main Account Report (cont.) The result report without any zero rows displays: 7/14/2023 11 of 16 Notes Modify Reports If you need to make an adjustment to an account that isn’t listed, you can make changes directly to any of the worksheets. 1. Insert a blank row in the Excel worksheet. 2. For the accounts, select the inserted row in Column B. 3. From the PowerOlap menu, select Select -> Members… You can also use the icon on the PowerOlap toolbar. 4. In the member selection box, choose Account from the Dimension drop down list. 5. The accounts will display in the left pane of the dialogue box. Click to highlight the accounts to be added to the worksheet. 6. Click the 7/14/2023 icon to copy the accounts to the list on the right pane. 12 of 16 Notes Modify Reports (cont.) 7. For the Fill drop down list, select Down to populate the rows with the accounts selected. 8. Click OK. 9. Copy the formulas in column B through column J to the new account rows. 10. Hit the F9 key to refresh the data. 7/14/2023 13 of 16 Notes Change Request Form Notes When there is a need to request a new account, the Change Request Form must be filled out and submitted. 2 4 1 3 5 6 7/14/2023 7 1 Requested By: Name of the requestor. 2 Assigned to: Person who will apply the changes. 3 Requested Date: Date of the original request. 4 Approved by: Approval by Gail or Tom Beach. 5 Email Address Requestor’s email address. 6 Reason for Request Description of why the request has been made. 7 System Which system (FRX, Great Plains, or Olap) does this account need to be created. 14 of 16 Change Request Form (cont.) Notes When there is a need to request a new account, the Change Request Form must be filled out and submitted. a c b d e 8 f-j - 14 15 16 7/14/2023 8 New Account Check this box if a new account needs to be created. a. New account number to be assigned. b. Which Division the account number is to be assigned. c. Which report group should the account number be assigned. d. Is the account a P&L account or Balance Sheet account? e. If the account should mimic an existing account’s settings, specify the originating account. f -j. CMO levels 1-5 that are assigned to the account. 9 New Division Check this box if a new division needs to be created. Specify a name and a description if a new division needs to be created. 10 New Company Check this box if a new company needs to be created. Specify a name and a description for the new company. 15 of 16 Change Request Form (cont.) 7/14/2023 11 New Department Check this box if a new department needs to be created. Specify a name and a description for the new department. 12 New Report Check this box if a new report needs to be created. Specify a name and the details of the report which needs to be created. 13 Modify Report Specify the name of the report and the details of that report to be modified. 14 Completed by: The person who has made the changes on the system side. 15 Reviewed By: The requestor who has reviewed the changes made. 16 Accepted By: The requestor has agreed the changes are correct. 16 of 16 Notes