P & L Drill Downs

In a previous section we describe how FTE Template2 is used to provide a second level of detail for employee and contractor expenses. There is an additional method of drill down available. For any profit center you can include a worksheet which employs formulas unique to your business or organization to calculate revenue or expense line item amounts. Line items in the budget are assigned to lines in the calculation worksheet using the Assign Line Items screen. Amounts from the calculation worksheet are "live" with the detailed P & L worksheet for that profit center.

 

This is a powerful feature as it allows Excel power users to create and manage complex worksheets that are interfaced to the overall budget system by the program. The program maintains all of it's worksheets and automatically updates any references to the calculation worksheet.

 

If you have an existing spreadsheet budget that employs a unique calculation scheme to determine budgeted amounts, this feature enables you to move your entire project into PerpetualBudget without having to re-do all of it.

 

A common application for this is the calculation of revenue from sales and any expenses that are directly or indirectly proportional to the calculated revenue amounts.

 

The worksheet naming convention is: "PDD <org_name>".

 

Structural rules for these sheets are the following:

 

Column 1 (A) must be the GL account ID.

Column 2 (B) must be the Organization ID.

Column 3 (C) must be a GL account name in order to be used as a drill down source row.

Column 4 (D) must be the Organization Name

Months 1-12 must be the range "F to Q".

Add a Named Range: PDDGLArea  A1:Dn, where n is the last row in the working range.

When the file is active and PerpetualBudget is activated the following screen is displayed:

 

 

Select Yes to add the worksheet to the PB project. This allows you to send copies of the PDD worksheet remotely to cost centers, directors, etc. Remote users familiar with the calculation worksheet can then modify the budget via their copy.

 

To assign a line to a receiving budget organization enter the three parameters above. Periodically the program will check the P & L Drill Down worksheets to update the line item assignments. You can also force an update by running PB - Tools - Workbooks - Update PDD. Any budget worksheets that need updating will show up in the update list.

 

PerpetualBudget will not maintain this worksheet but will maintain any budget worksheet that contains a reference to any of the lines in it.