Reports Main Menu

by rwalliker » Wed Jan 17, 2018 11:38 am EXCEL REPORTS You can create custom spreadsheets for Reporting or Forecasting. These can be opened within the program where you can make use of an additional menu bar for manipulating and saving the layout or for refreshing reports or exporting data back into the database. These are ordinary excel spreadsheets that are stored in the cloud. The instructions on a sheet are contained in the first 7 columns and the first and second rows (highlighted yellow in the new template file) of a named range beginning with 'NL' or '_NL'. These instructions tell 4CastPro what data to bring back or write back to the database Adding a new report Excel Reports can be created offline and uploaded to the model by first clicking the “Choose File” button and then clicking the “Upload” button There is also an 'Add Report' button. You should enter a name for the report and choose from the dropdown a template if you wish. If the “No” dropdown is selected a general template file is added to the list of reports which can be used to build your report. Against each report there are 4 options • RefreshnSave – Refreshes report from database and saves data in one operation • Download – Downloads file to desktop • Delete • Open • MakeFav – Adds report to main menu To copy a report – simply download the report you want to copy and then upload with a different name Menu on opening a Report: Refresh – Refreshes data from database SavetoDbase – Saves data to database for a Writeback Report Show Instructions – Shows Columns A-F and Rows 1 and 2. You are then provided with the following facilities that allow you to amend a report or build it from template: Custom Excel Reporting (Refresh Report via “Refresh” Button on Menubar) Custom spreadsheets import from the database to a named range eg "NL" within the spreadsheet. The following columns (fields) are required within the "NL" named range: Left Column "Import" - Imports where set to "I" or "INEG" for reversed sign You need ACCT, ACCTGP0, TYPE and CC columns(Columns 2-6 of your named range) Data is brought back from the database by 1)Specifying the CC and either ACCT, ACCTGP0 or TYPE in columns 2-6 2)Adding the following Time Series Month or Special Selection fields on Row one of your named range: CYFCT_PER LYACT_PER CYBUD_PER CYFCT_1 to 12 CYFCT_YTD LYACT_YTD CYBUD_YTD LYACT_1 to 12 CYFCT_TOT LYACT_TOT CYBUD_TOT CYBUD_1 to 12 These include Special Selection Summary fields as well as the ordinary database fields eg current period year to date (CYFCT_YTD) 3)Optionally you can specify a forecast version on Row two of your named range eg V1,V2 beneath the Times Series eg V1 will bring back data from Version1 of the forecast 4)Finally hitting 'Refresh' on the menubar to bring latest data back from database The named range needs to include all I columns and all selected data fields You can include rows that are not I rows and columns that are not data columns for Formatting headings etc The yellow report definition section provides the spreadsheet with the details of where to obtain the data, whether to show full units or only 1000’s etc This yellow section can be hidden or locked to provide users with a cleaner view of the report and to stop them amending it if that is what is required. Custom Excel Forecasting (Writeback via “SavetoDbase” Button on Menubar) The custom excel spreadsheets can also writeback to the database as well as import data so can be used to perform excel based forecasting. Writeback reports allow you to setup spreadsheets at Account level that write back to the database by populating the Export column of the spreadsheet (with E or ENEG) and hitting 'SavetoDbase ' on the menubar when ready to send data back to the database eg SalesForecast or OverheadForecast templates. You can only export at ACCT level for an actual Company(not a node) as the system needs to know where to post data back into the database In the current year balances are only written back to future months preventing you overwriting imported balances. In the case of Statistical data this can be overridden by Putting 'STA' in the TYPE column You save data to the database from a named range "NL" within the spreadsheet by hitting 'Exportxls' on the add-in. The benefit is that you can take existing forecasting spreadsheets and integrate then into your 4Cast model in a controlled manner: -You can protect the spreadsheet leaving only data entry cells available for input. -You can hide the top row and first 7 columns that control which lines import or export to the database. Meeting more complicated Scenarios 1)Multiple Ranges You can set-up up multiple NL ranges NL, NL1, NL2,_NL,_NL1 etc in one spreadsheet to allow you to say setup a custom board pack spreadsheet. You can simply add a new named range e.g. NL6 for a new page or range in the workbook. The named ranges only have to start with 'NL' or '_NL' so you can give them meaningful names such as 'NL-MainP&L' 2)Import/Export Column Instructions As well as the standard I,E,INEG and ENEG there are other options Import Instructions: I/INEG-For importing (+/-) balances IINV-For importing the Invoiced figures on accounts set up on an accruals basis IADD/IDIS-For importing Fixed Asset Additions and Disposals Import Instructions: E/ENEG-For exporting (+/-) balances. EINV-For exporting the Invoiced figures on accounts set up on an accruals basis EADD/EDIS-For exporting Fixed Asset Additions and Disposals TYPES OF REPORT FIXED FORMAT This is the standard custom report selecting lines on basis of CC and either ACCT,ACCTGP0 or TYPE You can build a report on an alternative ACCTGP by changing ACCTGP0 to ACCTGP1,ACCTGP2 etc DOUBLE CRITERIA If building a report with rows selected by ACCTGP then you can use a double Criteria ie Where ACCTGP0="Main Sales" and ACCTGP1="001FCT" This is done by renaming the TYPE column ACCTGP1 CROSSTAB REPORT This is capable of producing for example a by location report If for example ACCTGP1 contained locations then you would populate cell E2 with ACCTGP1 and use Row 2 to define the required location User Access to Custom Reports Where a user other than a User in the Admin role adds a report the report is Prefixed with the Username and is only visible to that user. Dashboard The Dashboard feature is a selectable option under Set-up Options/Other It is basically a custom excel spreadsheet named Dashboard.xlsx You can use all the same custom reporting techniques to bring through data from the database Any excel graphs need to be at the top of the spreadsheet. They will then appear on the dashboard

