Handling Large Accounting Data Records in Microsoft Excel pt2

Now we have the Purchase Transaction in my previous post

Let’s move on to other transaction,  Sales Transaction

We buy, We sell our products for certain markup

Example: For 1 kilo of Orange, 90 pesos is the cost per kilo, we have to gain about 20% of its cost. That is, 90 x .20 = 18 pesos is our profit so that our selling price is (90 pesos + 18 pesos = 108). 108 pesos of Orange fruit for 1 kg. (or 90 * 120%)

Orange (Fruit)
Selling price per kilo = 108 pesos
Cost per kilo = 90 pesos
Profit or Mark-up per kilo = 18 pesos

1. Create another worksheet, give it a name”Data Entry Sales”
2. Paste this:

3. Date	Items	Quantity (kg)	Selling price	Total Sales	Docs No.
   8/1/2015	Orange	7	108	756
   8/1/2015	Banana	10	66	660
   8/1/2015	Apple	3	108	324
   8/1/2015	Mango	15	60	900
   8/1/2015	Avocado	5	36	180
   8/2/2015	Orange	7	108	756
   8/2/2015	Banana	10	66	660
   8/2/2015	Apple	5	108	540
   8/2/2015	Mango	15	60	900
   8/2/2015	Avocado	5	36	180
   8/3/2015	Orange	5	108	540
   8/3/2015	Banana	12	66	792
   8/3/2015	Apple	7	108	756
   8/3/2015	Mango	20	60	1200
   8/3/2015	Avocado	5	36	180
   8/4/2015	Orange	7	108	756
   8/4/2015	Banana	12	66	792
   8/4/2015	Apple	7	108	756
   8/4/2015	Mango	20	60	1200
   8/4/2015	Avocado	3	36	108
   8/5/2015	Orange	8	108	864
   8/5/2015	Banana	14	66	924
   8/5/2015	Apple	8	108	864
   8/5/2015	Mango	18	60	1080
   8/5/2015	Avocado	3	36	108
   8/6/2015	Orange	8	108	864
   8/6/2015	Banana	16	66	1056
   8/6/2015	Apple	6	108	648
   8/6/2015	Mango	15	60	900
   8/6/2015	Avocado	3	36	108

4. These are the sales transactions occurred from August 1 to 6
   
5. Add another worksheet name it to”Summary of Sales”
6. Insert Pivot Table
   
7. Select the range that you want to analyze
   
8. Choose the Data Entry Sales sheet
   
9. Highlight from cell “A1” to “F31”
10. Click OK
11. From PivotTable fields
12. Drop “Items” to “Rows” area
13. Drop “Quantity(kg)” and “Total Sales” to “Values” area
    
14. So you now have Sales and Purchases Report

ADDTIONAL

1. If you want to select your desired range of dates, go on to PivotTable Field drop “Date” to “Filter” area. For example, I want from August 1 to 4, just tick the desired date checkbox 8/1/2015 to 8/4/2015, uncheck the unnecessary dates. (be sure to check first the “Multiple Items” checkbox)
   
2. You noticed those quantities or amounts can’t easy to read. It’s because of the formatting of a cell, simply by looking at the default number formats which is “General”
   
3. To change the format, right click onto your desired column (within the PivotTable) e.g., “Sum of Quantity (kg)” .Select cell B3, right click it.
4. Choose “Number Format”, under category , click “Accounting”
5. If you don’t want to have a symbol in every row. Choose “None”
   
6. Hit enter

As you probably notice, in every account type (i.e., Sales Account or Purchases Account) there must be separate sheet for them for you to analyze each. But how about, we include more additional account type, like Other Expense Account, or even Salary Account?. Perhaps there can be many worksheets as in your workbook, this will may be slows down your work.

What I’m telling you about is, we can organize this “floody” worksheets into a centralized reporting. For we have to work in a single entry basis and post this to our Database and finally generate a Report

                           Data Entry –> Database –> Report

Download the Output File

Advertisement