Expense Sheet

1. Expense Sheet Templates Configuration
2. Adding an Expense Sheet
3. Expense Sheet Report
4. Expense Import Configuration
5. Importing Expenses
6. Expense Importer Watch Folder

1. Introduction

Expenses can be added either through the Expenses icon in the Time-report window or in the Project window. But when a User has been on a trip and collected 20 receipts for various expenses, the traditional way becomes too tedious.

The Expense Sheet feature simplifies this procedure by providing one table where the User fills in all the Expenses, and later on, another User approves them and they finally get added into the Project.

A typical workflow would be:

1. A User goes on a trip and collects receipts.
2. Upon return the User goes to the Project and creates an Expense Sheet. The total for each receipt is input on the appropriate line.
3. The User prints out an Expense Sheet Report and staple the actual receipts to it, and hands it over to the Accounts Department.
4. The Accounts Department User opens the corresponding Project in farmerswife, checks the Expense Sheet and approves each line separately to get the actual Expense added to the Project.

An Expense Sheet can be created for multiple Projects at once. For each line, the User can control which Project it goes to. And with the Split tool, Expenses can be split across multiple Projects based on how many percent of the totals should be on each Project.

Furthermore, the Expense Import functionality allows adding Expenses to Projects by importing them through a CSV file within the Expense Sheet window.

The Expense Importer Watch Folder that is also configured in the Server Setup > Projects tab, is not connected to any of the options described in this document. It is a custom developed functionality and not open for public use.

2. Expense Sheet Templates Configuration

There are no particular permission flags related to the Expense Sheet functionality. Any User who can add a simple Expense to a Project can also add an Expense Sheet, as long as there is an Expense Sheet Template configured in the Project tab on the Server Setup.

It is possible to set up multiple Expense Sheet Templates for the company. For example, one Expense Sheet for trips abroad and a different one for domestic trips. The Templates can contain pre-configured expense lines, Custom Fields, and other settings.

Click on the green plus icon next to Expense Sheet Templates and configure the following options:

• Name: Give the template a descriptive name that will allow Users to choose the correct one. E.g. Trip Abroad; Domestic Trip.
• Approval: Choose between Approval By Project Owner or No Approval Needed. In the first case, the Project Creator will be the only one who can approve the Expenses; and in the second case, the User who adds the Expense Sheet can approve them himself.
• VAT: Add a new tax by right-clicking on the table and selecting New. Each tax percentage results in a new column in the Expense Sheet. The User will input the Expenses including tax,under the corresponding tax column - the tax is then removed when the Expense gets added to the Project.
• Customs: This is where extra fields can be added to show in the header of the Expense Sheet, e.g. Start Date and End Date, if the Expense Sheet is being configured to be used for trips.
• Line Customs: This is where extra columns can be added to show in the expense table, e.g. Supplier. Note that these custom fields can only be created as Entry widgets.
• Allow Adding Custom Lines: If disabled, the Users can only use the pre-configured Expenses. If enabled, they can add new Expense lines.
• New Lines: Clicking the green plus icon adds an Expense as a new line to the Expense Sheet table. It brings up the Object Browser dialog for selecting an Expense to use. Once an Expense line is added, configure the options below.
• Sep: Toggling this checkbox controls whether there should be a blank line between that line and the previous line, purely for visual purposes.
• Group: Click to add a New Group, and then click again to select to which Group this line will belong to. The lines will be separated by groups, e.g. Cash and Credit Card. Also for visual purposes.
• Name: The Expense name can be modified but make sure to not change the meaning - this is important to bear in mind specially if you are using References for a Financial Integration.
• Budget Account: If Budget license is active in your configuration, you can define the target Account per line.
• Help Note: Optional setting if you want to add a help note for the Users to know how to use the line.
• CSV Import Mappings: Here is where the field mapping is configured if the Expense Import functionality is to be used. See more details in the Expense Import Configuration chapter.

3. Adding an Expense Sheet

To add an Expense Sheet to a Project, click on the Expenses icon and choose one of the available templates under Expense Sheet at the bottom of the menu:

This is how the actual Expense Sheet can look like, with explanations:

The Expense Sheet header:

• Number: As soon as the User finishes filling in all details for the Expense Sheet and saves it (by clicking OK), the system automatically assigns a unique consecutive number to it that serves as a reference.
  *Note that Expense Sheets saved as Drafts are not numbered until this option is unchecked.
• Name: This is the name of the Expense Sheet. By default, it takes the same name as the template, but it can be changed to any name that the User decides.
• Draft: If enabled, only the User who created the Expense Sheet can see it inside the Project, and as long as it is marked as draft, the lines cannot be approved. The purpose of this is to let the User save unfinished Expense Sheets without having them affect the Projects. When this option is unchecked, and as soon as at least one line is approved, the sheet cannot be changed back to Draft anymore.
• Custom Fields: The extra fields of information configured for this Expense Sheet will show in the header. One  example could be: Start Date and End Date.

