Expense
- Salaries
- Rent
-- Warehouses
-- Retail space
- Utilities
253 karma · joined June 12, 2017
Expense
- Salaries
- Rent
-- Warehouses
-- Retail space
- Utilities
SELECT SUM(amount) /* this should sum to zero */
FROM transaction_line
---
Also to identify any non-balancing transaction is easy:
SELECT tx.id, tx.date, SUM(amount) tx_sum
FROM transaction tx
INNER JOIN transaction_line txl ON txl.transaction_id = tx.id
GROUP BY tx.id
HAVING tx_sum != 0
---
This will identify them even if caused by your business logic bug, database bug, disk corruption etc.
This can also be done on the single accounting transaction just after insert too and can be done within the same database transaction.
Sales Cr $100
Cash Dr $30
Bank Dr $70
Your approach will have:
Sales Cr $70, Bank Dr $70
Sales Cr $30, Cash $30
That looks like two sales, which is not really the case.
SELECT account.name, SUM(amount) balance
FROM account ac
INNER JOIN transaction_line tl ON tl.account_id = ac.id
GROUP BY account.name
You can cache this balance values with a current_balance column on accounts table
Once you have that, for any real world transaction, all you need to figure out is what are the accounts to debit/credit, ie classification. That is a higher level thing and is the business logic of an accounting application.
accounts (id, name)
transaction (id, date) /* some call it journal */
transaction_line (id, transaction_id[fk], account_id[fk], amount)
I use -ve amount for credit, +ve for debit. That way when you do SUM(trascation_line.amount) it would come to 0.
This also cleanly maps to database transaction too where all transaction_line rows and transaction row should be atomic.
If you want multi-currency support, instead of amount column, it needs to be currency_code, currency_rate, amount_in_currency, amount_in_basecurrency ( i know we don't need this all, but sometimes you want to record the amount as entered, eg EURO 52.10 u want to record as entered even if your base currency is USD)
1. Starting capital: Capital Cr $100, Bank Db $100
2. Purchase widgets worth $50: Bank Cr $50, Inventory Db $50
3. You sell half of this inventory (valued $25) for $75 in cash:
Sales Cr $75, Cash Db $75
Inventory Cr $25, Cost of goods sold Db $25
4. You sell rest of your inventory on credit to John for $110:
Sales Cr $110, Accounts Receivable-John Db $110,
Inventory Cr $25, Cost of goods sold Db $25
5. You pay $30 salaries via cheque: Bank Cr $30, Expense-Salary Db $30
At the end of all this your PL (profit loss statement) would look like this
Sales: $185 (110 + 75)
Cost of goods sold: $50 (as you sold all of the inventory)
Gross profit = sales - cost of goods sold = 185 - 50 = $135
Expenses = $30 (salaries only)
Net profit = gross profit - expenses = 135 - 30 = $105
Your balance sheet at the end of all this will be :
Capital: Cr $100
Accumulated profit: Cr $105
Bank: Db $20
Cash: Db $75
AR John: Db $110
Inventory: 0 (as you sold all).
Note the balance sheet balances nicely as Capital + Accumulated profit = bank + cash + AR + inventory
No. Some transactions have 3 lines, eg: two debits and one credit. Some examples: - Split payment at a shop, $100 item bought with $70 cheque and $30 cash would be credit sales $100 debit cash $30 debit bank $70
Now if you introduce sales tax or VAT/GST, its more complicated. Say the $100 item is actually $90 + $10 VAT, then entries goes: credit sales $90 credit vat-payable $10 debit cash $30 debit bank $70
- Shift more to sales/marketing and a bit less on dev in my current work.
- Side hustles in SaaS/ecommerce/blockchain
- Read more
Solution: Preferably a cloud based quantity take-off software that can read/mark cad/pdf files. Once the data has been input (manually or otherwise), it should be able to generate various reports (or presentation bills) based on what is being asked,(see questions above).
The only cloud based one I know is Stack[0]
Problem 1: Architecture/Engineering is still very much traditional. Various distributed teams across cities/countries email CAD (mostly autocad) files back and forth or host it in cloud, with changes highlighted in revision clouds. The delivery of final design involved the interplay of specialisted teams, architecture/structure/M&E/QS/Civl/Onsite etc. Lack of proper tools cause problems in synchronising and keeping everyone uptodate with all the changes, error prone and its extremely manual. This becomes evenmore important when the actual construction is going on and you need to keep track of ammendments etc. A simple architectural change (eg moving a door), need to be reflected in structure/m&e all drawings, possiblly reworking internal wiring etc.
They (I dont mean everyone, but most of the industry) have never seen how distributed teams of programmers use version control software to sync/branch/diff/merge work from multiple teams.
Solution: A tool that can seemlessly do diff/merge/branch of CAD drawings (perhaps integrated into AutoCAD or other CADs itself) will bring a lot of efficiency into architecture/engineering field. Lead Architect could have his team working on various parts of the design/experimental ideas (think feature branches), which he merges to his tree. Engineering team will pull from Lead Architect. M&E team can pull from both these teams etc (this is similar to Linus trusted leutinent model). The idea is NOT to change how they organise their team or automate everything, but provide tools to help the human to make things more efficient.
As far as I know OnShape are the only people working on such a thing and that is 3D. Most arch/eng is still 2D. Autodesk themselves have Revit but I am not sure of to what extent it does all these.
Wasnt it the case that no one was able to explain Michaelson-Morley experiment? That speed of light was measured to be the same irresective of observer speed? Ppl came with all sorts of explanations (ether/lorentz tx etc), but it was Einstein who had the creativity to suggest that maybe time itself was slowing down. With that insight, the rest was beautifuly derivable.
And this is also why, Hilbert was able to (and otherws were racing to) derive the field equations before Einstein, since Einstein needed help from mathematicians (Grossman?), and Hilbert was an already excellent mathematician. SR and GR is derivable from beautifuly simple insights (time slowdown, gravity/acceleration equivalence).
One could say Einstein came with this insight out of the blue, but Poincare was also investigating time dilation, but stopped because it was too counter-intuitive.
Edit: typos
Thanks.
ItemMaker = function(data){
return {
$cell: true,
$type: "h1",
style: "font-size: 100px;",
$text: data,
}
}
var i1 = ItemMaker('foo');
var i2 = ItemMaker('bar')