MONTHLY EXPENSE REPORT TEMPLATE HOW TO
MONTHLY EXPENSE REPORT TEMPLATE FULL

To circumvent that issue, you have to work out a firm but fair expense report policy that every single employee must know and follow. Check out these tips in making an expense report to get started.Īccording to a Forbes article, expense report fraud can happen a lot of times.

If you have gone through this, you know how important it is to keep your income in check. There are events where employees need to pay for the business costs out of their budget. Through it, you can control your business’ finances, and you can also manage the cash flow properly. Expense reports would often come with proof of purchase and produced monthly, quarterly, or yearly. Here's a second sample, using the same dates, but with the categories of some expenses changed.Expense reports are for the tracking of expenditures incurred for which they would request for reimbursement. Items::$D,$A2 The category name in column D matches the name in the header cell of 'this row' Items::$A,'<'&EDATE(B$1,1), The date in column A is before the date one month after the date SUMIFS(Items::$B,Items::$A,'>='&B$1,Items::$A,'='&B$1, The date in column A is on or after the date in the header cell of 'this column',

MONTHLY EXPENSE REPORT TEMPLATE FULL

Each cell contains a full Date and Time value, but is formatted to display only the short monthname and the day nuber (1).Įach of the body cells contains the formula below, entered in B2, then filled to the end of row 2and down to the last row of the table. Row 1 contains the first of the month date for each month to be summarised.

As noted above, these must be an exact match to the categories entered in the Items table. Set the first one to 'none', then use the Fill button (small yellow circle that appears on the bottom edge of the cell when you bring the pointer close to it) the drag a copy into all the cells in that column.Ĭolumn A contains the list of categories to be summarised. Select the cells now containing category names, then set the Data Format for the selected cells to Pop-up menu, and set them to 'Start with blank'Įach of those cells then contains a pop-up menu, all with the same choices, and all showing the same value as was originally pasted into that cell. The easiest way to ensure this is to make the list in Column A of Summary, copy that list, then select cell D2 (one click) and use Edit > Paste and match Style to Paste the list into column D of Items. The column (or columns) does not enter int the formulas.Ĭategory names in column D must match the names in the list of categories in column A of the Summary table. I left formatting at 'automatic', you may want to use 'number' with 2 decimal places or 'currency' with two decimal places.Ĭolumn C represents additional columns used the add details. If the date is entered as shown, Numbers automatically appends the current year to the Date part, and adds the Time part, set to 00:00:00 (midnight, at the beginning of the day.Īmounts in column B are entered from the keyboard. ALL 'dates' in Numbers are Date and time values. The summary is constructed using the formula shown below the tables (and copied below, with notes).ĭates in column A are entered as the items are recorded. If the tables have distinct names, not shared with any other table in the document, then the Sheet names are not needed in the formulas (and Numbers will drop them if you include them). I'd suggest doing the same while setting them up, then changing the names to what you want once you have the formulas up and running. I used short names to keep the formulas short. I want it to grab the two 'Telephone and Utilities' amounts from sheet #2 (Bell and Shaw) where I filled it in by date and have the sum show up here on sheet #4, so I can just copy these amounts onto my tax return. The second screenshot has the categories from the drop-down menu of type of expense. On the 'business expenses by category' sheet (aka Sheet #4/screenshot #2), I want it to add up the items chosen from the drop-down menu in that 'Type' column.Īgain, the first screenshot is my business expenses for May. Advertising, Supplies, Telephone and Utilities.

I made 4 sheets: (1) revenues, (2) expenses by month (screenshot #1), (3) profit, and (4) business expenses by category (screenshot #2).įor each expense on the 'expenses by month' sheet (aka Sheet #2/screenshot #1), I made a 'Type' column (3rd column in screenshot #1) whereby I used the exact 'business expense' name that we need for our tax return in Canada, e.g. I made a 'revenues and expenses' document that I want to use to keep track of my sales and expenses, as well as use to fill out my taxes in Canada every year.

MONTHLY EXPENSE REPORT TEMPLATE HOW TO

How to Sum by Type of Expenses Please note that I rarely use spreadsheets (total beginner), so please be simplistic and step-by-step in your answers.

Monthly expense report template