Using Accounting Analysis Pattern For Inventory


In the previous post,  Implementing the Accounting Analysis Pattern, I’ve used accounting analysis pattern to create a simple work orders processing software for a hypothetical motorcycle service station.   Most work orders processing will require motorcycle spare part’s replacements.   That is why service stations often have some spare part (replacement part) available in their warehouse.   I need to improve the design to track spare part inventory.   This is the requirement:

“The new design should records incoming spare part and its sales.   User should be able to display gross profit for specified spare part.   For the sake of simplicity, I will not introduce period in the new design (real life design usually allow daily, monthly, or yearly aggregation).   Sometimes user makes mistakes so the new design should allow user to revoke any recorded events while still display the correct value for inventory and gross profit.   The system should not forget any event’s adjustments.”

To handle incoming spare parts, I create a new subclass of AccountingEvent called IncomingSparepartEvent.   This event will be triggered by a purchase order.   Class IncomingSparepartInvoice represents the incoming invoice.  Not all incoming spare part events should be associated with an invoice, for example, stock adjustment will generate such event.

The IncomingSparepartEvent

The IncomingSparepartEvent

The source code for IncomingSparepartEvent.groovy is:

package domain

import ...

@DomainModel @Entity @Canonical
class IncomingSparepartEvent extends AccountingEvent {

    @NotNull @OneToOne(cascade=CascadeType.ALL)
    Sparepart sparepart

    @ManyToOne(cascade=CascadeType.MERGE)
    IncomingSparepartInvoice invoice

    @NotNull @Min(0l)
    Integer quantity

    @NotNull @Min(0l)
    BigDecimal unitPrice

    BigDecimal getTotal() {
        quantity * unitPrice
    }

    @Override
    void process() {
        InventoryEntry entry = new InventoryEntry(account: account, date: this.whenOccured, entryType: EntryType.SPAREPART,
            event: this, amount: total, quantity: quantity)
        account.entries << entry
        resultingEntries << entry
        account.events << this
    }

    List<AccountingEvent> adjust(Integer newQuantity, BigDecimal newUnitPrice) {
        List<AccountingEvent> results = []
        results << new RevokedEvent(this)
        results << new IncomingSparepartEvent(account: account, eventType: eventType, resultingEntries: [],
            whenNoticed: DateTime.now(), whenOccured: whenOccured, invoice: invoice, quantity: newQuantity,
            unitPrice: newUnitPrice, sparepart: sparepart)
        results*.process()
        results
    }
}

The source code for IncomingSparepartInvoice.groovy is:

package domain

import ...

@DomainModel @Entity @Canonical(excludes = "events")
class IncomingSparepartInvoice{

    @NotEmpty @Size(min=8, max=8)
    String invoiceNumber

    @NotNull @Type(type="org.jadira.usertype.dateandtime.joda.PersistentLocalDate")
    LocalDate date

    @NotEmpty @Size(min=3, max=50)
    String supplierName

    @NotEmpty @OneToMany(cascade=CascadeType.ALL, orphanRemoval=true, mappedBy="invoice")
    List<IncomingSparepartEvent> events = []

    BigDecimal total() {
        events.sum {
            if (it instanceof IncomingSparepartEvent && it.deleted=='N') {
                return it.total
            }
            0
        } ?: 0
    }

}
Add New Incoming Spare Part

Add New Incoming Sparepart

In the previous post, I created RevokeEvent class to represent ‘deleted’ event. It can be used to implement the reversal adjustment pattern for spare part related events. For example, if I change IncomingSparepartEvent‘s quantity from 100 to 10 an shown in the picture below:

Adjusting Incoming Sparepart Event

Adjusting Incoming Sparepart Event

It will generate two events: one with quantity = -100 (the RevokeEvent), and the other with quantity = 10 (a new IncomingSparepartEvent), as shown below:

Incoming Spare Part Event After Adjusment

Incoming Spare Part Event After Adjusment

