KBA-01840: Adjusting Period Distribution Using Schedule Data

Question:

Can I load data into my Project Cash Flow (previously Period Distribution) workbook from an outside schedule-driven source, using some sort of import workbook? Can I use the Schedule Doc type within Spitfire for the same purpose?

Answer:

Yes, in V23+.  Note that in V23, the Period Distribution Doc type was renamed Project Cash Flow and the Period Distribution (PD) workbook was renamed Project Cash Flow (PCF) workbook.

You will need a Project Cash Flow document and a special import workbook.  There are some prerequisites.

Project Cash Flow Prerequisites:

  • The snapshot of the PCF workbook should be current.
  • The status of the Project Cash Flow document must be In Process.

Import Workbook Prerequisites:

  • The workbook must contain a worksheet labeled Schedule.
  • The Schedule worksheet must contain the Project ID in cell B1.
  • Optionally, the worksheet can contain the Project name in cell B2.
  • The worksheet must contain the following four columns in this order: Cost Code, Account Category, Start Date, End Date. Column headers should be in row 4.
  • Worksheet data must start in row 5.
    • Start and End Dates must be entered in a date form (so Excel recognizes them as dates) but the display format can be what you choose.
    • The Start Date should be later than the current working month.
    • The End Date should be before the Finish Date on the Project Setup.
    • Data for the Account Category column is optional, but if used, all rows must include the Account Category (as spelled exactly in Spitfire).
      • If the Account Category data is omitted, then all rows with the same Cost Code will have their amounts distributed base on the Cost Code Start and End Dates.
      • If Account Category data is included, the Start and End Dates associated with each Account Category will be used.
  • The worksheet must be sorted by cost code in ascending order.

Note: Spitfire distributes several Schedule import workbooks, which you can use instead of building your own.

Examples of Import Workbooks:

How To Use:

  1. If you are using a Schedule document, attach the import workbook to the Schedule document, then use it to fill out your dates.
  2. Create a Project Cash Flow document.
  3. If you are using a Schedule document, link the import workbook also to the PCF document.
    1. On the Schedule document, select Move/Link File from the drop-down menu on the attachment row.
    2. Fill in the Target Doc Type as Project Cash Flow and the Target Document as the name of your PCF document.
    3. Click the Link to Both Docs button.
  4. Otherwise, attach the import workbook directly to your PCF document (or have it elsewhere on your computer).
  5. Open the PCF workbook.
  6. Select the By Schedule option from the Spitfire Menu ribbon. You will prompted for a Schedule Workbook.
  7. Browse for or select the import workbook (that has been configured as instructed above) then click the Go button.
    Note: if an incorrect workbook is selected, Spitfire will display an “incorrect workbook structure” message.
  8. After the import, you will be reminded to review the results and either Save or Close without saving. Click OK to clear the message.

Amounts will be distributed by Cost Code (or Cost Code and Account Category) between the Start and End Date periods. The import data will overwrite existing information in the periods, thus allowing you to move money forward based on new Start and End Dates.


KBA-01840; Last updated: July 14, 2023 at 15:07 pm