http://minussignkm.blogspot.com/2015/06/lecture-data-analysis-and-decision.html
In Thailand, Revenue department set a rule for submit revenue (PP.30) for every company on every month.
For PP.30 we have to show selling amount and buying amount that have a vat. Then settle those amount.
If we have a vat for selling more than vat for buying we have to pay the vat to revenue , but if we have vat for buying more than vat for selling we can refund the vat.
In reality we shouldn't refund the vat (why? I don't mention in here). So we left some buying vat to settle with the selling vat on the next month (Revenue department allow us to use 6 months past).
But the problem is how we can select the combination summation of a hundred invoice that very close and not over Selling Amount?
The solution is here.!!!
Example
Selling on JAN
Buying on JAN
Selling = 46,523.40
Buying = 84,934.00
To select the combination of Buying that mostly equal but not over 46,523.40
We can use excel solver.
1. Add column for logic (use , not use ). Name it to Binary. and put sample number (0-1).
2. Add column for amount that be selected of not be selected. Name it to Select Amount. and put the formula for =Amount x Binary
3. Add sum of select amount
4. Put the target Value (Selling Amount)
5. Open Excel Solver
6. Set parameter like this
- Set Objective is the sum of select amount (3) (must be formula)
- To => check Max radio box due to we need mostly close amount with the target value.
- By Changing Variable Cells is Binary column (excel can try to change the value in this range)
- Subject to the Contrains (Click Add)
+ Binary column must be binary
+ Sum of selected amount (3) <= Target Amount (4)
- Select a Soling Method = Simplex LP
7. Click solve
8. Solver will put the binary amount to select a proper list of number that match the target value.
9. You just pick the row that have binary value = 1 to prepare the PP.30.
You can download example file in here.
https://drive.google.com/open?id=0B6ldliI-dnbxdDZhSE9CSkZTTWs&authuser=0
ไม่มีความคิดเห็น:
แสดงความคิดเห็น