I will use the following JUnit’s test case in order to test the adjustment without actually connect to a database:

   void testRevokeEvent() {
        Sparepart sparepart = new Sparepart("SP001", "Sparepart 1", new BigDecimal("25000"))

        IncomingSparepartInvoice invoice = new IncomingSparepartInvoice(invoiceNumber: "INVOICE1",
                date: LocalDate.now(), supplierName: "SUPPLIER1")

        IncomingSparepartEvent event1 = new IncomingSparepartEvent(sparepart: sparepart, invoice: invoice,
                quantity: 10, unitPrice: new BigDecimal("23000"), account: sparepart.inventory,
                eventType: AccountingEventType.SPAREPART_INCOMING, whenNoticed: DateTime.now(), whenOccured: DateTime.now())
        event1.process()

        IncomingSparepartEvent event2 = new IncomingSparepartEvent(sparepart: sparepart, invoice: invoice,
                quantity: 30, unitPrice: new BigDecimal("22500"), account: sparepart.inventory,
                eventType: AccountingEventType.SPAREPART_INCOMING, whenNoticed: DateTime.now(), whenOccured: DateTime.now())
        event2.process()

        IncomingSparepartEvent event3 = new IncomingSparepartEvent(sparepart: sparepart, invoice: invoice,
                quantity: 5, unitPrice: new BigDecimal("23500"), account: sparepart.inventory,
                eventType: AccountingEventType.SPAREPART_INCOMING, whenNoticed: DateTime.now(), whenOccured: DateTime.now())
        event3.process()

        assertEquals(new BigDecimal("1022500"), sparepart.inventory.balance())
        assertEquals(45, sparepart.inventory.quantity())
        assertEquals(new BigDecimal("22722.2222222222"), sparepart.inventory.averageCost())

        // Delete event2
        RevokedEvent revokedEvent = new RevokedEvent(event2)
        revokedEvent.process()

        assertEquals(new BigDecimal("347500"), sparepart.inventory.balance())
        assertEquals(15, sparepart.inventory.quantity())
        assertEquals(new BigDecimal("23166.6666666667"), sparepart.inventory.averageCost())
    }

    void testAdjust() {
        Sparepart sparepart = new Sparepart("SP001", "Sparepart 1", new BigDecimal("25000"))

        IncomingSparepartInvoice invoice = new IncomingSparepartInvoice(invoiceNumber: "INVOICE1",
                date: LocalDate.now(), supplierName: "SUPPLIER1")

        IncomingSparepartEvent event1 = new IncomingSparepartEvent(sparepart: sparepart, invoice: invoice,
                quantity: 10, unitPrice: new BigDecimal("23000"), account: sparepart.inventory,
                eventType: AccountingEventType.SPAREPART_INCOMING, whenNoticed: DateTime.now(), whenOccured: DateTime.now())
        event1.process()

        IncomingSparepartEvent event2 = new IncomingSparepartEvent(sparepart: sparepart, invoice: invoice,
                quantity: 30, unitPrice: new BigDecimal("22500"), account: sparepart.inventory,
                eventType: AccountingEventType.SPAREPART_INCOMING, whenNoticed: DateTime.now(), whenOccured: DateTime.now())
        event2.process()

        IncomingSparepartEvent event3 = new IncomingSparepartEvent(sparepart: sparepart, invoice: invoice,
                quantity: 5, unitPrice: new BigDecimal("23500"), account: sparepart.inventory,
                eventType: AccountingEventType.SPAREPART_INCOMING, whenNoticed: DateTime.now(), whenOccured: DateTime.now())
        event3.process()

        assertEquals(new BigDecimal("1022500"), sparepart.inventory.balance())
        assertEquals(45, sparepart.inventory.quantity())
        assertEquals(new BigDecimal("22722.2222222222"), sparepart.inventory.averageCost())

        // Adjust event2
        event2.adjust(3, new BigDecimal("22000"))

        assertEquals(new BigDecimal("413500"), sparepart.inventory.balance())
        assertEquals(18, sparepart.inventory.quantity())
        assertEquals(new BigDecimal("22972.2222222222"), sparepart.inventory.averageCost())
    }

When user creates a new incoming spare part event that contains non existing spare part, program will create a new Sparepart object and its accounts. This is the structure of Sparepart class:

