Automated 402-2 OUTLAY REPORT

1. Automated 402-2OUTLAY REPORT

2. The 402-2 is an Excel spreadsheet which is used to track all funds related to the project, from beginning to end.

3. The upper section of the 402-2 is used to change contract amounts (Change Orders), engineering amounts (Eng. Amendments), to track interest earnings (and final balancing). Interest Change Orders Engineering Amendments

4. The lower section of the 402-2 is used like a checkbook. You insert dates, payees names and purpose, and amounts. The totals are calculated automatically. Payees Amounts Dates

5. The lower left section of the 402-2 is divided into columns formulated to keep running totals of dollar amounts for line items. “Project Control” is a protected column which keeps a running total of individual entries, and the balances for the overall budget The protected, shaded areas of the columns directly to the right of Column C contain formulas which keep a running total of line item costs

6. The lower right section of the 402-2 is divided into columns formulated to keep running totals of dollar amounts for funding sources. “Funds Control” is a protected column which keeps a running total of individual draws and the balances for the overall funding The protected, shaded areas of the columns directly to the right of Column P contain formulas which keep a running total of funding dollars

7. The 402-2 will be set up with basic information (names, beginning balances of funds and line items, etc.) The contractors are listed The project, owner’s name, address and bank account information are included in this area The beginning balances are shown on the yellow shaded row

8. During the Construction period you will be responsible for entering interest earnings, change orders and engineering amendments………. Interest Change Orders Engineering Amendments

9. You will also enter the dates of payments, the payees and the purpose, the amounts of the invoices and pay estimates, and the source of funding Amounts from each funding source Payee & purpose Amount Dates

10. Lines 61-64 contain formulas which will display information resulting from entries in the “white” cells of the spreadsheet… Line 61 - Includes change orders, engineering amendments, transfer of amounts among line items (final balancing) and changes to contingency via additional funding. Line 62 - Adjusts balance of contracts, agreements, or line items Line 63 - Totals the amounts spent on invoices, pay estimates, etc. Line 64 - Shows the balance of funds available for that particular category.

11. Items entered at the top of the spreadsheet (change orders, interest earnings, etc.) are automatically reflected on line 61 of the “Contingency” column The entries for change orders, engineering amendments and interest… are totaled together and added to the beginning balance… resulting in an adjusted contingency balance.

12. Cell B-58 shows funds on hand in the bank after deposits (including earned interest), and withdrawals. 1 - the entries for individual payments are entered under the appropriate line item 3 - Cell B-58 reflects the amount of funds on hand in the bank 2 - The corresponding funding source is credited

13. You can modify the spreadsheet to meet your specific need (delete some of the columns, rename the headings, etc.)

14. To rename headings, delete columns, etc. you must “Un-Protect” the spreadsheet. • Click – Tools • Click – Protection • Click – Unprotect Sheet After making needed changes please “Re-Protect” the spreadsheet. • Click – Tools • Click – Protection • Click – Protect Sheet (the password has been left blank)

15. However, there are some columns you cannot delete….. Interest Earnings Column Columns A - C The Contingency Column The Funds Control Column

16. Once you have completed your entries for the month, you should print out a new Outlay Report for your records.

17. PRINTING……

18. NOTE • You must designate a print area. If you do not, Excel will assume that you want to print the previously selected data, or if no data has been selected, the entire spreadsheet. (The entire spreadsheet is over 500 printed pages, so do not send a print command for the full spreadsheet!)

19. PRINTING…… • To print the areas you want shown on your current month’s outlay report, you should: • Highlight the area of cells that contain the current month’s entries. • Click on “file”, then “print.” • Click beside the area titled “selection.” • This will print a report showing the current month’s outlay, complete with revised numbers. The “file, page setup, sheet” menu has been modified to cause rows 50 - 66 to print at the top of each report, so you only need to highlight the current information to make each month’s printout complete.

20. PRINTING…… • You can also customize the print job: • Click “file”. Then click “page set up” on the pull-down menu. • Once the page setup dialog box is opened, click on the file tab marked “sheet”. • The print area has a place to type in the range you wish to print.

21. PRINTING…… • Example: • If you typed in A67:AJ68, the first two rows of the payment area of the spreadsheet would be printed. In addition, “50:66” has been pre-filled in the area after “rows to repeat at top.” This tells the computer to print the information in these rows at the top of each sheet; therefore, you only need to insert the current rows you are working on for each report. • If you wish to print the area at the top of the spreadsheet (change orders, etc.), simply put a comma after the range designated in the print area, and type in “A1:R49.” This will print the section as a separate page. Since the numbers you type in the change order area are reflected in the formulas on row 61, you may not wish to print this area every time you print a report. • Click on “o.k.” • Click on the print icon on the toolbar.

22. Saving Your Work You should save your work each time you make changes to the Outlay Report. When you first open the spreadsheet, click on “file”, “save as” and give the file a new name. I typically use the date of my report as a part of the name to be able to more easily recognize the report. That way I always know I am working with the most recent report. It also allows me to go back and look at the financial status of a project at a given time. Be sure to save our work when you close a spreadsheet.

23. Question and Answers…..