In the last post, we started using the CALCULATE() function to filter out some of the values that we didn’t want on our totals. This was done in order to get a pivot table that didn’t need to use the …
FILTER and CALCULATE – Removing pre-calculated totals.
In the last post, we started using the CALCULATE() function to filter out some of the values that we didn’t want on our totals. This was done in order to get a pivot table that didn’t need to use the …
Sum of a row using CALCULATE()
In the last post, we created a custom field in the Excel data model; however, we still needed to use Excel formulas to calculate the percentage of a row. In this example, we will be using a simple CALCULATE() function …
Custom Field for Quarterly Data in the Data Model
As part of the Quarterly Report case study, we built a report using the data model, but there were still some instances where we used Excel formulas. In this post, we will be creating a custom field so that there …
Quarterly Report – Using the Data Model
In this post, along prior two posts, I discussed a hypothetical case where data has been provided and your job (as the analyst) will be to create a quarterly report detailing sales of 2018 new vehicles in Canada. This post …
Quarterly Report – Pivot Table
In this post, along with the next few posts, I will be discussing a hypothetical case where data has been provided and your job (as the analyst) will be to create a quarterly report detailing sales of 2018 new vehicles …
Quarterly Report – Using SUMIFS()
In this post, along with the next few posts, I will be discussing a hypothetical case where data has been provided and your job (as the analyst) will be to create a quarterly report detailing sales of 2018 new vehicles …
Creating a Basic Loan Amortization Schedule Without Code
An amortization schedule contains details with respect to a loan, including the following: The principal amount of the loan. The interest on the loan (typically reflected in annual terms). The length of the loan (typically reflected in years; except in …
Using PMT() to calculate installments
One of the most commonly used financial functions is the payment function – the PMT() function. This formula replicates the mathematical equation below: Instead of using the mathematical equation, the Excel formula uses a much simpler list of arguments. =PMT(interest …
SUBTOTAL() vs. SUM()
As an accountant, one of the frequent tasks that needs to be accomplished is the summing up of results; this can be anything from summing up ledger balances to a full income statement or balance sheet. We normally use a …