Topic

Messages

Financial Projections

I would like to know how other C&P users compile financial projections. I type our projections in excel and calculate Gross profit off of our projected billings by multiplying projected billings by our avg. gross profit percentages. Pretty straight forward but what I am interested in is how do you compile your projected billings. Using history for some media clients can be helpful but collateral billing is not as straight forward. I have been using the Estimate vs Billings report under the analysis section of job reports. I'll print this report and let the Production & Traffic managers and the AEs go through it and label which jobs will bill which months. My projections are done by Month. This is time consuming and delayed. By time I get the reports back a week has gone by and my report does not reflect what has transpired in the last week. What I am thinkging about is a system that we could continually update so at anytime I could run a report and list what we project to be billed in each month from the current open job list. I'm thinking about using the Billing status code for this. Currently I am using the billing status code to be able to group billed jobs together by month for the profitability reports. Any ideas on this subject?

Thanks,

Brian Woolard
Chief Financial Officer
O'Brien et al. Advertising

Do you mean short term cash flow month-to-month projections? Or do you mean an annual budget/financial forecast?

I use excel in determining both. It's taken me many years to develop the formats. I use C&P to gather data and maintain back-up ledgers, but thus far find forecasting done best in excel.

Roxanne Cowan

Brian,

As far as projections occur this is what we do:

First of all we use classic - not sure how this would transfer to other systems.

I export the following tables for the report -

AR ARITEMS CLIENT JOBS

I have these tables in a tab delimited format -

In MS Access I have built an estimating screen - projected billing screen. Basically the job is input and the estimated billing is applied across the months.

The report contains the following broken down by group (marketing, pub, print, web)

Client - Job Number - Job Description - Project Director - Budgeted amount (Fee for service only) Amount billed to date for fee for service - Balance left in job to be billed - Current months billing - estimated billing amounts by month for 9 months out - finally any unearned amounts -

I have a master copy that i go through during the month to make sure that the items are accurate. I also have a report that goes out by project director to their e-mail for only their projects and only for 3 month duration. This way if there are any differences I can be given the information or changes that are necessary.

Some of the other items that i have that helps the accuracy of the report - The only projects that are reported are the ones that are not closed. Also, they have to have a status greater than or equal to 3 (but less than the 999 for closed)

I have also excluded non billable projects (no need for that information => if i cannot bill it i don't need to see it here) I also exclude any projects that the estimate is not approved.

Also, I have a report of any items without a Project Director that goes to the Creative director (through e-mail) so they can put in who the project director is in the system.

Also, I have 2 other reports that i call the gotcha reports - they are reports that track any activity in a project that exceeds 3 hours and if the project does not have an approved estimate (or incorrect status) This way if a project director has forgotten a step that will catch it.

I only have a few persons who can close jobs as well.

If you want more info on this report or how it works let me know. It most likely sounds more complicated than it is. The longest time is spent updating the records in the estimates - The next step that i have for this system is to set it up on a thin client so that the persons on Mac's can actually enter the data for the expected billing date themselves.

Thanks

Bill Riendeau
Brown & Company Graphic Design, Inc.