The Sparepart

The Sparepart

This is the source code for Sparepart.groovy:

package domain

import ...

@DomainModel @Entity @Canonical
class Sparepart {

    Sparepart() {}

    Sparepart(String partNumber, String name, BigDecimal price) {
        this.partNumber = partNumber
        this.name = name
        this.price = price
        setInventory(new SparepartInventory())
        setExpense(new SparepartExpense())
        setSales(new SparepartSales())
    }

    @NotEmpty @Size(min=5, max=5)
    String partNumber

    @NotEmpty @Size(min=3, max=50)
    String name

    @NotNull @Min(1l)
    BigDecimal price

    @NotNull @OneToOne(cascade=CascadeType.ALL, orphanRemoval=true, mappedBy="sparepart")
    SparepartInventory inventory

    @NotNull @OneToOne(cascade=CascadeType.ALL, orphanRemoval=true, mappedBy="sparepart")
    SparepartExpense expense

    @NotNull @OneToOne(cascade=CascadeType.ALL, orphanRemoval=true, mappedBy="sparepart")
    SparepartSales sales

    void setInventory(SparepartInventory inventory) {
        this.inventory = inventory
        inventory.sparepart = this
    }

    void setExpense(SparepartExpense expense) {
        this.expense = expense
        expense.sparepart = this
    }

    void setSales(SparepartSales sales) {
        this.sales = sales
        sales.sparepart = this
    }

}

This is the source code for SparepartInventory.groovy:

package domain

import ...

@DomainModel @Entity @Canonical(excludes = "sparepart")
class SparepartInventory extends Account {

    @OneToOne
    Sparepart sparepart

    BigDecimal averageCost() {
        balance() / quantity()
    }

    @Override
    BigDecimal balance() {
        entries.sum { it.amount ?: 0 } ?: 0
    }

    Integer quantity() {
        entries.sum { Entry entry ->
            if (entry instanceof InventoryEntry) {
                return entry.quantity ?: 0
            } else if (entry instanceof AdjusmentEntry) {
                return (-entry.adjustedEntry.quantity) ?: 0
            }
            0
        } ?: 0
    }

}

This is the source code for SparepartExpense.groovy:


package domain

import ...

@DomainModel @Entity @Canonical(excludes = "sparepart")
class SparepartExpense extends Account {

    @OneToOne
    Sparepart sparepart

    @Override
    BigDecimal balance() {
        entries.sum { it.amount ?: 0 } ?: 0
    }

}

This is the source code for SparepartSales.groovy:

package domain

import ...

@DomainModel @Entity @Canonical(excludes = "sparepart")
class SparepartSales extends Account {

    @OneToOne
    Sparepart sparepart

    @Override
    BigDecimal balance() {
        entries.sum { Entry entry ->
            entry.amount ?: 0
        } ?: 0
    }

    BigDecimal grossProfit() {
        balance() - sparepart.expense.balance()
    }

}

To track inventory, I will use perpetual inventory system (from traditional accounting).   Unlike periodic inventory system, perpetual inventory provides real time data.   It doesn’t require closing entry, but it use three accounts: the inventory (SparepartInventory class), the sales (SparepartSales class), and cost of goods sold (SparepartExpense class).

An incoming spare part event will increase inventory account by its total purchase price.   A spare part sales event will increase sales account by its selling price.   Spare part sales event will also increase cost of goods sold account and decrease inventory account by the same amount.   In order to calculate the cost of goods sold, I use average cost (AVCO) method.   It is easier to implement AVCO than the other methods: First in First out (FIFO) and Last in First out (LIFO).  Finally, gross profit can be calculated by subtracting the balance of sales account with the balance of cost of goods sold account.

