Develop Tool for Project Cash Flow - Linked Speadsheets, MS Projects, SharePoint
Budget: $2 – $8 USD
[SCOPE OF WORK and TEMPLATES are ATTACHED]
Goal:
Populate a “Project Cash Flow” spreadsheet (Microsoft Excel, .xlsx) using inputs from a Bill of Materials file (Microsoft Excel, .xlsx). Populate attributes of the Bill of Materials file (Microsoft Excel, .xlsx) using inputs from a project schedule file (Microsoft Project, .mpp).
Tools:
MS Project
Excel
SharePoint
Templates Provided by BoxPower:
PROJECT SCHEDULE
[.mpp | "Sample Project Schedule” ]
Each Project Schedule is updated and managed by a Project Manager.
Every Project Schedule consists of a list of tasks.
Each task has the following attributes:
Task Name (format: text)
Start Date (format: “short date” mm/dd/yyyy)
End Date (format: “short date” mm/dd/yyyy)
Cash Amount (format: number, positive or negative, 2 decimal places)
Project Managers will regularly adjust attributes of tasks within the Project Schedule as events in the project change.
Project Managers may create new tasks within each Project Schedule.
Project Managers may delete existing tasks within each Project Schedule.
BILL OF MATERIALS (BOM)
[.xlsx | “template - PROJECT BOM”]
Every BOM will include lists of line-items.
Each line-item details a project cost (e.g. a piece of equipment to be procured, a service to be purchased, etc.) or project revenue (e.g. a progress payment).
Each line-item has the following attributes, among other attributes:
Item Name: (format: text)
Payment Terms: (format: # of days; percentage of total payment)
Payment Date: (format: short date mm/dd/yyyy)
Payment Value: (format: number)
Actually Paid: (format: ternary logic y/n/s)
The Payment Date of each Bill of Materials line-item is determined by adding the “Payment Terms” value to the “Work Completed” date – the “Work Completed” date is informed by the end dates of corresponding tasks in the Project Schedule, and therefore the authoritative “Payment Date” is a product of inputs from the Project Schedule.
PROJECT CASH FLOW
[.xlsx | “template - Per Project Cash Flow Sheet"]
Every Per-Project Cash Flow Sheet will include a single list of line-items.
Each line-item details a project cost or project revenue.
Item Name: (format: text)
Payment Date: (format: short date mm/dd/yyyy)
Payment Value: (format: number)
In addition to the attributes attributed to each line-item, the Per-Project Cash Flow sheet also features a matrix of weeks throughout the duration of the project.
Each line-item inserts a value into the matrix of weeks based on its payment value and payment date.
Each column (week) is summed to determine total project costs and total project revenues by week for each week of the project’s life cycle.
Each cell in the matrix that is populated by a value should have conditional logic implemented such that if the Actually Paid value is YES, the cell is colored red; if the Actually Paid value is SCHEDULED, the cell is colored yellow; if the Actually Paid value is NO, the cell is not colored.
Consultant shall:
Propose options for linking line-item Payment Date in BOM file to corresponding Task End Dates in Project Schedule file.
Pay special attention to methods that would enable Project Managers to avoid manually linking each line-item in the BOM file to an analogous task end date in the Project Schedule – e.g. task name / line-item name matching, etc.
Propose options for linking Payment Date and Payment Amount in BOM file to the corresponding cells in Per-Project Cash Flow sheet.
Pay special attention to methods that would enable Project Managers to create and delete lines in the BOM .xlsx file without disrupting linkage to the Per-Project Cash Flow sheet.
Present to BoxPower a list of options for linking these 3 files, including pro/con presentation and explicit explanation of areas that would require manual upkeep by PMs to maintain links between sheets.
Implement linking and template modification as directed by BoxPower based on the presentation of options.
Perform 1 round of “troubleshooting & revision requests”
Goal:
Populate a “Project Cash Flow” spreadsheet (Microsoft Excel, .xlsx) using inputs from a Bill of Materials file (Microsoft Excel, .xlsx). Populate attributes of the Bill of Materials file (Microsoft Excel, .xlsx) using inputs from a project schedule file (Microsoft Project, .mpp).
Tools:
MS Project
Excel
SharePoint
Templates Provided by BoxPower:
PROJECT SCHEDULE
[.mpp | "Sample Project Schedule” ]
Each Project Schedule is updated and managed by a Project Manager.
Every Project Schedule consists of a list of tasks.
Each task has the following attributes:
Task Name (format: text)
Start Date (format: “short date” mm/dd/yyyy)
End Date (format: “short date” mm/dd/yyyy)
Cash Amount (format: number, positive or negative, 2 decimal places)
Project Managers will regularly adjust attributes of tasks within the Project Schedule as events in the project change.
Project Managers may create new tasks within each Project Schedule.
Project Managers may delete existing tasks within each Project Schedule.
BILL OF MATERIALS (BOM)
[.xlsx | “template - PROJECT BOM”]
Every BOM will include lists of line-items.
Each line-item details a project cost (e.g. a piece of equipment to be procured, a service to be purchased, etc.) or project revenue (e.g. a progress payment).
Each line-item has the following attributes, among other attributes:
Item Name: (format: text)
Payment Terms: (format: # of days; percentage of total payment)
Payment Date: (format: short date mm/dd/yyyy)
Payment Value: (format: number)
Actually Paid: (format: ternary logic y/n/s)
The Payment Date of each Bill of Materials line-item is determined by adding the “Payment Terms” value to the “Work Completed” date – the “Work Completed” date is informed by the end dates of corresponding tasks in the Project Schedule, and therefore the authoritative “Payment Date” is a product of inputs from the Project Schedule.
PROJECT CASH FLOW
[.xlsx | “template - Per Project Cash Flow Sheet"]
Every Per-Project Cash Flow Sheet will include a single list of line-items.
Each line-item details a project cost or project revenue.
Item Name: (format: text)
Payment Date: (format: short date mm/dd/yyyy)
Payment Value: (format: number)
In addition to the attributes attributed to each line-item, the Per-Project Cash Flow sheet also features a matrix of weeks throughout the duration of the project.
Each line-item inserts a value into the matrix of weeks based on its payment value and payment date.
Each column (week) is summed to determine total project costs and total project revenues by week for each week of the project’s life cycle.
Each cell in the matrix that is populated by a value should have conditional logic implemented such that if the Actually Paid value is YES, the cell is colored red; if the Actually Paid value is SCHEDULED, the cell is colored yellow; if the Actually Paid value is NO, the cell is not colored.
Consultant shall:
Propose options for linking line-item Payment Date in BOM file to corresponding Task End Dates in Project Schedule file.
Pay special attention to methods that would enable Project Managers to avoid manually linking each line-item in the BOM file to an analogous task end date in the Project Schedule – e.g. task name / line-item name matching, etc.
Propose options for linking Payment Date and Payment Amount in BOM file to the corresponding cells in Per-Project Cash Flow sheet.
Pay special attention to methods that would enable Project Managers to create and delete lines in the BOM .xlsx file without disrupting linkage to the Per-Project Cash Flow sheet.
Present to BoxPower a list of options for linking these 3 files, including pro/con presentation and explicit explanation of areas that would require manual upkeep by PMs to maintain links between sheets.
Implement linking and template modification as directed by BoxPower based on the presentation of options.
Perform 1 round of “troubleshooting & revision requests”