Handling Large Accounting Data Records in Microsoft Excel pt3

For this tutorial, I’m going to design a new format for our accounting records.

We have the Purchase and Sales, report

Download this file

Let’s Start

As you can see there are two Data entry accounts. (Purchases and Sales).

We’re going to consolidate these two using the Data Entry Form, this will be a more complicated tutorial compared to my previous posts.

But I’ll go easy on you, step by step, no shortcuts, just learning.

1. Add a worksheet to your workbook, rename it to “Data Entry Form”.
2. On cell B3 type, “Date:”, while on cell B4 type, “Transaction:”
3. Under transaction, cell B5 type “Docs no.”
4. Next, cell C7 to F7, type “Items”, “Quantity(kg)”, “Unit Cost”, “Total Cost” respectively.
5. Take a look at the image below
   
6. The entries have been given
7. Add borders, to make it look like a form.
8. You may notice of headings “Unit Cost” and “Total Cost” under the Purchase transaction, which are unchangeable.
9. If you’re going to change the Transaction into “Sales”, the two headings will remain unchanged, which is irrelevant for our system. Purchase is for the cost, while Sale is for the selling price not the Unit Cost.
10. Question, how are we going to change this from static to dynamic?
11. Just use the formula instead of constant i.e., “Function IF”, when a target change, the other target will probably change. See this LINK for how to use IF
12. Formula:

• For cell E7 —> “=IF(C4=”Purchase”,”Unit Cost”,IF(C4=”Sales”,”Unit Price”,”Please enter correct transaction”))”
• If cell C4 is Purchase then cell E7 will be Unit Cost
• If cell C4 is Sales ,the cell E7 is Unit Price
• For cell F7 –> “=IF(C4=”Purchase”,”Total Cost”,IF(C4=”Sales”,”Total Sales”,”Please enter correct transaction”))”
• If cell C4 is Purchase therefore cell F7 is Total Cost
• If cell C4 is Sales therefore cell F7 is Total Sales
• For incorrect inputs cell E7 and F7 value will be “Please enter correct transaction”
• Try it and See for yourself

1. Create another worksheet, rename it to “Database”
2. From that sheet cell A2 to I2, type:
• Date
• Transaction
• Docs no.
• Items
• Quantity (kg)
• Unit Cost
• Total Cost
• Unit Sales
• Total Sales
  
3. For posting or transferring our data entries to Database,
4. Create a CommandButton place it under the table (here we’re going to use vba)
   
5. Right click the CommandButton, click properties (be sure the design mode is enabled)
6. Choose your backcolor (the color of your button), and change the name of the caption if you want to  (e.g., “Post it”)
7. For reporting, add a worksheet, then rename it to “Report”.
   
8. Our file is now formatted to deal with our accounting records. Next step would be working with our program.

Download Output File

Advertisement