This is the test case for testing the average cost (AVCO) calculation method:

    void testGetAverageCost() {
        Sparepart sparepart = new Sparepart("SP001", "Sparepart 1", new BigDecimal("25000"))

        IncomingSparepartInvoice invoice = new IncomingSparepartInvoice(invoiceNumber: "INVOICE1",
                date: LocalDate.now(), supplierName: "SUPPLIER1")

        IncomingSparepartEvent event1 = new IncomingSparepartEvent(sparepart: sparepart, invoice: invoice,
                quantity: 10, unitPrice: new BigDecimal("23000"), account: sparepart.inventory,
                eventType: AccountingEventType.SPAREPART_INCOMING, whenNoticed: DateTime.now(), whenOccured: DateTime.now())
        event1.process()

        IncomingSparepartEvent event2 = new IncomingSparepartEvent(sparepart: sparepart, invoice: invoice,
                quantity: 30, unitPrice: new BigDecimal("22500"), account: sparepart.inventory,
                eventType: AccountingEventType.SPAREPART_INCOMING, whenNoticed: DateTime.now(), whenOccured: DateTime.now())
        event2.process()

        IncomingSparepartEvent event3 = new IncomingSparepartEvent(sparepart: sparepart, invoice: invoice,
                quantity: 5, unitPrice: new BigDecimal("23500"), account: sparepart.inventory,
                eventType: AccountingEventType.SPAREPART_INCOMING, whenNoticed: DateTime.now(), whenOccured: DateTime.now())
        event3.process()

        assertEquals(new BigDecimal("1022500"), sparepart.inventory.balance())
        assertEquals(45, sparepart.inventory.quantity())
        assertEquals(new BigDecimal("22722.2222222222"), sparepart.inventory.averageCost())
    }

The following picture shows how user can display accounting information on a spare part (better implementation should allow user to select period and to filter entries):

Displaying Inventory For Spare Part

Displaying Inventory For Spare Part

Displaying Sales For Spare Part

Displaying Sales For Spare Part

Spare part sales event (SparepartSalesEvent) is a work order event (WorkOrderEvent) because it will be generated during motor cycle repair. In a real case, customer can buy a spare part without ordering a motor cycle repair. The picture below shows the structure of SparepartSalesEvent:

The SparepartSalesEvent

The SparepartSalesEvent

This is the source code for SparepartSalesEvent.groovy:

package domain

import ...

@DomainModel @Entity @Canonical
class SparepartSalesEvent extends WorkOrderEvent {

    @NotNull @ManyToOne(cascade=CascadeType.MERGE)
    Sparepart sparepart

    @NotNull @Min(1l)
    Integer quantity

    @NotNull @Min(1l)
    BigDecimal amount

    @Override
    AccountingEventType getEventType() {
        AccountingEventType.SPAREPART_SALES
    }

    @Override
    void process() {

        BigDecimal costOfGoods = sparepart.inventory.averageCost().multiply(quantity)
        BigDecimal total = amount * quantity

        InventoryEntry inventoryEntry = new InventoryEntry(account: sparepart.inventory, date: whenOccured,
            entryType: EntryType.SPAREPART, event: this, amount: -costOfGoods, quantity: -quantity)
        sparepart.inventory.entries << inventoryEntry
        resultingEntries << inventoryEntry

        TransactionEntry expenseEntry = new TransactionEntry(account: sparepart.expense, date: whenOccured,
            entryType: EntryType.SPAREPART, event: this, amount: costOfGoods)
        sparepart.expense.entries << expenseEntry
        resultingEntries << expenseEntry

        TransactionEntry salesEntry = new TransactionEntry(account: sparepart.sales, date: whenOccured,
            entryType: EntryType.SPAREPART, event: this, amount: total)
        sparepart.sales.entries << salesEntry
        resultingEntries << salesEntry

        WorkOrderEntry workOrderEntry = new WorkOrderEntry(account: account, date: whenOccured,
            entryType: EntryType.SPAREPART, event: this, amount: total)
        account.entries << workOrderEntry
        resultingEntries << workOrderEntry

        super.process()
    }
}

Because SparepartSalesEvent is a WorkOrderEvent, it can be treated just like any other WorkOrderEvent, as shown in the following test case:

void testProcess() {
        // Sparepart Incoming
        Sparepart sparepart = new Sparepart("SP001", "Sparepart 1", new BigDecimal("25000"))

        IncomingSparepartInvoice invoice = new IncomingSparepartInvoice(invoiceNumber: "INVOICE1",
                date: LocalDate.now(), supplierName: "SUPPLIER1")

        IncomingSparepartEvent event1 = new IncomingSparepartEvent(sparepart: sparepart, invoice: invoice,
                quantity: 10, unitPrice: new BigDecimal("23000"), account: sparepart.inventory,
                eventType: AccountingEventType.SPAREPART_INCOMING, whenNoticed: DateTime.now(), whenOccured: DateTime.now())
        event1.process()

        IncomingSparepartEvent event2 = new IncomingSparepartEvent(sparepart: sparepart, invoice: invoice,
                quantity: 30, unitPrice: new BigDecimal("22500"), account: sparepart.inventory,
                eventType: AccountingEventType.SPAREPART_INCOMING, whenNoticed: DateTime.now(), whenOccured: DateTime.now())
        event2.process()

        IncomingSparepartEvent event3 = new IncomingSparepartEvent(sparepart: sparepart, invoice: invoice,
                quantity: 5, unitPrice: new BigDecimal("23500"), account: sparepart.inventory,
                eventType: AccountingEventType.SPAREPART_INCOMING, whenNoticed: DateTime.now(), whenOccured: DateTime.now())
        event3.process()

        assertEquals(new BigDecimal("1022500"), sparepart.inventory.balance())
        assertEquals(45, sparepart.inventory.quantity())
        assertEquals(new BigDecimal("22722.2222222222"), sparepart.inventory.averageCost())

        // Work Order
        Customer customer = new Customer("Solid Snake", "X 123 YZ")

        WorkType quickService = new WorkType("QS", "Quick Service")

        WorkTypeCostPR normalPrice = new WorkTypeCostPR("Normal Price", EntryType.SERVICE, AccountingEventType.PAYMENT, [:])
        normalPrice.priceList[quickService] = new BigDecimal("12000")

        Pricing normalPricing = new Pricing(LocalDate.now().minusMonths(1), true, "Normal Pricing",
            [normalPrice])

        WorkOrder workOrder = new WorkOrder(orderNumber: "W001", customer: customer, pricing: normalPricing, workType: quickService)

        // Test Step Register
        WorkOrderEvent registerEvent = new WorkOrderEvent(account: workOrder, eventType: AccountingEventType.REGISTER,
            whenNoticed: DateTime.now(), whenOccured: DateTime.now())
        registerEvent.process()
        assertEquals(0, registerEvent.resultingEntries.size())
        assertEquals(0, workOrder.entries.size())
        assertEquals(1, workOrder.events.size())
        assertEquals(registerEvent, workOrder.events[0])

        // Test Step Working
        WorkOrderEvent workingEvent = new WorkOrderEvent(account: workOrder, eventType: AccountingEventType.WORKING,
            whenNoticed: DateTime.now(), whenOccured: DateTime.now())
        workingEvent.process()
        assertEquals(0, workingEvent.resultingEntries.size())
        assertEquals(0, workOrder.entries.size())
        assertEquals(2, workOrder.events.size())
        assertEquals(registerEvent, workOrder.events[0])
        assertEquals(workingEvent, workOrder.events[1])

        // Test Step Sparepart Sales
        SparepartSalesEvent sparepartSalesEvent= new SparepartSalesEvent(account: workOrder, whenNoticed: DateTime.now(),
            whenOccured: DateTime.now(), sparepart: sparepart, amount: new BigDecimal("27000"), quantity: 2)
        sparepartSalesEvent.process()

        assertEquals(4, sparepartSalesEvent.resultingEntries.size())

        assertEquals(43 , sparepart.inventory.quantity())
        assertEquals(new BigDecimal("977055.5555555556"), sparepart.inventory.balance())
        assertEquals(new BigDecimal("45444.4444444444"), sparepart.expense.balance())
        assertEquals(new BigDecimal("54000"), sparepart.sales.balance())

        assertEquals(1, workOrder.entries.size())
        assertEquals(new BigDecimal("54000"), workOrder.entries[0].amount)

        assertEquals(3, workOrder.events.size())
        assertEquals(registerEvent, workOrder.events[0])
        assertEquals(workingEvent, workOrder.events[1])
        assertEquals(sparepartSalesEvent, workOrder.events[2])

        // Test Step Finish
        WorkOrderEvent finishEvent = new WorkOrderEvent(account: workOrder, eventType: AccountingEventType.FINISH,
            whenNoticed: DateTime.now(), whenOccured: DateTime.now())
        finishEvent.process()
        assertEquals(0, finishEvent .resultingEntries.size())
        assertEquals(1, workOrder.entries.size())
        assertEquals(4, workOrder.events.size())
        assertEquals(registerEvent, workOrder.events[0])
        assertEquals(workingEvent, workOrder.events[1])
        assertEquals(sparepartSalesEvent, workOrder.events[2])
        assertEquals(finishEvent, workOrder.events[3])

        // Test Payment
        WorkOrderEvent paymentEvent = new WorkOrderEvent(account: workOrder, eventType: AccountingEventType.PAYMENT,
                whenNoticed: DateTime.now(), whenOccured: DateTime.now())
        paymentEvent.process()
        assertEquals(1, paymentEvent.resultingEntries.size())
        assertEquals(new BigDecimal("12000"), paymentEvent.resultingEntries[0].amount)

        assertEquals(2, workOrder.entries.size())
        assertEquals(new BigDecimal("54000"), workOrder.entries[0].amount)
        assertEquals(new BigDecimal("12000"), workOrder.entries[1].amount)
        assertEquals(new BigDecimal("66000"), workOrder.balance())

        assertEquals(5, workOrder.events.size())
        assertEquals(registerEvent, workOrder.events[0])
        assertEquals(workingEvent, workOrder.events[1])
        assertEquals(sparepartSalesEvent, workOrder.events[2])
        assertEquals(finishEvent, workOrder.events[3])
        assertEquals(paymentEvent, workOrder.events[4])
    }

