Using the Report Wizard

• Report Template: There are four financial report templates available for selection with each reporting pack. The Standard Templates are Balance Sheet By Type, Balance Sheet By Account Class, Trial Balance, and Profit and Loss Statement. The Chinese Templates are Income Statement, Balance Sheet, Statement of Cash Flow, and Statement of Changes in Equity Report. The Vietnamese Templates are Trial Balance, Income Statement, Cash Flow Statement, and Balance Sheet. Select the template that you want to use. These templates, except Trial Balance, are designed with empty group definitions to get you started with your own custom data using the Add-In. The Trial Balance template doesn’t use any group definition and instead shows all chart of account codes.
• Site: Select a site. This default site name comes from the site selected on the Excel toolbar. The Year field is updated when the site changes..
• Period: Select the period that will be used in the formula.
• Year: Select the fiscal year that will be used in the formula.
• Currency: This field is optional. If you select a currency, the G/L balances are converted to the selected currency.
• FSB: This field is optional. It provides a list of FSB names that is populated each time a different site is selected. The primary GL is used when no FSB is selected.
• Use Dynamic Parameters: Select this check box to make the report more interactive by using the selectable parameters lists. Dropdowns for Period, Fiscal Year, and Site are added to the top of the spreadsheet.
• Income Summary Account: Select the income summary account that will be use in the formula.
• Include Zero Balance: Select this check box if you want to include zero-balance accounts. If you create the report without this option selected, accounts that are zero this month but have values next month do not appear in the report. We recommend that you use this option if you create your own Custom template or report. This check box does not display if using the Chinese Reporting Pack and Income Statement is selected as the report template.

• Balance Sheet by Type: This report, grouped by the Type column from the Chart of Accounts, has Account Code and the SLDESC function to retrieve the description, and either the SLGLBAL or the SLNGLBAL function to retrieve the balance amount. The Site, Period, and Year have a selectable drop-down list, so you can easily change the data that displays in the report. The difference between revenue and expenditure is added to the bottom of the Balance Sheet with a title of Estimated P/L. The argument value of the GL function uses absolute cell reference for Site, Period, and Year, and relative cell references for account code, when the Use Dynamic Parameters is checked in the Report Wizard. The estimated P/L is calculated using the End of Period rate.
• Balance Sheet by Account Class: This report is the same as Balance Sheet by Type except that the grouping is performed based on Account Class.
• Trial Balance: This report is set up with the use of an extra drop down list field in the Report Wizard. When you select Trial Balance as the Report Template, the Income Summary Account field is displayed.

  This report is grouped by Account Class. It includes account codes with type Assets, Liabilities, Owners' Equity, Revenue and Expense. The closing amount, which may be already part of Retained Earnings, may cause a non-balanced debit and credit totals. To avoid this, the closing balance must be subtracted from the Retained Earnings account. Since the SL doesn’t keep the Income Summary account while performing the Year-end closing activities, the Add-in requires you to select the same prior to generating the Trial Balance. The Income Summary Account field only displays account codes with the type ‘Owners Equity’.

The closing summary amount is calculated as: SLPL(12, 2012, "OH", "Y") - SLPL(12, 2012, "OH", "N")

• Profit and Loss Statement: This report is set up with the use of an extra check box on the Report Wizard. When you select Profit and Loss Statement as the Report Template, the Use Expense-to-Revenue check box is displayed. If you select the Use Expense-to-Revenue check box, the period total and YTD total percentage is calculated by dividing an individual expense by total revenue. If this check box is cleared, the percentage is calculated by dividing an individual expense amount by total expense.

This report includes items from revenue and expenditure type accounts. You must assign the appropriate Account Class to your chart of accounts code to generate this statement. The report is formatted in this order:

• Revenue and Group Total
• Cost of Sales and Group Total
• Gross Profit. Calculated : Revenue - COS
• Operating Expense and Group Total
• Profit from Operations. Calculated: GP - Operation Total
• Interest Income Expense and Group Total
• Non-Operating Income Expense and Group Total
• Depreciation and Amortization Expense and Group Total
• Profit before Tax Expense. Calculated: GP - Total Expense (without Tax)
• Tax Expense and Group Total
• Net Profit Calculated: GP - Total Expense

Vietnamese Reporting Pack: There are four report templates if you use the Vietnamese reporting pack: Trial Balance, Income Statement, Cash Flow Statement, and Balance Sheet. The templates are formatted using the GL Group functions. Users must map their Chart of Account codes to each group to get the balance amount for each group. Once the report is rendered on a worksheet, right-click on each Reporting Group and assign account codes from the ‘Add To Reporting Group’ form. This is a one-time setup for each template, and you can modify the grouping at any time.

Trial Balance: This template is formatted as per the Model No. S06-DN and grouped by the account class. It is designed to show opening balance, current period balance, and closing balance.

Income Statement: This template is formatted as per the Model No. B02-DN. It is designed to show net income/loss for the given period and fiscal year and also show the balance for the same period of the previous fiscal year.

Cash Flow Statement: This template is formatted as per the Model No. B03-DN. It is designed to show cash flow for the given period and fiscal year and also show the balance for the same period of the previous fiscal year.

Balance Sheet: This template is formatted as per the Model No. B01-DN. It is designed to show the balance for the given period and fiscal year and also show the closing balance for the previous fiscal year.

Chinese Reporting Pack: There are four report templates if you use the Chinese reporting pack: Income Statement, Balance Sheet, Statement of Cash Flow, and Statement of Changes in Equity Report. The main content of the Chinese reports consists of the Reporting Group, Sequence Number, Amount columns that show the current month as well as the cumulative amount.

Income Statement (CRP only): This is an income statement report. It includes the Company Name, Site Name, Day, Month, Year of the statement, and the name of the person who prepared this statement. Initially, this report is generated with no balance amount. That's because the Reporting Group definition has no account code associated with it. Once the report is rendered on a worksheet, you can right click on each Reporting Group and assign account codes from the Add To Reporting Group form. This is a one-time setup, and you can modify the grouping when needed.

Balance Sheet - CRP (CRP only): This report is grouped by the Type and Account Class column from the Chart of Accounts. It is formatted using the Excel functions, SLDESC to retrieve description and SLGLBAL function to retrieve the balance amount. The Site, Period and Year are choices in the drop-down list, so the report can be viewed for different periods.

Statement of Cash Flow: The Statement of Cash Flow shows the actual cash collected and paid by using inflow and outflow.

The Statement of Cash Flow has these three sections:

• Operating Activities
• Investing Activities
• Financing Activities

The columns headers in the report are: Item, Line No, Subtotal, and Cumulative. The report template is formatted using the SL GROUPING functions such as SLGRPDESC, SLGRPGLBAL, SLGRPGLYTDBAL and SLNGRPGLYTDBAL. Initially, the report is rendered without any balance; that is because there is no COA associated with any group definitions. Once the template is generated, you can add COA to each group. You can modify the group definition to suit your requirement.

Most of the inflow and outflow balances are derived after adding or subtracting certain Balance Sheet items. In this case, an increase or decrease in current year balance is calculated by comparing with the previous year balance. The columns that retrieve previous year and current year balance to calculate the difference are hidden.

This table shows the addition or subtraction of differences to Inflow and Outflow total:

The Add-In finds the previous fiscal-year by using the current fiscal year definitions. The end-period of the previous fiscal-year is retrieved from the period table.

Financing and Investing sections have total inflow and outflow which is added to the total Inflow and Outflow at the end.