Library Overview Reference Products Images Sales Stock Departments Formal Stocktake Restrictions Customer/Account Day Of Week Time Of Day Weather PinboardArray Payments by Department

Excel Payments by Department

This PinboardArray function returns a matrix of payments and departments. It allocates each payment type to individual salelines using your order of application. It correctly handles multiple different departments on a single sale AND where that sale was paid using mutiple different tender types. For example it can allocate "a dress and a chocolate bar; paid with cash, eftpos, and gift voucher"

It is also able to report tax exclusive amounts rather than the net payment amount

Internal Logic

Select all payments recorded during a time period. Payments can differ from sales for several reasons, such as paying down an account, making payment on layby sales and so on.
Select all related salelines (items actually purchased) for those sales
Derive the department for each saleline
If reporting "extax" prices, determine for each saleline the total without tax
For each payment in turn, top to bottom of list
Then for each department in the matching sale, processing left to right in department order
Allocate as much of the current payment to that department as possible.

Examples

=PinboardArray("list payments by department")

=PinboardArray("list payments by department3, amount extax, remove zero payments")

=PinboardArray("list payments by department",,,,"payment order=cash-change+rounding,eftpos,Direct Bank:directdebt+wise,account,voucher,?,AMEX,*")

=PinboardArray("list payments by department",,,,"payment order=cash,eftpos,account,voucher,?,AMEX,*","department order=clothing,food,furniture,Misc,*")

Worked Example

Sale #1

Dept A $4
Dept B $5
Dept C $6
Paid cash $15

The request =PinboardArray("list payments by department") for Sale #1 will return

	Dept A	Dept B	Dept C
Cash	$4	$5	$6

Sale #2

Dept A $22
Paid cash $10
Paid Eftpos $12

The request =PinboardArray("list payments by department") for Sale #2 will return

	Dept A
Cash	$10
Eftpos	$12

Sale #3

Dept A $20
Dept B $30
Paid cash $24
Paid Eftpos $26

The request =PinboardArray("list payments by department") for Sale #3 has a problem in definition. Was Dept A paid by Cash or Eftpos?

To solve this problem, the formula uses the order payments and departments are allocated to the output table to select the order. It starts at the top left and moves across and down; allocating amounts as it goes.

You can influence the order using "payment order=" and "department order=" in your request. If you do not explicitly specify the order, a default order is chosen by the server.

=PinboardArray("list payments by department",,,,"payment order=cash,eftpos", "department order=a,b")

	Dept A	Dept B
Cash	$20	$4
Eftpos	$0	$26

=PinboardArray("list payments by department",,,,"payment order=cash,eftpos", "department order=b,a")

	Dept B	Dept A
Cash	$24	$0
Eftpos	$6	$20

=PinboardArray("list payments by department",,,,"payment order=eftpos,cash", "department order=b,a")

	Dept B	Dept A
Eftpos	$26	$0
Cash	$4	$20

=PinboardArray("list payments by department",,,,"payment order=eftpos,cash", "department order=a,b")

	Dept A	Dept B
Eftpos	$20	$6
Cash	$0	$24