Monthly Archives: January 2022

Excel

COUNTA in accounting

You have a balance of 5693 in a cost center. This balance should be divided equally to multiple cost centers. You create an Excel and enter that into system to have a manual journal entry.

To make the process bit smoother we can use COUNTA function.

Presuming, the balance in the cost center CCM is positive and the cc should be zeroed by dividing the plus to several cost centers. One way is to increase costs to CCM in debit and post negative costs in credit for the multiple cost centers.

The sentence in D3 is =ROUND($C$2/(COUNTA($A$1:$A$24)-1);2).

The balance in CCM is divided by number of cost centers in A-column minus one (CCM). This is done if you add lines for dividing the balance for several other cost centers. The division is rounded by two decimals.

New cost centers are added but the sentence is not changed.

Before you post this journal entry, you want to check the roundings. Now debit is 5693 but credit 5693,04. The journal entry would not be posted as debit does not equal to credit. You can manually add the numbers to have equal debit and credit. A simple way to adjust the debit balance if you want all the receiving cost centers to have the same balance. In that case, cost center CCM would have a balance of -0,04. If the cost center may not have negative value, then you need to adjust credit balances.

There is one process question to be notified. As we post credit balance for expense accounts, that will create a negative total if there the balance is in the cost center lower that 632,56. For example, in cost center CC1 there is no postings for the account 5000. Then credit posting makes the balance for CC1/5000 negative, which is increasing the balance in CC1. If you take a report for CC1, the report might look unusual to show negative costs.

Another way is to create percentages instead of absolute numbers for each line.

The percentages must equal to 1, like the value in F1.

The sentence in D3 is =ROUND(F3*$C$2;2).

If you want to adjust percentages and divide the balance unequally to cost centers, the formulas can be overwritten. This was done in F5 and F9.

COUNTA might not look like a breakthrough in accounting, it can still fasten the process when creating a long journal entry. Once the model is made, it can hopefully be used with several journal entries.

Business Intelligence, Power BI

Cubemember and cubevalue

You have simple sales data of sales data and product data. Sales data includes following fields: ID, product, region, rep and sales. The product data includes these fields: product, costs, price and size. The sales data is indexed by id, a separate running number. Product data is indexed by product which needs to be unique.

We need to have a sales report per region and per product size ?

The data was stored in MS Access.

The product master data.

A part of the sales transaction data.

A data model is created in Excel.

Let’s create also a measure to sum up the sales.

Instead of creating a Power Pivot on top of data model, we can still extract data with CUBEMEMBER and CUBEVALUE functions.

First, make a two-dimensional matrix. In horizontal axis we have different regions, vertically we have product size. We should fill the table with correct values. Like how many pieces of small product were sold in north region ?

Let’s start in the cell C3. We want to extract the region attributes. The sentence in C3 is =CUBEMEMBER(“ThisWorkbookDataModel”;”[Sales].[Region].[North]”) .

First, we define the data model. It is the data model under the Excel worksheet. Write “ThisWorkbookDataModel”. Secondly, we define the table. We have just two tables in our data model. We need to select the sales table as there we have regions north and south. After the table definition, select the field region and lastly, the value for region, north.

Repeat the same in D3, but now the last argument is south instead of north.

The vertical axis and the size values are created with the same logic.

Size dimension is in prod table. The arguments are datamodel, table, column and column value.

As we want to report the number of pieces sold, we have created the S_sale measure. The is the measure we would use in Power Pivot, and it is the measure we also use with cube functions.

The sentence in C2 is =CUBEMEMBER(“ThisWorkbookDataModel”;”[Measures].[S_sale]”) . The first argument with CUBEMEMBER is the data model. As we use the data model under Excel workbook, just select of type “ThisWorkbookDataModel”. The second argument is the measure itself. Select of write “[Measures], press dot, and you will have list of measures. Select the S_sale.

Now we can extract the values from data model.

For the CUBEVALUE we need show the data model, the measure and the dimensions. The data model is “ThisWorkbookDataModel”. The absolute and semi absolute references are just to make it easy to copy the formula. Show the product size, B4, region, C3, and the measure, C2.

The extracted values.

Still, we will create same report in Power BI.

Results are parallel.

Also a query was  made in Access.

SELECT sales.region, prod.size, SUM(sales.sales) AS Sale

FROM sales INNER JOIN prod ON sales.product = prod.product

GROUP BY sales.region, prod.size;

This is the SQL script.

The CUBEMEMBER and CUBEVALUE functions can withdraw the data from the Excel data model. If you don’t want to build Power Pivot or Power BI, you can simply use cube-functions. Of course, the result is not so graphical as with Power BI.