The CSV export, of the SchoolCash Catalog, Item Order Report, provides value-based pricing (VBP) and/or transactional pricing breakdown information. A related article can be found by selecting this link Value-Based Pricing (VBP) Transactional - Breakdown Report


QUESTION:

What are the steps to calculate the amount owing vendor/supplier, rounding and transactional fee?


RESPONSE:

The SchoolCash Catalog Item Order Report, CSV export provides this information.  


Note: This is not the report to provide to the vendor/supplier or distribution.

Suggested related article DRAFT - 3 Item with Options Report for Vendor & Distribution - Value-Based Pricing (VBP) Transactional


PROCESS:

Whether you are, or are not, an Excel user, the following process will provide easy-to-follow instructions.


From SchoolCash Catalog, select Reports>Item Order Report

Choose applicable item filters

Generate

Select CSV (comma delimited) 

Click Export


The CSV export provides rows and columns of data. 


Novice Excel users 

may find the amount of information overwhelming. The removal of some unnecessary columns is advisable.


1. Remove Columns A through to, and including Column I

    a. Left Click in the field labelled "A". 

    b. Keep the Left Click depressed, and drag the cursor across Columns A through to, and include, Column I


    c. Within the grey highlighted area, Right Click, and select Delete  


Column J through will change to Column A through to R, for example, J was Item Name, the Column will change to A


2. Add Filters to provide the opportunity to view specific information, for example, want to review revenue from a specific grade or homeroom.


    a. Click the triangle in the left-hand corner of the spreadsheet. This will highlight the entire worksheet.


    b. From the Home tab (top line), Select Sort & Filter dropdown

    c. Click on Filter


    d. Each column will have a dropdown arrow


Try selecting a dropdown such as homeroom and remove all checkmarks but one. 

This will filter the spreadsheet to find just that homeroom. 

More than one selection can be chosen. 

To view all information or make another selection, choose the dropdown again and select all or make new selections. 

Try it!


3. Add Grand Total Calculations to: 

  • Column H, Paid Item Balance (school revenue);
  • Column I, Paid Transaction Fee (%); and 
  • Column J, Paid Rounding (may vary by district - district decision)


   a. Scroll to the last line of data

   b. Click in the field of Column H, Paid Item Balance after the last line of data


    c. From the Home tab (top line), Click the Sum icon and select the keyboard tab key, this will calculate all the fields above and enter it in the field

FYI: This amount reflects the amount entered in the SchoolCash Catalog item creation Option/Choice amount field, prior to the addition of the transactional fee and rounding.



   d. Repeat for field of Column I, Paid Transaction Fee after the last line of data

   e. Repeat for field of Column J, Paid Rounding after the last line of data


Intermediate or Advanced users 

may wish to utilize the Excel Pivot Tables feature to capture the information required. 


Additional Related Articles

DRAFT - 1 Report with Breakdown - Value-Based Pricing (VBP) Transactional

DRAFT - 3 Item with Options Report for Vendor & Distribution - Value-Based Pricing (VBP) Transactional

How to Generate Reports