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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: