Finance : Variance by Account

[rlautt]

Itemizations are enabled in Java, not in .NET

[rlautt]

[rlautt]

[afs1jes]

Agreed.

Thanks

Jim Schwartz Services Manager

e: jschwartz@a3solutions.com mailto:jschwartz@a3solutions.com w: http://a3solutions.com http://a3solutions.com/ p: 817.705.5067

   https://www.linkedin.com/company/a3-solutions/    https://www.facebook.com/a3solutionsinc/ https://twitter.com/a3_solutions https://twitter.com/a3_solutions

On Jan 28, 2020, at 8:27 PM, rlautt notifications@github.com wrote:

https://user-images.githubusercontent.com/6751792/73320213-6e344880-41f3-11ea-9bd2-4514bf385576.png — You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub https://github.com/afs1jes/CHNCT-Migration/issues/24?email_source=notifications&email_token=ABQAYMSYX7DSJXGMP2JDOB3RADLP3A5CNFSM4KBXQMJ2YY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGOEKFUBLQ#issuecomment-579551406, or unsubscribe https://github.com/notifications/unsubscribe-auth/ABQAYMRRPSTQ2CN6EJ7WRUTRADLP3ANCNFSM4KBXQMJQ.

[afs1jes]

Itemizations disabled on all Variance reports pending bug fix.

[csoroka]

Email sent 4/26/2020 - items still outstanding I ran a Variance by Account for 01-200 today (2019 Revised Budget vs 2019 Actuals for Dec)

I was able to drill into the YTD budget amount for 5325 but I noticed there are some extra columns that we wish to hide to mirror what our current drill down looks like. Currently we only have the Title (Itemization), Description, and Monthly amounts. Also the amounts are formatted to currency in our current version, but are not in the new version.

When I drill into actuals the layout isn’t the same as the layout for Variance Report Explanation template, can you mirror that along with the changes in the email I sent at 8:39am this morning (EST).

Thank you, Chrissy

[afs1jes]

The new A3M processes request for Itemizations (formerly Line Item Details or LID) and Transactions without any setup... meaning that there is no longer a template sheet that you could use to customize the formatting. We can, however, apply VBA to your templates so that they re-format things so that they appear the way that your current models look. We'd like to avoid writing different code for each model but, if we can identify a standard for Itemizations and a standard for Transactions, we may be able to come to an agreement about what changes we can make to the default results.

That said, I think that I should give a little explanation about how each of those processes work in the three or four basic situations. First, let's talk about Transactions. Regardless of where you are in A3 Modeling, you can access (Actuals) Transactions for an intersection. The process always delivers the data in the same layout. You get a column for each dimension. These are collapsed using Excel's Grouping functionality, but they all appear in the page header area. You also get all the columns that are in the transaction table. In your case, that's "Date", "Journal", "Source & "Amount". All of the columns have Excel's Autofilter applied so that you can filter and/or sort the records however you'd like. Because these records can not be updated from the resulting sheet, and because some versions of Excel require the sheet to be unprotected in order for Autofilter functions to work properly, the sheet is unprotected. Because the Amounts may be currency, headcount, square footage or any other non-monetary amount, the Amount column is formatted pretty basically. All of this happens the same whether you drilling to transactions from a report, an input model or from ad hoc. If the default is not acceptable, let's mock-up the changes that you'd like to see (I think that we may have already done this in one of the other templates) so that we can apply the same code everywhere and, if at all possible, let's see if we can come up with an acceptable solution that we could possibly incorporate into the base product rather than using VBA at all. For instance, I'm an advocate of formatting the amount data using Excel's Accounting (comma) formatting with 2 decimals. That would return something like this (62,472.17) instead of the current default of -62472.17. I think that I could also advocate for protecting the sheet (using the "Allow Autofilter when protected" option (pending testing). What do you see as the absolutely required formatting changes to the Transactions sheets?

I'll start another comment to talk about Itemizations.

Thanks

Jim

[afs1jes]

One more thing re: Transactions formatting... while we can run VBA to revise formatting of transactions when called from a report or an input model, we do not have the ability to call code to reformat the result when called from ad hoc. I mention this because, if we elect to use VBA to modify the results, we would be creating a situation where the results from ad hoc will look different from the results from an A3 model or report.

[afs1jes]

Itemizations....

Itemizations are also now "template-less", or no setup required in the new A3M. Like the Transactions, there are columns for each of the dimensions giving you more details about what is included in the results. For instance, you might like to know which month a transaction is associated with when you drill from a Qtr or YTD. These columns are collapsed in the default formatting of Itemizations but, unlike Transactions, there is no Excel Grouping button available unhide those columns presently (something that I'm asking to be modified in the default results). Beyond those fields, you should see columns labeled Itemization and Description and then there are columns for the periods. Here's where it starts getting a little tricky... If you're in an input model and ask for Itemizations, you will get columns that match those in the input model, typically Jan:Dec plus Q1:Q4 and a Full Year Columns. for the non-actuals columns, the formatting and formulas will be inherited from the originating (Input) sheet. Columns that are marked as Actuals, will get a standard protected and slightly faint gray font. The sheet is protected, with only non-Actual periods and the two descriptive text columns unlocked and available for edit. In a Report (or a non-write enabled dataset), the formatting is very similar, but nothing will be unlocked. Here too, you will only get columns that are present in the report, so if you skip Qtrs in the report, those will not be in the Itemization sheet you get when drilling. Some reports, typically something like a variance report where you don't have more than one column associated with a year / scenario, can be told to return the entire record for the transactions. In those cases, if your Itemization table has months, Qtrs and Full Year, you will get all of those in the itemization results sheet. If you ask for itemizations from ad hoc, you will get, like that last example, all the columns available... the full year view. Whether from a report oe ad hoc, the Itemizations will display the columns for the member names and the amounts are formatted to appear as -12345.67.

Note that the "Reconciliation" area appears above the amount columns only in input models, and only when the model is using Actuals Protection.

Like the Transactions sheets, we can execute VBA to modify the results, but I would similarly like to see if we can come to an acceptable solution that avoids the use of VBA, if possible. I, for one, see several instances where we could provide more standardized results (like using Grouping on the dimension columns everywhere, protecting sheets while allowing Autofilter to function, placing the Recon area above columns in reports and I'm sure there are other items).

And like Transactions, the VBA is not available to be called when drilling ad hoc.

Let's see if we can zero in on something that requires as little VBA as possible while also satisfying the needs of the users executing either of these operations, regardless of where they originate.

If you have a little time on Friday or Monday, let's see if we can connect on a Zoom call to discuss these items.

Thanks

Jim

[csoroka]

Christina Soroka has created a secure e-mail message for you at:

https://ssl.datamotion.com/r.aspx?b=16&eh=QoeC7s30P0ifyqa8iTrsgg&cb=chnct1

To access your message, simply follow these steps:

1. Click on the above link.
2. You will be prompted to create a password to protect your account.
3. Access your Inbox to view your message.

If you are unable to open the link, please make sure that your firewall or your company's security settings are not blocking the link.

Additional Help: • Questions relating to the content of the e-mail should be directed to the sender or sending organization. • Questions relating to reading this secure message can be sent to helpdesk@chnct.org.

---

Message Details: Subject: RE: [afs1jes/CHNCT-Migration] Finance : Variance by Account (#24) From: csoroka@chnct.org To: reply@reply.github.com Created: 4/24/2020 8:21:58 AM (UTC-04:00:00) Message Expires: 7/24/2020 8:21:58 AM (UTC-04:00:00)

Copyright (c) 1996-2020 DataMotion, Inc.

[csoroka]

Christina Soroka has created a secure e-mail message for you at:

https://ssl.datamotion.com/r.aspx?b=16&eh=bDC0DWEreEyd3Uuqvsiv5Q&cb=chnct1

To access your message, simply follow these steps:

1. Click on the above link.
2. You will be prompted to create a password to protect your account.
3. Access your Inbox to view your message.

If you are unable to open the link, please make sure that your firewall or your company's security settings are not blocking the link.

Additional Help: • Questions relating to the content of the e-mail should be directed to the sender or sending organization. • Questions relating to reading this secure message can be sent to helpdesk@chnct.org.

---

Message Details: Subject: RE: [afs1jes/CHNCT-Migration] Finance : Variance by Account (#24) From: csoroka@chnct.org To: reply@reply.github.com Created: 4/24/2020 8:23:43 AM (UTC-04:00:00) Message Expires: 7/24/2020 8:23:43 AM (UTC-04:00:00)

Copyright (c) 1996-2020 DataMotion, Inc.

[afs1jes]

e-mail copied from data motion secure mail: It is very rare that we run reports from ad hoc. But if we do create a report from ad hoc can we change the formatting after it is run?

-Chrissy

---

As of today, the sheet is not protected, so yes, you could manually modify the output.

Assuming that we try to standardize the output formatting in all situations, one of the options is to lock the results sheets. If the resulting sheet is locked, then you won t be able to modify it without unprotecting it. You know the password, but I don't think that your user community does. So I think the answer to your question is: Yes... you could, but also No.. your users could not.

-Jim

[afs1jes]

e-mail copied from data motion secure mail: Drilling down into budget via any report, except for the budget input template, should look the same as you had for the Variance Explanations Input template. However, I just went into the “Variance Explanations Input” template, to provide you a screen print, and the drill down has changed and has no data in it whatsoever. The same for the drill down of actuals as well. But I would need to see it in its prior format to verify.

I understand what you are saying about the formatting. We would want the FTE with the two decimals points as well. The only other data that we may not want formatted in that way is membership. We would want the comma but not the decimal points, if possible.

Can you update the “Variance Explanations Input” template as it was and we can review that as the example to use for other reporting?

-Chrissy

---

I'll see if I can check on why you didn't find out why you didn't get any results on your drill from to Transactions from the Variance Explanations model.... I assume that you were drilling to Itemizations and not to Transactions.

Regarding formatting the amount differently depending on what the account is, I'm not sure that's really going to be feasible. We'd need to get and maintain a list of the accounts and how you want each one of them formatting. That's certainly possible using custom VBA, but there would be no way to apply it to Itemizations (or Transactions) pulled from ad hoc mode.

Rather than create an individual VBA routine for each template and try to manage them all, I would prefer that we complete the steps in this order: 1) Identify an acceptable formatting solution that will work for all of your Itemizations and / or Transactions. 2) Identify which of those, if any, A3 is willing to incorporate into the base process. 3) Create the VBA module, if necessary, to address any outstanding requirements not addressed in step 2. 4) Apply that VBA module to all the templates so that they all work the same way.

Does that sound acceptable?

-Jim

PS - if you reply to these GiHub issues from your e-mail system, please mark the title as Unsecure. Alternatively, you could post your replies directly in the issue on the GiHub page.

[csoroka]

Christina Soroka has created a secure e-mail message for you at:

https://ssl.datamotion.com/r.aspx?b=16&eh=vlusUSPO_UqyEEFgGcgYJA&cb=chnct1

To access your message, simply follow these steps:

1. Click on the above link.
2. You will be prompted to create a password to protect your account.
3. Access your Inbox to view your message.

If you are unable to open the link, please make sure that your firewall or your company's security settings are not blocking the link.

Additional Help: • Questions relating to the content of the e-mail should be directed to the sender or sending organization. • Questions relating to reading this secure message can be sent to helpdesk@chnct.org.

---

Message Details: Subject: RE: [afs1jes/CHNCT-Migration] Finance : Variance by Account (#24) From: csoroka@chnct.org To: reply@reply.github.com Created: 4/24/2020 10:32:38 AM (UTC-04:00:00) Message Expires: 7/24/2020 10:32:38 AM (UTC-04:00:00)

Copyright (c) 1996-2020 DataMotion, Inc.

[csoroka]

I ran this report for LOB 01 Department 200 2019 Revised Budget vs 2019 Actuals for Total Administrative Expenses This issue that is still occuring: When drilling down into Revised Budget the layout should look the same as the Variance by Account Explaination Template and it does not, there are extra columns and there are no actual rows and Jan-Jun seem to be showing data. If you were to total the YTD columns in the itemization tab of one of the accounts and compare to the Budget total on the data tab for that account, you would see they don't tie.

[csoroka]

In addition, I noticed that Actuals transaction drill down is displaying a Period: Feb as the header even thought I ran the report as of July YTD. Not sure if we should hide the period field as it might cause confusion.

[afs1jes]

_transactions sheet modified the header so that the Period displays the queried period rather than the period of the first record returned.

[csoroka]

What is the status on this?:

When drilling down into Revised Budget the layout should look the same as the Variance by Account Explanation Template and it does not, there are extra columns and there are no actual rows and Jan-Jun seem to be showing data. If you were to total the YTD columns in the itemization tab of one of the accounts and compare to the Budget total on the data tab for that account, you would see they don't tie.

[csoroka]

TEST email for Dina

[afs1jes]

it's next on my list

[afs1jes]

The extra columns present the member names of the intersection associated with the itemization record. In the old system, they were not needed because you could not drill to itemizations unless you were at leaf level. In the new system, you can drill to transactions from rollups, creating the situation where the records could be associated with multiple members from any / each dimension. The new layout allows the user to filter and sort the records on any of the dimensions and is part of the default presentation.

The request for column header information that presents the monthly Itemization totals, actuals and variance between the two is not part of the default functionality. Also, the Variance report by account in the current production environment does not include that information. I'll need Stuart and Dina to weigh-in on whether this change is within the scope fo the migration. (screen-shots attached).

Note that neither version of the report displays monthly actuals in the itemizations sheets because neither report includes all months of the itemization time horizon. The Variance Explanation report is able to include that information because that report includes all months in the report (non-selected months are hidden in the variance sheet).

[csoroka]

I wasn't aware that we could now see rollup details now, which we could not in the previous system. I will discuss with Dina.

I'm concerned about the Revised Budgets LIDs because there are no budget itemizations rows for Jan-Jun as we are utilizing the actual dollars as our budget amounts for those months - how will the itemization data look when you drill into those rows for Revised Budget? Look at 2020 Revised for an example.

[csoroka]

It looks like the original version of A-3 never had the actuals row. And the fact that we can see all Cost Centers on this report to filter we understand why that isn't possible. This report should be all set to move to "client accepted".

[kclark-a3]

• Template itemizations and transactions

[csoroka]

I'm going to assume the same issues with the Variance Report Explanations is going to hold true for this report as well. So in the effort of saving my time, I would like to hold off reviewing this until you have fixed the Var Expl issues and ensured that any of the same issues have been corrected in this template, as well, before I review.

[kclark-a3]

Variance by Account is ready for testing by CHN.

Page/print setup - does not include blank page for 'Data' sheet and transactions style sheet. Itemizations and transactions can be drilled into LID - cells are grey instead of white because white background indicates that cells are editable.

[csoroka]

Ran Revised vs Actuals for 2021 (01-610) the revised column came up blan Variance Report_for 2021 Revised Budget Dec 01 - Corporate 610 - IS Technical Svcs.pdf k

[csoroka]

Hello - I ran this report for 01-610 2021 Revised Budget Dec vs 2021 Actuals Dec (net income / unadjusted) When I tried to drill into the YTD 2021 Revised Budget column I got the message: "Itemizations are not available for this column"

When I drill into GL transaction I'm unable to sort by Posting Comment (I got a message that says: The cell or chart you're trying to change is on a protected sheet) I thought Jim mentioned last year that the sorting could happen without unprotecting the sheet as we don't give our users the password to unprotect. Only Dina and myself know that password.

Thank you, Chrissy

[csoroka]

Any update on this?

[kclark-a3]

Updates:

• Drilling capability is now on for YTD column
• Sorting capability on protected sheet is in progress

[kclark-a3]

Update: Sorting can now be done for transactions style sheet without unprotecting sheet.

[csoroka]

Looks good thank you

[csoroka]

I ran the other companies today -FAL -HLD -FDN and I was not able to see the itemized data for any of the 2022 Org Budget nor the 2021 Revised Budget (those were the only two I tried) I assume this might just be a copy data issue for the other companies - so I left the "Trend by Account" under the accepted by CHN and will test it again once all the LID data is copied over. From what I can tell the GL transactions is visible.

Thank you, Chrissy

[csoroka]

Unable to sort LID by any of the filters. Also I noticed that the LOB & Dept columns are missing from the LID drill down (they were present during the itemization testing back in March).

[kclark-a3]

Update

• LOB and dept are now displayed.
• unable to filter in itemizations style sheet is not available in Java system and will not be available in .Net for security reasons.

[csoroka]

Thank you for updating with LOB & Dept We understand we will not be able to sort we appreciate you looking into it

[csoroka]

I ran this report for December 2020 Rev Budget vs 2020 Actuals (01 Foundation Corp & 100 Corporate / General) when I drilled into the YTD 5325 for 2020 Revised Budget for 5325 LID the YTD totals do not match.

[kclark-a3]

I ran this report in both systems with selections December 2020 revised budget vs actuals, 01 Foundation Corp and 100 Corporate / General and the reports are blank.

Variance by Account for_2020 Revised Budget_Dec_01 - Foundation Corporate100 - Corporate General.xlsx VarianceReport_for_100CorporateGeneral_01FoundationCorporate_2020RevisedBudget_Dec.xlsx

[csoroka]

I think the account roll up that you may have run this with was for CHN "Net Income" instead of "FDN Net Income" - can you please try to run again?

[kclark-a3]

I ran this report in both systems with selections December 2020 revised budget vs actuals, 01 Foundation Corp, 100 Corporate / General and FDN Net Income. The LID ties to Data sheet for both reports.

Variance by Account for_2020 Revised Budget_Dec_01 - Foundation Corporate100 - Corporate General.xlsx VarianceReport_for_100CorporateGeneral_01FoundationCorporate_2020RevisedBudget_Dec.xlsx

[csoroka]

Ran this report for 2020 Revised Budget vs 2020 Actuals for all four companies (Net Income) I was unable to pull up any transactions for FDN, HLD & FAL I pull up transactions for CHN 5150 but the December monthly total transactions doesn't tie to the data tab. I'm concerned because I figured since all the transactions were reconciled by A3 that I wouldn't need to run all scenarios.

[kclark-a3]

Transactions now ties to the data sheet for all companies.

Variance by Account for_2020 Revised Budget_Dec_Total CHN_Total CHNCT.xlsx Variance by Account for_2020 Revised Budget_Dec_Total FDN_Total CHNCT Foundation.xlsx Variance by Account for_2020 Revised Budget_Dec_Total FAL_Total Founders.xlsx Variance by Account for_2020 Revised Budget_Dec_Total HLD_Total Holdings.xlsx

[csoroka]

Looks good