Once I make sure all tests passed, I can start developing the MVC (presentation layer) as shown in the following picture:

Add A Sparepart Sale In Work Order

Add A Sparepart Sale In Work Order

Sparepart Sales Event Will Be Displayed in The Event Lists

Sparepart Sales Event Will Be Displayed in The Event Lists

Spare Part Sales Amount Will Be Added To Work Order Balance

Spare Part Sales Amount Will Be Added To Work Order Balance

The complete source code can be found at the following link: https://docs.google.com/file/d/0B-_rVDnaVRCbaWx0aGRkamJWQm8/edit?usp=sharing.  It requires Griffon 1.2 and simple-jpa 0.4.1 plugin.

Perihal Solid Snake
I'm nothing...

3 Responses to Using Accounting Analysis Pattern For Inventory

  1. Komang Hendra Santosa mengatakan:

    Mas kalo kita mau jual sparepart secara eceran gmn ya?dan jg pembuatan print struk serta data penjualan selama sebulannya

    • Solid Snake mengatakan:

      Pembelian barang secara eceran tetap mempengaruhi inventory sama seperti pembelian barang lainnya.

      Mencetak struk dan membuat laporan adalah bagian dari application logic bukan business logic seperti yang dibahas pada artikel ini. Sebagai informasi, untuk mencetak struk, bila mesin cetak mendukung JavaPOS, gunakan library bersangkutan atau gunakan bawaan Java seperti Java2D printing. Untuk membuat laporan, gunakan Jasper Report.

    • Solid Snake mengatakan:

      Sebagai informasi, berikut ini adalah artikel yang berkaitan dengan menampilkan preview laporan atau mencetak laporan secara langsung tanpa preview (untuk contoh kasus struk) di Griffon: Merancang laporan dan Menampilkan atau mencetak.

Apa komentar Anda?

Please log in using one of these methods to post your comment:

Logo WordPress.com

You are commenting using your WordPress.com account. Logout / Ubah )

Gambar Twitter

You are commenting using your Twitter account. Logout / Ubah )

Foto Facebook

You are commenting using your Facebook account. Logout / Ubah )

Foto Google+

You are commenting using your Google+ account. Logout / Ubah )

Connecting to %s

%d blogger menyukai ini: