jcountry22
New Member
- Joined
- Aug 15, 2014
- Messages
- 36
My company operates on a April-March Fiscal Year.
One Branch (ABC) operates on a standard Fiscal Year for Jan-Dec.
When reporting financials, actual is just the current month.
However, I need assistance with a formula that updates Year-to-Date figures for branch ABC.
My entire workbook operates on 2 cells changing to update financial information. The only 2 cells that change to update data are:
Sheet1 - Cell R4 (I manually change to current month)
Sheet1 - Cell R5 (I manually change to current year)
The below data cells are made by formulas based on the data within Cell R4 & R5.
Sheet1 - Cell R1 = Fiscal Year
Sheet1 - Cell R2 = Budget Fiscal Year
Sheet1 - Cell R3 = Fiscal Period
Are you able to input a formula within the @MTDNETBUD function, so that by changing Sheet1 - cells R4 & R5, the branch (ABC) YTD rolls forward? If so, how would this formula be constructed?
If I need to explain in a different manner please let me know. Hopefully I'm explaining this clearly enough.
=@MTDNETBUD()
Company Code
Budget Code
Fiscal Year
Fiscal Period
Account
Segment01
Segment02
Segment03
Segment04
The below table is an example of the data needed to present branch ABC's financials at a given Fiscal Period.
One Branch (ABC) operates on a standard Fiscal Year for Jan-Dec.
When reporting financials, actual is just the current month.
However, I need assistance with a formula that updates Year-to-Date figures for branch ABC.
My entire workbook operates on 2 cells changing to update financial information. The only 2 cells that change to update data are:
Sheet1 - Cell R4 (I manually change to current month)
Sheet1 - Cell R5 (I manually change to current year)
The below data cells are made by formulas based on the data within Cell R4 & R5.
Sheet1 - Cell R1 = Fiscal Year
Sheet1 - Cell R2 = Budget Fiscal Year
Sheet1 - Cell R3 = Fiscal Period
Are you able to input a formula within the @MTDNETBUD function, so that by changing Sheet1 - cells R4 & R5, the branch (ABC) YTD rolls forward? If so, how would this formula be constructed?
If I need to explain in a different manner please let me know. Hopefully I'm explaining this clearly enough.
=@MTDNETBUD()
Company Code
Budget Code
Fiscal Year
Fiscal Period
Account
Segment01
Segment02
Segment03
Segment04
The below table is an example of the data needed to present branch ABC's financials at a given Fiscal Period.
| Month | Fiscal Period | Fiscal Year | Act Year | Budget FY |
| January | 10 | 2019 | 2020 | FY2020 |
| February | 11 | 2019 | 2020 | FY2020 |
| March | 12 | 2019 | 2020 | FY2020 |
| April | 01 | 2020 | 2020 | FY2021 |
| May | 02 | 2020 | 2020 | FY2021 |
| June | 03 | 2020 | 2020 | FY2021 |
| July | 04 | 2020 | 2020 | FY2021 |
| August | 05 | 2020 | 2020 | FY2021 |
| September | 06 | 2020 | 2020 | FY2021 |
| October | 07 | 2020 | 2020 | FY2021 |
| November | 08 | 2020 | 2020 | FY2021 |
| December | 09 | 2020 | 2020 | FY2021 |
