What reports do I need

1

I presume that I need to use custom reports or request that new reports be included in Manager. But I am not sure the best way to go about this to achieve the desired outcome.

What I want to do is the following:

In January of each year, I want to create a report where I can record the following:

  1. All Income expected from April that year to March the following year
  2. All expenditure expected from April that year to March the following year
  3. Starting Bank Balance on 1st April
  4. Closing Bank Balance 31st March the following year
  5. I need to include Balance Sheet Income/Expenditure from 1st April that year to 31st March the following year

What I want to achieve is the following:

Essentially what I want to do is work out (approxiamately) how much money I will have on the 31st March of the following year so that I can do the following:

  1. Calculate how much I can pay myself a salary/dividend from the 1st April that year to 31st March the following year
  2. I want to see how much money I can spare for buying new equipment (like a new server), what my advertising budget could be or how much money I have to spend on database development for example.
  3. Essentially I want to ensure that I don’t run out of money before the 31st March either on the personal or business side.

Points to consider about my specific business

My clients work on an annual contract system starting from 1st April to 31st March. So by the end of January, I will know roughly what my anticipated income and expenditure will be for IT Support, Inventory Cost and Inventory Sales and for Network Cabling Installations as I now do the budgets with my clients in January for the financial year starting that April coming up.

So my business is not like a shop, when I don’t know from month to month how many customers will walk through the door. Obviously I am not going to get an exact closing balance amount for the 31st March as new work comes in during the financial year and I don’t win all the business budget for in January. However, I will be within a couple of thousand Pounds of the mark. The idea is more to get as much as possible an educated guess as to the disposable income that I will have that financial year, that I can spend on salary, dividends and investing in company assets.

Problems that I am currently encountering

I am currently using the Profit and Loss Budget Report which is really helpful.

However, I have to use an excel spreadsheet to tot up the inventory cost and inventory sales and to record each client’s IT Support income to get the totals. This is actually very time consuming. It took me hours to do this last time.

Would it be possible to create a custom report where I can add all the quotes that I have created in January to create an income/expenses report for each client for the financial year coming up? I already have all the information I need in my quotes and in the inventory Items fields. It is just a question of pulling the information from quotes and inventory items to a custom report for each client.

The Budget Report does not show starting and closing Bank Balances, nor does it show balance sheet income/expenditure. In addition, for example my accounting bill is paid in one year, but is actually split between two different financial years, as is my corporation tax - actually paid this year, but is allocated to the previous financial year. All this distorts the so called profit/loss balances and income/expenditure, making it harder to determine what actual spare cash I will have.

Would it be possible to create a custom report where I can use the Profit and Loss Budget Report (as most of the income and expenditure information is already there) and add Starting and Closing Bank Balances and move balance sheet income/expenditure to show it as money spent/received in that financial year. In addition to also remove employee expenditure (salary and petrol and dividends) as that is spare cash.

In simplest terms, I think my biggest problem is that the profit and loss budget totals bear no relationship to what I have actually got in my bank accounts at the beginning or end of a financial year because of things like dividends, corporation taxes (balance sheet accounts) as well as employee expenditure which results in very different figures from bank balances.

I don’t know if the approach that I have suggested is the best way to do it, but I assume that every company has the same problem. You want to know how much spare cash you will have after all operating expenses are deducted from income so that you can see what you have to spend on new equipment or pay a higher dividend etc.

2

What you are describing is a “Cash Flow Forecast”. The “Receipts and Payments” report in “Manager” shows historical cash movement(flow). Maybe this report could incorporate a “forecast” option similar to the P&L “budget” option.

3

None of the custom reports you describe is possible, because the data does not exist in the database. Your discussion about balance sheet income/expense does not make sense, because the BS describes position and the P&L reports performance.

4

@AJD That is probably the correct term. I deliberately did not use terms like Cash flow forecast so as not to confuse people by using the wrong terminology.

My thinking is that the sales quotes essentially contains all the information that I need because it contains the inventory I want to sell and obviously the inventory items fields contains the cost price. If I can somehow get Manager using Custom Reports to display specific quotes for clients and tell it to show inventory cost etc, this would go a long way towards solving my problem. All the information is there. Currently I am having to create an excel spreadsheet listing all the items that I will be selling over those 12 months and then going through each quote to see who is buying what and in what quantity and then having to go through the inventory fields to find the cost prices.

I will google Cash Flow Forecast and see what other people do to ascertain how much money they will have over the upcoming financial year as this is something that I really need in order to be able to budget for Advertising, Capital Purchases etc.

5

The data does exist in the database. In the quotes and inventory items tabs!

I am interested in knowing that I will have £xxx on the 1st April 2019 in my bank account and that I will have £xxx in my bank account on the 31st March 2020. Which is why I need a report like a cash flow forecast to show this, not one that is only concerned with profit and loss or BS transactions.

6

correction. The Receipts and Payments Report forecasting is not what I need as this deals with cash flow as in actual cash, not with sales and purchase invoices

While I am interested in cash flow in the context of following how much money is going in and going out of my bank account - I mainly use Sales Invoices and Purchase Invoices, not actual cash. So I would need a forecast report that includes invoices.

7

So it’s an Indirect Cash Flow Analysis. It has been discussed even recently in a topic but there is nothing yet. Once there will be this report we can extend it to forecast but I see it has very remote.

About quotes, they don’t have effect on any report.

8

Indirect Cash Flow Analysis. Never heard of that term! But looking it up, it sounds like what I am looking for.

Quotes currently don’t have any effect on reports true. But that doesn’t mean that it can’t if a custom report or inbuilt manager report could be designed to include them. As the quotes include most of the information that I need to create the report that I want, it makes sense to use the quotes instead of having to re-enter all the information all over again somewhere else.

But I will close this topic as it’s obvious that Manager can’t do this right now. I will have to make do with Excel to arrive at the figures that I need

9

Yes @dalacor the data in the Quotes tab and the S&P Order tabs can be filtered using the search function and exported to Excel to produce basic reports. The currency sign can be separated from the amounts using “Text to Columns” function delimited by “space” so the amounts can be summed.

10

I wasn’t clear enough, apparently. While sales prices can be included in inventory item definitions and sales quotes, all reports rely on querying tables in the database. And you were specifically asking whether custom reports could produce the forecast you want.

Line item information (such as inventory items) is not included in the sales quote table, only header and custom field information. And there are no transactions entered for a quote, so nothing is available when querying general ledger transactions. Therefore, you cannot do what you want with a custom report. That was my point. Being available somewhere in the database doesn’t mean a bit of information can necessarily be queried with a custom report, and certainly not operated on to produce a forecast.

The step from where Manager now is to what you ask is not small. I’ve worked with very large businesses that do all their forecasts in spreadsheets. You just have so many more tools, including display graphics options. Of course, all those tools could be added to Manager, at a cost of hundreds of megabytes of code and many millions in development cost.

11

I am not sure if you mean open open sales quote, then type say customer name and press search and then click on export. What this does is it creates an exact replica of the view I am seeing - Date, Sales Quote No, Client, Title and Amount. I need to be able to see the line items in each quote. I can’t do that if that is what you are suggesting.

I will have a look and see if I can’t use an api to pull this information into an excel spreadsheet as this may be the best way to do it. I don’t have a problem using Excel, my problem is that it is time consuming to go through all the quotes in Manager and copy across the information.

I will investigate using an api to achieve this. Thanks