Architekwiki
  • WIKI
  • Start Here
  • Resources
  • Other
    • MyCorbu
    • Reading List
    • OFFPLAN
    • Details
    • About
    • Sign Up
    • Terms of Use
  • MyCorbu APP
  • WIKI
  • Start Here
  • Resources
  • Other
    • MyCorbu
    • Reading List
    • OFFPLAN
    • Details
    • About
    • Sign Up
    • Terms of Use
  • MyCorbu APP

Architekwiki

A Resource For Architects
Subscribe

FINANCIAL PROJECTIONS MADE SIMPLE

1/8/2017

0 Comments

 
Financial Projections
I am going to share how to build a spreadsheet that you can use to project your financial future  -  kinda. 

Why 'kinda'? 

Well, because projections are notoriously 'iffy' - just too many unknowns. And almost all of them are beyond your control.

But even a poor projection is better than the crossed finger method.

I have used a spreadsheet like this for years to monitor our backlog of work. I have to admit that my interest in projecting fees into the future was usually because I feared that our work was drying up. It usually wasn't. Over time you learn that projections more than three months out aren’t very trustworthy - but really worth doing for the insights that your gain.
Financial Projections
​So let me explain what you are looking at and how you build it.
Alternatively you could purchase a copy of this one for $25 and edit it.
​
BUY OFFPLAN

HOW TO BUILD IT

Financial Projections
​I have a downloadable PDF of the spreadsheet that might be easier to follow along with. DOWNLOAD PDF.
​
In the first column list all your active projects. This is just text. If you have "sub projects" or additional services, give them their own line. Sometimes it makes sense to list phases on their own line.  The only rule is that it should make sense to you. These rows are never deleted. When they are no longer active, you hide them. I did this kind of maintenance whenever the height of the sheet got unwieldy. The gray highlight is my ‘note to self’ that this line can be hidden.

Financial Projections

Financial Projections
​The column headers to the right of the first column represent months. These are text headers and aren't used elsewhere. I usually keep between 18 and 24 months visible. I liked to have all or most of the previous year visible plus the current year to about six months into the future. Once again these columns are never deleted. If you freeze the initial columns, you can go a year or so before feeling the need to hide the oldest months. In the example, 2016 is actual data and 2017 is projected data.

Financial Projections

​Tip
If you are careful about adding new columns by selecting the last month before the vertical green border and adding columns to the right, you won't disturb either the formatting or the formulas. The green border acts as a buffer. Formulas include the green border in their range. This allows columns and rows to be added inside the border without affecting the formulas.
 
Before Microsoft modernized Excel, these hidden rows and columns would really affect the size of the file. I haven't noticed that being an issue for years.
 

Financial Projections
​This area at the intersection of project names and the future months contains your projected revenue. These are numbers formatted as currency with comma separator, zero decimal places and negative numbers shown in parentheses. (Typical for all numbers.)
The simplest way to allocate your remaining fee for each project, phase or service is to divide it equally over the months until you expect to be finished. You can find a downloadable spreadsheet here that makes quick work of that for you.  

Financial Projections

​More About Projections 
I project net fees. I deduct expenses for consultants from the total (or gross) fee to get this net amount. When you include the consultants' fees the complexity increases much more than the effort it takes to remove them. If you are following standard practice and paying consultants when you are paid, including them in the projections is a wash anyway. 

With experience you will get a feel for projecting different types of fees. Construction Administration fees are front end and rear end loaded because of submittals at the beginning of construction and punch list and close out activities at the end. Construction Documents fees start slow, then standardize, then increase as the deadline approaches.

Financial Projections
​These are *actual* revenue results in the 'past' months. Use number format. You simply replace your projection with the amount that you invoiced (less consultants). 

Financial Projections

Financial Projections
​At the far right are three columns that act as checks and balances. The first contains a SUM formula that shows the total of all actuals and projections for that row. The second contains your net fee for this work. The third contains a formula that subtracts the SUM from the net fee to give you the amount of fee not allocated. These are number formatted. 
​
These columns are a big help. When you are projecting the fee, you can see how you are doing. When you are entering actual results, you can see how your remaining fee needs to be tweaked.

Financial Projections

​Time Commitment 
This 'maintenance' to update OFFPLAN takes about an hour per month. The original setup to build your own version will probably take most of a day or more to enter formulas, format and enter data. This depends on your spreadsheet skills and whether you want historical data or not. This is really about the future so I wouldn't spend much time on history. You will collect enough of that data soon enough. 


​...or you can get  a spreadsheet ready to use for $22?
BUY OFFPLAN

Financial Projections
​This area is where your results are shown. I will explain each row in this area from top to bottom. All are number formatted. 

Most of this information is the result of calculations by your built-in formulas. Two exceptions are 'EXPENSES THIS MONTH' and 'SALES THIS MONTH'. These numbers are actual amounts that you add when you update the spreadsheet.

Financial Projections
Financial Projections
​REVENUES THIS MONTH - This is a simple formula totaling of all the Projected or Actual net revenues for the month.  

T12M REVENUES - "T12M" is an an abbreviation for Trailing 12 Months. It is a formula that looks like this in excel: *@SUM(C22 - N22)* where C is the first month of a 12 month range and N is the twelfth month.  I have faked the formula until you get to August in this spreadsheet. You will get approximate results if you research your revenues for the five ‘actual’ months and enter them. Your invoices have this info.
​
The purpose of calculating the total revenues for the past 12 months is to  track your trend in revenues. As this number goes up or down it is a much stronger indication of how you are doing than looking at monthly totals. It levels out the normal monthly ups and downs. Just one change in the T12M is cause for satisfaction or concern. Two monthly changes in the same direction is a definite trend. 

NET EXPENSES THIS MONTH - This is your projected or actual expenses without consultants. Depending on how you budget for expenses you may have this number for each month. If you budget by the year, put 1/12th of your annual expense budget here. If you don't budget at all, put 1/12th of last year's expenses from your tax return here. That is the projection side. Each month replace your projection with your actual expenses (less consultants).

PROFIT / (LOSS) - This is revenues minus expenses. A simple formula. A positive number is profit. A negative number shown in parentheses is a loss. 

Financial Projections
​RUNNING PROFIT / (LOSS) - This is a formula showing your year-to-date profit or loss. This is usually partly actual results and partly projected results. If you have a profit target you could insert a line of values above or below showing what your P/L  should be for comparison.
 
The formulas look like this, they add each month to the previous total:
JAN: "=(H28)", a simple cell reference
FEB - DEC: "=(I28)+(H30)"

The Running Profit/Loss is a 'maintenance' item. The formulas have to be added for each new year that you 'open'. It is simple to do. Select the Jan-Dec formula cells of the previous year. Copy. Select the Jan cell for the new year. Then paste. Just in case, double-check that everything is calculating correctly. 


​ADD SALES DATA
The operations side of things is now set up to give you a way of projecting how your finances will look. But there won't be anything to project if you haven't got a stream of work coming in the door. This tool can't help with business development but it can track how your business development is going. That's what the next two lines do for you. 

SALES THIS MONTH - On this line add the net fee value of any new work that came in during the month. I usually added the individual values as a simple formula: "= fee1 + fee2 + etc". This makes finding where you added the fee easier in case there is a change. I also added the project in (1) and allocated the fee in (3) using the three columns in (5). The estimated fee in (5) and the recorded sales should agree so your T12M Sales is accurate. 

T12M SALES - Just like the Trailing 12 Month Revenue formulas, the T12M SALES formula shows you the trend. Declining results means that the future holds less work for your firm. Increasing results is good news unless you are already swamped. The T12M Sales number could be telling you a different story than your T12M Revenue number. So this formula extends the usefulness of projecting just your fees alone. Unfortunately it will take you a year to get this information. (I am assuming that you won’t want to take the time to discover when each sale was made over the past year.)

​That's All There Is To It
The spreadsheet looks daunting because of its size, but it is actually pretty simple. Once set up you only need to do updates a couple of times a month when new work comes in or there is a change in scope for ongoing work. Then you do monthly updates when you have invoices and expense results. Once every six months or so add six months' worth of columns. 

This is really valuable information for less than a dozen hours worth of effort spread out over the year. 

Kickstart your Financial Projections with OFFPLAN.
There are three formats.
.xlsx, .number, and .xls
​ 
Just $22
BUY OFFPLAN
Picture
0 Comments

Your comment will be posted after it is approved.


Leave a Reply.

    Resources

    Start Here

    Picture

    Picture

    MyCorbu

    Trello-PM

    FeeCalqs

    Picture

    Terms of Use
    Your use of Architekwiki is implicit agreement with the 
    ​
    Terms of Use.

    RSS Feed


    Categories

    All
    Business Development
    Communications
    Concepts
    Design
    Detail
    Estimating
    Finances
    Legal Issues
    Management
    Miscellaneous
    Money
    MyCorbu
    People
    Planning
    Process
    Projects
    Records
    Services
    Specifications
    Standards
    Time
    Tools


    Archives

    February 2021
    January 2021
    December 2020
    November 2020
    October 2020
    September 2020
    August 2020
    June 2020
    April 2020
    March 2020
    February 2020
    January 2020
    December 2019
    November 2019
    October 2019
    September 2019
    August 2019
    July 2019
    June 2019
    May 2019
    April 2019
    March 2019
    February 2019
    January 2019
    December 2018
    November 2018
    October 2018
    September 2018
    August 2018
    July 2018
    June 2018
    May 2018
    April 2018
    March 2018
    February 2018
    January 2018
    December 2017
    November 2017
    October 2017
    September 2017
    August 2017
    July 2017
    June 2017
    May 2017
    April 2017
    March 2017
    February 2017
    January 2017
    December 2016
    November 2016
    October 2016
    September 2016
    August 2016
    July 2016
    June 2016
    May 2016
    April 2016
    March 2016
    February 2016
    January 2016
    December 2015
    November 2015
    October 2015
    September 2015
    August 2015
    July 2015
    June 2015
    May 2015
    April 2015
    March 2015
    February 2015
    January 2015
    December 2014
    November 2014
    October 2014
    September 2014
    August 2014
    July 2014
    June 2014
    May 2014
    April 2014
    March 2014
    February 2014
    January 2014
    December 2013
    November 2013
    October 2013
    September 2013
    August 2013
    July 2013
    June 2013
    May 2013
    April 2013
    March 2013
    February 2013
    January 2013
    December 2012
    November 2012
    October 2012

    Resources
VISIT ARCHITEKWIKI'S RESOURCES
Picture
Architekwiki | Architect's Resource | Greater Cincinnati | (859) 444-4560
© 2012-2020   Architekwiki​