The Expense Lines:

• Price Amount: If the Expense Sheet template was configured to include already some Expense lines, like in our example, the User just needs to fill in the price (including tax) in the corresponding Expense line and Tax column.
• Duplicate and Delete: Use the duplicate and the delete icons that appear just in front of each Expense line to perform any of those two actions.
• Help Note: If there is a question icon, hold the mouse over it to read the Help Note.
• Name: The Expense Name can be modified but make sure to not change the meaning - this is important to bear in mind specially if you are using References for a Financial Integration. By mousing over the Expense icon, you can still read the initial Expense Name.
• Date: The User can add the Expense date.
  
• Note: To add any note about the Expense.
  
• Approved: This is where lines are approved, one by one.
• Budget Accounts: This column will only show if the Budget module is licensed in your configuration. If the User has “Edit Budgets” permission, she can change what Budget Account the Expense should be attached to. Of course, this does not have to be the same User that created the Expense Sheet. For example, this might be done as part of the approval process,done by the Project Owner. The Project Owner might decide that some specific Expense Lines should go to a different Account than the Expense Sheet Template is set up for.
• Project: By default, the Expense line is to be added to the Project where the Expense Sheet is created. But the selector under this column, allows sending Expenses to another Active Project.
• Split: The Split button next to the Project column allows splitting the Expenses among two or more Projects. Click on the Split button to open the Split Expense Sheet area. Add one or more Projects and adjust percentages before clicking OK. Use it once all details have been filled in. It applies to all lines.
• Custom Fields: The extra columns configured for this Expense Sheet will show to the right of the Project column. In this example: Supplier.
• <Add New Line: If the Expense Sheet allows adding new lines, a green plus icon will show at the bottom of the list.
• The Totals: Totals for each of the Expense Groups (if Groups were configured) will show at the bottom, and under each of the tax columns. And a grand total below these.

Adding Expenses to Project: 
After marking at least one line as “Approved” and upon clicking OK in the Expense Sheet window, a pop-up dialog warns that “x Expenses Will Now Be Added To The Projects”.

Once you click OK on that message, there is another dialog that asks “Use Expense Rate As Sell Rate?” If "No" is selected here, all the added Expenses will have their respective Buy Rates, but their Sell Rates will all be 0. This is useful if the Expenses should be invoiced through a single line (by adding the total amount to that line). If "Yes" is selected, the added Expenses will have the same Sell Rate as Buy Rate.

If the Split functionality is used, the Expenses will be added to the corresponding Projects. This is how the Edit Project window looks like once the Expenses have been added:

4. Expense Sheet Report

The details of an Expense Sheet can be printed out using the Report icon at the bottom right corner.

5. Expense Import Configuration

The Expense Import functionality allows adding Expenses to Projects by importing them though a CSV file within the Expense Sheet.

The CSV can have the Expense Name, Price, Date, Note and Project Number where it should be added.
In the initial configuration a CSV will be selected to map headers from that file with headers in farmerswife Expense Sheet.
The CSV files to be imported through Expense Sheets in Projects must always have the same format than this initial file used for the configuration. Therefore, make sure to create as many Expense Sheet Templates as different CSV mappings are needed.

Configuration:

1. Prepare the CSV file to be imported:
   
   Use Row 1 to add the column headers.
   If there is a date header, configure the corresponding date format on the cells below that header.
   For the price cells select Currency format, and configure the decimals. Make sure it is the same format used in farmerswife.
   
   
2. Configure the Expense Sheet Template:
   • Create a template as explained under the Expense Sheet Templates Configuration chapter.
   • Remember that the following column headers already exist: Name, Amount, Date, and Project.
     * The field Note did not exist at the time when this functionality was developed, therefore it is not available in the mapping. You could create a Line Custom for it and call it e.g. Details.
   • Use the Line Customs functionality to create as many extra column headers as required, e.g. Approved By, Supplier, Prepared By. These Line Custom Fields, which can only be Entry widgets, can be configured to have a default value that will be imported into those rows where the CSV has no value.
   • Once the template is ready, click on CSV Import Mappings to configure the mapping.
3. Map the headers from the CSV file with headers from the Expense Sheet Template:
   
   
   • Click on Load CSV File and select the CSV file that you prepared on step 1.
   • Select the separator character (comma or semi-colon depending on your Locale Settings).
   • In Headers Line Number, type the number of the line where the column or header names appear, so farmerswife will be able to read them for the parsing (also when starting to use the Expense Import, Expenses will be imported from the following line as configured here).
   • The CSV Import Mappings window now displays the column headers from the selected CSV file, and next to each a selector allows mapping them to the corresponding header in the farmerswife Expense Sheet Template (standard headers and customized headers).
   • Create a template as explained under the Expense Sheet Templates Configuration chapter.
     
     
