Calculate processing time for AP invoices



Feature Brief: Calculate processing time for AP invoicesYou can calculate the processing time for a batch of invoices from the date they were received until the date they were paid. The result can help you determine department efficiency for up to a year. You can use either Microsoft? Excel? or SAP Crystal Reports? to calculate processing time.In formulas to calculate processing time, you use the new Received Date field on the Invoice History form in Eden Accounts Payable. With the Eden feature to export invoice history to Microsoft Excel, you can use Excel to calculate the difference between the received date and the check/wire date (the invoice processing time), and then calculate the average. With SAP Crystal Reports, you create a custom report that includes a formula to calculate the number of days between the received date and check date, excluding Saturdays and Sundays. Previously, the Received Date field was unavailable and you had to type this value into Excel to calculate processing time. Note The four-day work week calculation is only available with Excel 2010. OverviewFeature release: Eden v5.10Areas impacted:Invoice History form, Main tab, Received Date field Invoices form, Main tab, Received Date field Invoice Quick Entry form, Main tab, Received Date fieldImport Invoices formRequired? NoConditionsReceived date for invoices has been entered and savedNew Functionality TOC \h \z \u \t "Heading 3,1,Heading 4,2,Heading 5,3" Tracking the date an invoice is received PAGEREF _Toc368406385 \h 2Calculate processing time for a batch of AP invoices – Using a custom SAP Crystal Report PAGEREF _Toc368406386 \h 2Calculate Processing time for a batch of AP invoices - Using Microsoft Excel PAGEREF _Toc368406387 \h 3Export to Excel PAGEREF _Toc368406388 \h 3Add holidays to Excel PAGEREF _Toc368406389 \h 4Five-Day Workweek - Calculate the Efficiency PAGEREF _Toc368406390 \h 5Calculate the processing time. PAGEREF _Toc368406391 \h 5Calculate the average PAGEREF _Toc368406392 \h 6Four-Day Workweek (or any work week) - Calculate the Efficiency PAGEREF _Toc368406393 \h 6Calculate the processing time. PAGEREF _Toc368406394 \h 6Calculate the average PAGEREF _Toc368406395 \h 7Tracking the date an invoice is receivedRecord the date a department receives an invoice using the new Received Date field on the following forms:Invoices form, Main tabInvoice Quick Entry form, Main tabImport Invoices form - Available for both dynamic and permanent file layout, but not required for importInvoice History form, Main tab - A received date can still be entered for a post-paid invoiceThis field is informational only. You can use it on Find (QBE) forms to select invoices and on Browse forms for reporting.You could use the Received Date field as a benchmark to determine the efficiency with which a department processes invoices. You can either export invoice history to Excel and perform the calculations, or you can write a custom SAP Crystal Report and use the formula.Calculate processing time for a batch of AP invoices – Using a custom SAP Crystal ReportAn alternative to exporting to Excel and performing the calculations is to create a custom SAP Crystal Report and use the following formula. This Crystal formula calculates the number of days between the received date and check date, excluding Saturdays and Sundays:Local DateTimeVar d1 := {ESAINHSH.RECEIVED_DATE};Local DateTimeVar d2 := {ESAINHSH.CHK_TRNFR_DATE};DateDiff ("d", d1, d2) - ???? DateDiff ("ww", d1, d2, crSaturday) -???? DateDiff ("ww", d1, d2, crSunday)Calculate Processing time for a batch of AP invoices - Using Microsoft ExcelExport invoice data to Excel and add formulas to calculate the time spent on each invoice and the average processing time.Export to ExcelOn the Invoice History form, choose Record menu ??Find (QBE) to select the desired invoices for a year. Open the Browse form by clicking the Browse button and prepare the file for export:Figure SEQ Figure \* ARABIC 1: Invoice History form, Main tab, Browse button. To change the column order, drag and drop the columns. The Date Received and Check Transfer Date fields should be displayed. If you don't see them, click the Show / hide button and check each field.For any fields that you do not want to export, click the Show / hide button and clear the field. Choose File menu ? Export ?Excel. Enter the file name and click Save. Note For instruction on using the Browse form, you can watch the 'Browse Form' videos in the Eden Forms library on Tyler Community.Add holidays to ExcelBefore you calculate the time it took to process invoices, you must add your agency's holidays to the Excel file and formula to avoid counting them as work days. Copy the relevant holidays below. You only need to copy the left column, but you may copy the right column, too.Holidays01/01/20[xx]New Year's Day 01/[xx]/ 20[xx]Birthday of Martin Luther King Jr. 02/[xx]/20[xx]Washington's Birthday (President's Day)05/[xx]/20[xx]Memorial Day07/04/20[xx]Independence Day09/[xx]/20[xx]Labor Day10/[xx]/20[xx]Columbus Day11/11/20[xx]Veterans Day11/[xx]/20[xx]Thanksgiving Day12/25/20[xx]Christmas Day01/20/20[xx]Inauguration Day (every four years)Table SEQ Table \* ARABIC 1: Template for holiday dates.Inside Excel, paste the holiday dates after the last invoice row. Note Do not paste the holiday dates to the right of the table (orange column). Figure SEQ Figure \* ARABIC 2: Holiday dates copied to the Excel file.Update the [xx] placeholders to the current year and days. You are now ready to calculate invoice processing time using the method that matches the work week your agency follows: For a five-day work week, refer to the 'Five Day Workweek – Calculate the Efficiency' section.For a four-day work week (or any week that is not a five-day work week), refer to the 'Four-Day Workweek – Calculate the Efficiency' section.Five-Day Workweek - Calculate the EfficiencyWith the invoice information and holiday dates appearing on your Excel spreadsheet, you are ready to calculate the invoice processing time for a five-day work week. Calculate the processing time.For the first invoice click the first cell to the right of the first-row.Figure SEQ Figure \* ARABIC 3: The cell where formula will be entered.Enter the NETWORKDAYS formula into the formula field: =NETWORKDAYS(start_date, end_date, [holidays])Replace start_date and end_date with the cells that hold those values for the invoice. In our example, the formula will use the Received Date from column H, row 2, as the start_date and the Check Transfer Date from column G, row 2, as the end_date of the processing time period. Note You must select the Received Date value first (not the Check Transfer Date value). Otherwise, your calculations will be negative numbers.Replace [holidays] with the range of rows where you entered the dates for holidays. For our example, enter the cell range for holidays: C9 through C18. Figure SEQ Figure \* ARABIC 4: The completed formulaThe invoice processing time now displays inside the cell. Copy down the formula to the remaining rows. The rest of the rows fill with the invoice processing time.Calculate the averageTo calculate the average processing time, click the first cell below the column of invoice processing values. Figure SEQ Figure \* ARABIC 5: Cell for calculating average.Enter the AVERAGE formula into the formula field:Figure SEQ Figure \* ARABIC 6: The completed average formula.Press Enter to calculate the average processing time for the batch of invoices. Four-Day Workweek (or any work week) - Calculate the EfficiencyWith the invoice information and holiday dates appearing on your Excel spreadsheet, you are ready to calculate the invoice processing time for a four-day work week. Calculate the processing time.For the first invoice click the first cell to the right of the first-row.Figure SEQ Figure \* ARABIC 7: The cell where formula will be entered.Enter the NETWORKDAYS.INTL formula into the Formula field. =NETWORKDAYS.INTL(start_date, end_date, [weekend], [holidays])Replace start_date and end_date with the cells that hold those values for the invoice. In our example, the formula will use the Received Date from column H, row 2, as the start_date and the Check Transfer Date from column G, row 2, as the end_date of the processing time period. Note You must select the Received Date value first (not the Check Transfer Date value). Otherwise, your calculations will be negative numbers. Replace [weekend] with the work-week string and [holidays] with the range of rows where you entered the dates for holidays. For our example, enter the work-week string value (enter a zero for a work day and a 1 for a weekend day) and cell range for holidays: "0000111" and C9 through C18. Figure SEQ Figure \* ARABIC 8: The completed formula for a Monday-Thursday work-week.The invoice processing time now displays inside the cell. Copy down the formula to the remaining rows. The rest of the rows fill with the invoice processing time.Calculate the averageTo calculate the average processing time, click the first cell below the column of invoice processing values. Figure SEQ Figure \* ARABIC 9: Cell for calculating average.Enter the AVERAGE formula into the Formula field:Figure SEQ Figure \* ARABIC 10: The completed average formula.Press Enter to calculate the average processing time for the batch of invoices. ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download