In this case, it is performing a calculation similar to a SUMIFS function, but it is doing something that the SUMIFS can’t handle. The SUMPRODUCT function is Excel’s most flexible function. The divide by 26" converts the annual figure into a fortnight amount. Once created, the names can be used in formulas.Īs structured references have been used in the “Refers to” box, these four names automatically expand as new rows are added to the table. The other names we need to create are Total (H2:H18), Start (I2:I18) and End (J2:J18). We need to repeat the above process for three more names by selecting ranges in the other columns. Note the structured reference used in the “Refers to” box. In the Name box enter Dept and click OK (see Figure 3). Press Ctrl + Alt + F3 (or click the Formulas tab and click the Define Name icon) to open the New Name dialog. Using the Department column as an example, we will select the range B2:B18. The quick way to do that is to click a cell in that column and press Ctrl + Space.
We first select the column of data that we want to name. We need to create four names to use in our formulas. They will have the advantages of the formatted table names in that they will automatically expand as the table expands, but they won’t have the drawbacks mentioned above. To get around these issues we can use range names to define the columns that we need to use from the table. This is because of the way in which the structured references are handled by Excel. Copying the formulas across columns can also be problematic. If we use the names built into the formatted table (called structured references), the formulas become very long. Since this is a forecast, that level of accuracy is considered acceptable. It is important to note that the formulas used may not provide an accurate wage calculation for the first fortnightly payment of a new employee or the last fortnight payment of a terminating employee. A date in the Start Date column will determine the first fortnightly payment. Formatted tables were covered in the August 2018 edition of INTHEBLACK.Ī date in the End Date column determines the last fortnight that the employee will be paid. The formatted table in Figure 1 has been named WagesForecast via the Design ribbon tab that is displayed when the table is selected. In future, this forecast structure could be used as the budget. The wages assumptions used in Figure 1 are shown in column M in Figure 2, which also includes a fortnightly breakdown of the wages based on the table from Figure 1. I have added and populated the Start Date and End Date columns to enable forecasting. In Figure 1, columns A to H were used to create the initial wages budget. The example relates to wages, but could be applied to any situation where you have costs that start and stop. This article is based on a question from a CPA Australia member who wanted to convert an existing budget structure into a forecast structure.
0 kommentar(er)