by rwalliker » Wed Jan 17, 2018 1:31 pm Cashflow The cashflow report is calculated from movements on the statutory PL and Balance Sheet together with other key information The whole report is created from a template xls file 4CastPLStat.xls which resides on the server which can be amended by us if required. The “Download xls” button allows you to download the cashflow report to your desktop. You can unhide hidden rows and columns to see the full calculations behind the cashflow report The cashflow report is derived mathematically from the actual/forecast balance sheets The balance sheet can forecast itself semi automatically by: 1)Setting debtor days and creditor days or profiles to calculate trade debtors and trade creditors 2)Setting VAT %'s 3)Setting up fixed asset and depreciation account relationships 4)Setting up loan accounts 5)Setting up stock methods for forecasting stock balances 6)Setting up methods to calculate accruals/prepayments and provisions etc (See Methods section of Help under Forecasting for a full look at Methods and their cashflow implications) 7)Setting up factoring/invoice discounting 8)Setting up and forecasting corporation tax and dividend provisions and payments 9)Setting other accounts as REPEAT method eg Share Capital (unless changes are anticipated) 10)Selecting any other set-up options like 'Depreciate in month of purchase' or 'Apply 60 day VAT creditor' Any changes to forecast Sales/Costs should then reflect through onto the balance sheet and cashflow by hiting the 'Recalc' button

by rwalliker » Wed Jan 17, 2018 12:54 pm PACKS 4CastPro is designed for both Monthly Management Account Reporting against Budget/Forecast and for producing the financials for a 5 year plan There is a canned Management Account Pack (for simple Board Requirements) and a canned 5 Year Plan Pack (Perfect for when the Bank need a Plan) The “Savexls button” allows you to save the reports to your desktop Management Accounts Pack Button This creates a spreadsheet pack of Management Accounts/Plan reports for a Single Company or a Consolidated Group (Depending on which Company Node you currently have selected) with the following tabs • Statutory P&L Account SchA • Statutory Balance Sheet SchB • Fundsflow SchC • Statutory Balance Sheet by Period SchD • Management Format P&L Account SchE • Management Format P&L Account by Period SchF This saves considerable time over setting up print macros etc when using a spreadsheet model. A custom board pack could be set up using the “Excel Report” main menu item Plan Pack Button This creates a spreadsheet with tabs for the selected number of years forward It gives the plan by month with following sections • Statutory P&L Account • Statutory Balance Sheet • Funds Flow Statement • Cashflow Statement • Detailed Management Account P&L