4. Configure the rest of the options in the CSV Import Mappings window:
   • Set All Approved: If checked, all imported Expenses will be set as approved by default
   • Allow Attaching Lines to Closed Projects: Enable this option to allow imported Expenses to be added to Projects that are set to Active: No and even Invoiced.
   • Imported Lines Icon: Select an icon for the imported Expenses.
   • Email Notifications: Enter the email address of those people who are to receive a notification email when lines fail to be imported.
   •  Date System: Select the date format that has been configured in the CSV file.

Extra configurations:
It is possible to configure which Projects will allow Expenses to be added through this functionality, and also which will allow Expenses to be deleted.
You need to create two separate Check box Custom Fields at Project level (in Server Setup > Projects tab > Project Customs) with the label name Allow E.S. Import, and Allow E.S. Delete.

Then only enable the field Allow E.S. Import on those Projects where Expenses can be added through the import functionality.
And likewise, enable the field Allow E.S.Delete only on those Projects where Expense deletion should be permitted, when Expense had been added through the import functionality.

6. Importing Expenses

To import Expenses from a CSV file, log into the farmerswife Desktop Client application and open the Project where Expenses are to be imported.
In order to easily manage imported Expenses, you can create a Project per year, e.g. “Expenses 2015” and use it for importing CSV files.
In the Edit Project window, click on the Expenses icon and select the template that was created for importing Expenses.
As it opens, click on the Import Expenses button in the bottom right corner and browse for the CSV to be imported.
Expenses will be imported and each column will display the information as it was mapped in the initial configuration.
The information can be modified, for example the Project where the expense is to be added.

When the checkbox in the Approved column is greyed out, it means that the selected Project for this line does not allow import (see Allow E.S. Import).
As soon as the lines are flagged as approved and you click OK, a pop-up window will inform of the quantity of Expenses that will be added to the corresponding Projects. Next select if the Expense Buy rate should also be added as Sell rate (otherwise the sell rate will be set to 0).

Expenses are finally added to the corresponding Projects.

Next time this Expense Sheet is opened, the Approved column appears locked, as these Expenses have already been added to the Projects. Note that the Expense Sheet is also accessible from the Projects the Expenses refer to.
The Expense Sheet has a Number and a Name. If Expenses are to be imported per month and each User fills in his own CSV, the Expense Sheet Name can be e.g. “May expenses / LisaTaylor”.

If an Expense Sheet is deleted, the Expenses in there will be deleted as well and they will be removed from the Projects where they had been added, except from those Projects where the custom field “Allow E.S. Delete” is disabled.

Notifications Email

A notification email will be sent on failed CSV imports. The email will contain a CSV file attached, containing only those lines that failed to be imported and new column at the end with a note on the failure.

These are the reasons for failure:

• Amount is empty, 0, negative number or value different than numbers.
• Project does not exist.
• Date for mat is wrong or empty.

The corrections can be made on this CSV and after saving it, it can be imported again into the Project

7. Expenses Importer Watch Folder

By creating a "Watch Folder" and configuring it correctly, we will be able to get our expenses automatically added to our projects.

We need to save our expenses in a .csv file paying attention to the following formats requirements to make sure the upload works correctly:

• The file has to have this 7 sections in this exact order:

1. Project Number
2. Date
3. Name
4. Note
5. Buy
6. Sell
7. LineID

• Each row must have there 7 columns.
• Example filename: expenses123456.csv.
• File extension must be csv lowercase.
• Buy and See if a decimal MUST use "."
• Date must be dd/mm/yyy.
• LineID can be empty = " ", else a unique line number for the project to avoid the same expense being imported twice.
• Lines that failed to be imported will be added to a file in the watch folder called errors.txt, it's recomemnede to delete this file once the errors have been read.
• Importer will import any csv files if finds in the watch folder, after it's imported it's deleted.
• Lines failed to import due to a locked project will be retried every minute.
• Server checks watch folder on server start and then every minute.
• Imported expenses becomes the same as adding a custom expense.

Save it as a *.csv file or windows coma separated file. Then create a folder called "Watch Folder" and there place the files.

Once in the farmerswife server, access the Server Setup > Projects tab. Scroll down to the section "Expense Importer Watch Folder". Here select the route for the previously created "Watch Folder" and press ok.

Now we log into our farmerswife client and we will see our expenses added at the bottom of the correspondent projects:

Once the file is ingested by farmerswife it will be automatically deleted. 

Only if there is an error uploading the expenses an "error.txt" file will be generated in the "Watch Folder". It is recommended to erase this file after reading it.