thbutterflycollector_
New Member
- Joined
- Oct 29, 2015
- Messages
- 42
- Office Version
- 365
- Platform
- Windows
Hi,
I'm trying to compare budget against actual against several cost codes. Part of the analysis requires me to look at hired equipment and I am required to compare spends vs budgets in weekly periods. I am trying (based on the anticipated or known on and off hire dates) to "spread" the liability up to a certain period end:
[TABLE="width: 0"]
<tbody>[TR]
[TD]1
[/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[TD]F
[/TD]
[TD]G
[/TD]
[TD]H
[/TD]
[TD]I
[/TD]
[TD]J
[/TD]
[TD]K
[/TD]
[TD]L
[/TD]
[TD]M
[/TD]
[TD]N
[/TD]
[TD]O
[/TD]
[TD]P
[/TD]
[TD]Q
[/TD]
[TD]R
[/TD]
[TD]S
[/TD]
[TD]T
[/TD]
[TD]U
[/TD]
[TD]V
[/TD]
[TD]W
[/TD]
[TD]X
[/TD]
[TD]Y
[/TD]
[TD]Z
[/TD]
[TD]AA
[/TD]
[TD]AB
[/TD]
[TD]AC
[/TD]
[TD]AD
[/TD]
[TD]AE
[/TD]
[TD]AF
[/TD]
[TD]AG
[/TD]
[TD]AH
[/TD]
[TD]AI
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]PERIOD END
[/TD]
[TD]28/02/2018
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]WEEK COMMENCING
[/TD]
[TD]01/01
[/TD]
[TD]08/01
[/TD]
[TD]15/01
[/TD]
[TD]22/01
[/TD]
[TD]29/01
[/TD]
[TD]05/02
[/TD]
[TD]12/02
[/TD]
[TD]19/02
[/TD]
[TD]26/02
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]ON HIRE DATE
[/TD]
[TD]ANTICIPATED HIRE PERIOD
(WEEKS)
[/TD]
[TD]ANTICIPATED OFF-HIRE PERIOD
[/TD]
[TD]STATUS (ON/OFF)
[/TD]
[TD]OFF HIRE DATE
[/TD]
[TD]REQ
[/TD]
[TD]BASIS OF HIRE
[/TD]
[TD]DELIVERY QUANTITY
[/TD]
[TD]UNIT
[/TD]
[TD]RATE
[/TD]
[TD]COST CODE
[/TD]
[TD]TOTAL
[/TD]
[TD]PERIOD
[/TD]
[TD]LIABILITY
[/TD]
[TD]INVOICED
[/TD]
[TD]ACCRUED
[/TD]
[TD]WEEK ENDING
[/TD]
[TD]07/01
[/TD]
[TD]14/01
[/TD]
[TD]21/01
[/TD]
[TD]28/01
[/TD]
[TD]04/02
[/TD]
[TD]11/02
[/TD]
[TD]18/02
[/TD]
[TD]25/02
[/TD]
[TD]04/03
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]01/02/18
[/TD]
[TD]6
[/TD]
[TD]15/03/18
[/TD]
[TD][/TD]
[TD][/TD]
[TD]Van
[/TD]
[TD]5 Days
[/TD]
[TD][/TD]
[TD]1
[/TD]
[TD]155.00
[/TD]
[TD][/TD]
[TD][/TD]
[TD]4
[/TD]
[TD]620.00
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]?
[/TD]
[TD]?
[/TD]
[TD]?
[/TD]
[TD]?
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]04/01/18
[/TD]
[TD]6
[/TD]
[TD][/TD]
[TD]OFF
[/TD]
[TD]09/02/18
[/TD]
[TD]Dumper Truck
[/TD]
[TD]5 Days
[/TD]
[TD][/TD]
[TD]1
[/TD]
[TD]575.00
[/TD]
[TD][/TD]
[TD][/TD]
[TD]5.40
[/TD]
[TD]3,285.00
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]?
[/TD]
[TD]?
[/TD]
[TD]?
[/TD]
[TD]?
[/TD]
[TD]?
[/TD]
[TD]?
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]07/03/18
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Blades
[/TD]
[TD]Purchased
[/TD]
[TD][/TD]
[TD]50
[/TD]
[TD]1.00
[/TD]
[TD][/TD]
[TD][/TD]
[TD]1
[/TD]
[TD]50.00
[/TD]
[TD]50.00
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Can anyone assist in guiding me on how to spread the costs listed in Column N between Columns R and Z? Some consumable items (shown in row 3) are also included and purchased as a one-off which also require factoring in.
Is this possible without a VBA? Hopefully someone can assist me.
Thanks
I'm trying to compare budget against actual against several cost codes. Part of the analysis requires me to look at hired equipment and I am required to compare spends vs budgets in weekly periods. I am trying (based on the anticipated or known on and off hire dates) to "spread" the liability up to a certain period end:
[TABLE="width: 0"]
<tbody>[TR]
[TD]1
[/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[TD]F
[/TD]
[TD]G
[/TD]
[TD]H
[/TD]
[TD]I
[/TD]
[TD]J
[/TD]
[TD]K
[/TD]
[TD]L
[/TD]
[TD]M
[/TD]
[TD]N
[/TD]
[TD]O
[/TD]
[TD]P
[/TD]
[TD]Q
[/TD]
[TD]R
[/TD]
[TD]S
[/TD]
[TD]T
[/TD]
[TD]U
[/TD]
[TD]V
[/TD]
[TD]W
[/TD]
[TD]X
[/TD]
[TD]Y
[/TD]
[TD]Z
[/TD]
[TD]AA
[/TD]
[TD]AB
[/TD]
[TD]AC
[/TD]
[TD]AD
[/TD]
[TD]AE
[/TD]
[TD]AF
[/TD]
[TD]AG
[/TD]
[TD]AH
[/TD]
[TD]AI
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]PERIOD END
[/TD]
[TD]28/02/2018
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]WEEK COMMENCING
[/TD]
[TD]01/01
[/TD]
[TD]08/01
[/TD]
[TD]15/01
[/TD]
[TD]22/01
[/TD]
[TD]29/01
[/TD]
[TD]05/02
[/TD]
[TD]12/02
[/TD]
[TD]19/02
[/TD]
[TD]26/02
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]ON HIRE DATE
[/TD]
[TD]ANTICIPATED HIRE PERIOD
(WEEKS)
[/TD]
[TD]ANTICIPATED OFF-HIRE PERIOD
[/TD]
[TD]STATUS (ON/OFF)
[/TD]
[TD]OFF HIRE DATE
[/TD]
[TD]REQ
[/TD]
[TD]BASIS OF HIRE
[/TD]
[TD]DELIVERY QUANTITY
[/TD]
[TD]UNIT
[/TD]
[TD]RATE
[/TD]
[TD]COST CODE
[/TD]
[TD]TOTAL
[/TD]
[TD]PERIOD
[/TD]
[TD]LIABILITY
[/TD]
[TD]INVOICED
[/TD]
[TD]ACCRUED
[/TD]
[TD]WEEK ENDING
[/TD]
[TD]07/01
[/TD]
[TD]14/01
[/TD]
[TD]21/01
[/TD]
[TD]28/01
[/TD]
[TD]04/02
[/TD]
[TD]11/02
[/TD]
[TD]18/02
[/TD]
[TD]25/02
[/TD]
[TD]04/03
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]01/02/18
[/TD]
[TD]6
[/TD]
[TD]15/03/18
[/TD]
[TD][/TD]
[TD][/TD]
[TD]Van
[/TD]
[TD]5 Days
[/TD]
[TD][/TD]
[TD]1
[/TD]
[TD]155.00
[/TD]
[TD][/TD]
[TD][/TD]
[TD]4
[/TD]
[TD]620.00
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]?
[/TD]
[TD]?
[/TD]
[TD]?
[/TD]
[TD]?
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]04/01/18
[/TD]
[TD]6
[/TD]
[TD][/TD]
[TD]OFF
[/TD]
[TD]09/02/18
[/TD]
[TD]Dumper Truck
[/TD]
[TD]5 Days
[/TD]
[TD][/TD]
[TD]1
[/TD]
[TD]575.00
[/TD]
[TD][/TD]
[TD][/TD]
[TD]5.40
[/TD]
[TD]3,285.00
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]?
[/TD]
[TD]?
[/TD]
[TD]?
[/TD]
[TD]?
[/TD]
[TD]?
[/TD]
[TD]?
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]07/03/18
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Blades
[/TD]
[TD]Purchased
[/TD]
[TD][/TD]
[TD]50
[/TD]
[TD]1.00
[/TD]
[TD][/TD]
[TD][/TD]
[TD]1
[/TD]
[TD]50.00
[/TD]
[TD]50.00
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Can anyone assist in guiding me on how to spread the costs listed in Column N between Columns R and Z? Some consumable items (shown in row 3) are also included and purchased as a one-off which also require factoring in.
Is this possible without a VBA? Hopefully someone can assist me.
Thanks