End of Month Spreadsheet

The End of Month report provides an easy way to balance your monthly numbers. 

The End of Month report is divided into 3 distinct sections:

1. Daily Transaction Spreadsheet.  This shows the total End of Day figures for each column.
2. Tables.  This shows Monthly Totals and changes for each section.
3. Pawn and Sales Activity summary.

To learn about each section, Click on the link above to jump to that part of the article.

 

Accessing the Report

To access this report, click on "Reports" from the Bravo Dashboard:

 

Select "End of Month" from the Closing Reports section of the Reports Screen and then Click Preview on the right. Or you can double click on the End of Month line:

 

Enter your date range, choose whether to show the Daily Section (the day by day rows at the top of the report), and if the report will have row shading (specific sections), then click "OK"

 

The report will look like this:

 

 

There are several options on this page:

1. Print the report to the Reports printer as set up in Bravo
2. Flip from page to page in the report.
3. Zoom in to the report.
4. Save the report as one of the formats listed under the drop down menu (**the email icon also saves a file)
5. Display the report in a new window.

If you are saving the report, you can choose one of the following file formats:

 

 

End of Month - Section 1

The End of Month report creates a spreadsheet, with one row for each day in the date range. The amounts total to the End of Day report figures for a given day.

This section is a CASH FLOW report. Only meant to show what was received in the store on a given day. Not what the store should report on to the government. This section is not meant to be compared to any other report. That would be Section 3.

 

This daily sheet has the following columns:

• Date
  • Date of Transactions
• Principal Redemptions, Renewals and Payments
  • Principal Payments received on Redemptions and Renewals
• Interest and Fees
  • Interest and Fees Collected (Redemption, Renewal, Extension)
• Loans
  • Total Amount of new Loans started
• Buys
  • Total Amount of new Buys
• Taxable Sales
  • Total amount of Sales that are subject to tax (Tax not included in this column).
    • This will also include Sales amounts collected on taxable Hold Sales.
• Sales Tax
  • Total amount of Sales tax collected - including finalized layaways.
    • This will also include Sales Tax amounts collected on Hold Sales.
• Nontax Sale
  • Total amount of Sales that are subject to tax (Tax not included in this column).
    • This will also include Sales amounts collected on taxable Hold Sales.
• Layaway/Repair Payments Less Tax
  • Layaway and Repair payments and deposits.  (No tax included in this column, even if repair is taxable, or layaway is finalized
• Cost of Sales
  • Cost of Goods sold
  • NOT counted toward the Cash On Hand Total
  • This field is replaced by Inter-Store Payments in the event of a payment received.
• Inter-Store Payments
  
  • Number of payments received by another store for the current one.
  • This field will REPLACE Cost of Sales only if there is an Inter-Store Payment to see for the date range ran.
• Credit
  • Store credit paid out to customers
• Cash In
  • Transfers of cash into the store (Bank to Safe or Inter-Store)
• Deposits & Paid Outs
  • Total of transfers of money out of the store (Safe to Bank or Inter-Store Transfers) and all Cash Disbursements made.
  • Includes Consignments Paid when applicable, but Consignments Paid are not part of the Deposits and Paid Outs spreadsheet. That report is a pure transfer of money and Cash Disbursements.
• Cash Over/Short
  • Any variance between what is expected at the end of the day and what is actually counted.
• Cash on Hand
  • Ending cash at the end of the day (amount remaining in store)

 

End of Month - Section 2

The Section Section of the End of Month report shows various tables.  This section consists of two pages that look like this:

The tables include:

• Inventory Base
• Loan Base
• Layaways
• Layaway Credits
• Repairs
• Sales
• Consignment Payables
• Firearms

Each table has two columns - The first column is labeled with the last date of the reporting range.  This column represents transactions that took place on the last date of the report's initial date range.  The second column is labeled "Month".  This column represents the data over the entire report range.  No matter how many days you choose to run this report, this column will always be called "Month".

Each table is shown in detail below.

Inventory Base

The Inventory Base Table shows the inventory numbers as they were at the beginning of the report period, changes to the base numbers (Expired Loans, Returns, Shrinkage, etc) and the ending Inventory Base number.

 

Just like all tables in the report, there are two columns.  The one labeled with the date represents changes to the Base number on the last day of Business of the report range only.  In this example, the date is 12/31/2017.  The Starting Inventory Base number under the date column represents where the inventory stood at the beginning of business on that date.  

In contrast, the column labeled "Month" shows the whole report range.  The Starting Inventory Base number in this column represents where the inventory stood at the beginning of the reporting date range.

The ending numbers are the same.  This is because the first column represents where the inventory ended at the end of business on this date.  Since that is the last date of the reporting range, the Ending Inventory Base number for the Month is the same.

To duplicate this calculation, you will need to do the following:

• Start with Starting Inventory Base
• Add Buys, Bought Loans, Trade-Ins
• Add Expirations
• Add Returns
• Vendor Purchase/Return (header)
  • Add Vendor Purchase
  • Subtract Vendor Returns
• Add Transfers in
• Subtract Transfers Out
• Subtract Police Confiscations
• Subtract Sales
• Subtract Shrinkage
• Subtract Donations
• Subtract Refined
• Cost Adjustments will be either added or subtracted depending on the number (Add positive numbers and subtract negative numbers)
• Voids/Other will be either added or subtracted depending on the number (Add positive numbers and subtract negative numbers)
  
  • This value comes up if there is a void that took place and/or as a balance account if something happened in the system that made it so the Ending Inventory Base would be off compared to what the actual cost of items are.
    • EX: The same item got converted to a SKU twice by two different users. The cost of items would go up by the value of one of those items, so the Voids/Other field would compensate to allow the system to still show the accurate Ending Inventory Balance.
• Transfers Out, In Transit will be either added or subtracted depending on the number (Add positive numbers and subtract negative numbers)
• End with Ending Inventory Balance
• Transfers Out, In Transit Balance is the Balance as of the End Date of the report of all items that have been transferred out of this store and have yet to be received in the other stores

You can see this report in more detail by looking at the Inventory Base Report.  You can read about this report here .(Link opens in new window).

 

Loan Base

The Loan Base Table shows the outstanding loan amount as it was at the beginning of the report period, changes to the base number (New Loans, Expired Loans, Redemptions, etc) and the ending Loan Base number.

Just like all tables in the report, there are two columns.  The one labeled with the date represents changes to the Base number on the last day of Business of the report range only.  In this example, the date is 12/31/2017.  The Starting Loan Base number under the date column represents where the Loan Base stood at the beginning of business on that date.  

In contrast, the column labeled "Month" shows the whole report range.  The Starting Loan Base number in this column represents where the outstanding loan amount stood at the beginning of the reporting date range.

The ending numbers are the same.  This is because the first column represents where the loan base ended at the end of business on this date.  Since that is the last date of the reporting range, the Ending Loan Base number for the Month is the same.

To duplicate this calculation, you will need to do the following:

• Start with Starting Loan Base
• Add New Loans
• Subtract Redeemed Loans
• Subtract Principal Payments
• Subtract Bought Loans
• Subtract Expired Loans
• Subtract Confiscated Loans
• Add Reactivated Loans
• End with your Ending Loan Base total

You can see this report in more detail by looking at the Loan Base Report.  You can read about this report here .(Link opens in new window).

 

Layaways

The Layaway Table shows both the layaway balance (amount still owed) as well as the Layaway Deposits (amount already received).

There are two sides to this table: Layaway Balance and Layaway Deposits.  Each side hase two columns, just like all tables in the report.  The one labeled with the date represents changes to the Base number on the last day of Business of the report range only.  In this example, the date is 12/31/2017.  The Layaway Balance or Layaway Deposit numbers under the date column represents where the Layaway balance/deposits stood at the beginning of business on that date.  

In contrast, the column labeled "Month" shows the whole report range.  The Layaway Balance and Layaway Deposit numbers in these columns represents where the layaway amounts stood at the beginning of the reporting date range.

The ending numbers are the same.  This is because the first column represents where the layaway balance/deposit numbers ended at the end of business on this date.  Since that is the last date of the reporting range, the Ending balance for the Month is the same.

For the Layaway Balance, you can calculate the Ending Balance by doing the following calculations:

• Starting Balance (Layaway Sales Amount Outstanding)
• Add New Layaways
• Add New Layaway Taxes
• Subtract Down Payments
• Subtract Redemptions
• Subtract Expirations
• Subtract Cancellations
• End with Ending Balance

For the Layaway Deposits, you can calculate the Ending balance by doing the following calculations:

• Starting Balance (Money Received)
• Add Down Payments
• Add Payments
• Subtract Redemptions
• Subtract Expirations
• Subtract Cancellations
• End with Ending Balance

The money from Layaway Deposits will be subtracted as the layaway is redeemed.  Once a layaway is redeemed, it no longer shows in Layaway Deposits or Layaway Balance.  This number will now be a sales figure.

For more information on the Layaway Balance Report, please see the article here . (Link opens in new window)

For more information on the Layaway Deposits Report, please see the article here . (Link opens in new window)

 

 

Layaway Credits

The Layaway Credits Table shows the total credit balance (credits owed to customers but not paid) and the paid credits.

To get this balance, take the starting balance, add new expiration credits and subtract any credits paid out.  This will give you the ending balance for the report.

Just like all tables in the report, there are two columns.  The one labeled with the date represents changes to the balance on the last day of Business of the report range only.  In this example, the date is 12/31/2017.  The Starting Balance under the date column represents how much was still owed in Layaway Credits at the beginning of business on that date.  

For more information on Layaway Credits, please see the article here . (Link opens in a new window.)

Repairs

The Repairs Table shows the total number of repairs completed, and the repairs deposits.

Just like all tables in the report, there are two columns on each side.  The one labeled with the date represents changes to the balance on the last day of Business of the report range only.  In this example, the date is 12/31/2017.  The Starting Balance under the date column represents how much is in the Repairs Deposit account at the beginning of business on that date.  

For more information on the repair module, please see the article here . (Link opens in new window)

 

Sales

The Sales Table shows the total number of taxable and non-taxable sales, layaways and fees for the reporting range.

The total sales figure includes taxable sales, taxable layaways (completed layaways that were not tax exempt), taxable fees, non-taxable sales, non-taxable layaways (completed layaways that were tax exempt), and non-taxable fees.  Gift card sales are the bottom line of the report, but are not included as part of the sales total on this table.  (**NOTE** Gift Card sales are included in the non-taxable sales column of section 1.  This may be one reason why the non-taxable sales numbers don't seem to match between section 1 and section 2 of the End of Month report.)

Just like all tables in the report, there are two columns.  The one labeled with the date represents the sales that occurred only on the last day of Business of the report range.  In this example, the date is 12/31/2017.  The numbers under the month column represents the sales figures over the entire range of the report.

This table is generated from the Sales Accounting Report.  For more information on the figures in this table, or on the Sales Accounting Report, please see the article here . (Link opens in new window.)

 

Consignment Payables

The Consignment Payables Table shows the balance for Consignments, including the consignments sold cost, the amount paid to the Consignor and the ending balance for the reporting range.

 

Just like all tables in the report, there are two columns.  The one labeled with the date represents the consignment balance on the last day of Business of the report range.  In this example, the date is 12/31/2017.  The numbers under the month column represents the Consignment Balance figures over the entire range of the report.

• Starting Balance
  • Quantity and Amount of Consignments that are available for payout to the consignor.
• Consignments Sold Cost
  • Consignments that have been sold and are awaiting to be paid out to the consignor.
• Consignments Paid
  • Money given to consignor after item sells and consignor collects their money for that day/month
• Ending Balance
  • Consignments left that are available for payout.

Note that the Consignments Sold Cost may also contain Consignments that have been paid in the same day. For example, if you sell an item that was on consignment it will appear on the End of Month under Consignments Sold Cost, but if you also payout the consignor that same day, the Consignments Paid will have a negative amount for the same $ amount to show that the consignor has been paid. Making the Ending Balance the same as it started that day.

For more information on Consignments, please see the article here . (Link opens in new window.)

 

Firearms

The Firearms Table shows the balance for firearm holding sales, including firearms put on hold, firearms denied, pawn items on hold, pawn items denied and the ending balance for the reporting range.

Because of regulatory holds, firearms cannot leave the store the same day they are sold (in most jurisdictions).  These firearms will go on hold, and are not technically considered sales until they are picked up by the buyer.  Likewise loans are not considered redeemed until they are picked up.

This table helps to track the amount of items on hold, the total of items picked up, and the total of items waiting for pick up for your store.  

There are two sections to this report.  On the left, Firearm Sales Holds are listed.  On the Right, Firearm Pawn Holds are listed.

Just like all tables in the report, there are two columns on each side.  The one labeled with the date represents the firearms hold balance on the last day of Business of the report range.  In this example, the date is 12/31/2017.  The numbers under the month column represents the firearm Balance figures over the entire range of the report.

For more information on the firearm redemption process, please see the article here . (Link opens in new window.)

 

End of Month - Section 3

The third and last section of the end of month report shows the details of the Pawn Activity and the Sales Activity tables.  These two pages are identical to the first two pages of the End of Day Consolidated report. For more information on the End of Day Consolidated report, see the article here . (Link opens in new window).

Pawn Activity

 The Pawn Activity page shows all of the various pawn totals.  It is broken out into several sections.

• New Loans, Buys, Trade-Ins, Bought Tickets, Bought Tickets via Inter-Store Transactions (Total Out)
• Renewals, Partial Payments, Extensions, Redemptions (In-Store and Inter-Store) (Total In)
• Reactivated Loans, Expired Buys/Trade-Ins, Expired Loans

The first section details all of the money that your store putout in Pawns/Buys and Trade-Ins for the reporting range:

On this section, you can see the total number of transactions, the Loan/Buy amount Paid out, any up-front fees collected, and a net total paid out.  There is a section for all transactions that came in through your store directly, and if you are a multi-store company utilizing inter-store transactions, you can see a separate line of any bought tickets that happened at any other store.

The next section shows the pawn payment activity.  This is the total amount taken in for Renewals, Partial Payments, Extensions and Redemptions:

In this section, you can see the total number of transactions, payment totals for Renewals, Extensions, Redemptions and Partial Payments.  You can see the loan principal involved, the break out of Principal Payment, Interest and Fees, any Miscellaneous Charges and the total collected by type. Any MobilePawn payments will also be shown in the In-Store Txns section and will be broken down at the bottom of this report under MobilePawn Activity.

If you are a multi-store company and utilizing inter-store transactions, you can see a detail of the transactions that came into your store, as well as the transactions that have come into other stores for your store's loans.  

The Bottom line shows the overall total of money taken in.

The last piece of the Pawn Activity Summary shows quantity and totals for all Reactivated Loans, any Expired Buys or Trade-Ins, and for Expired Loans.

 

Sales Activity

 

The Sales Activity section of the End of Month Report will show a detail of all of the sales taken in for the reporting range.

The first part shows a total of all taxable sales, returns, non-taxable sales and returns, and taxable and non-taxable consignment sales and returns.

You are able to see the total quantity of transactions as well as sales that came in via the web vs the store, and if there were any shipping and fees involved.

The total at the far right represents gross sales for the reporting range.

The above figures do not include layaways, scrap sales (refined items) or firearms.  Because layaways are not considered final sales until the final payment is received and items are picked up. Scrap Sales are considered refined and not a true sale that would be recorded here. Trade-Ins that exceeded the sale amount will appear under Taxable Returns as a negative value. Firearms are not considered final sales until the background check is passed and the item is picked up, these two numbers are broken out to their own section of the report:

Layaway Sale Redemption = all layaways with final payments received and items have been picked up.

Firearm Sale Pickups Redemption = all firearm sales that have been approved by the ATF and the items have been picked up.

The sales total on this line represents all sales INCLUDING layaway and firearm redemption.

 

At the bottom the report shows the Cost of goods Sold, and Consignment Payables.  

By subtracting this amount, you will get the Net Revenue (Profit).  There are separate lines to provide visibility to the Sales Tax collected, the Gift Cards sold and any Firearm payments that have been received, but not finalized (Picked-Up).

Sales Tax shown in this section does NOT include Hold Sales or Layaway payments. Only items that have been picked up have the Sales Tax information shown in this section. Use this Sales Tax figure to report on what you collected for sales on this day.

 

At the very end the report shows MobilePawn Activity.

The numbers shown here will also be found in the Pawn Activity under "In-Store Txns" from earlier in the End of Month report. This is just to show the breakdown of what payments were done through MobilePawn

 

 

If your store is set up to do EXTERNAL charges, meaning the customer pays for the Convenience Fees, your profit will show the same as the Total Payment.

If your store is set up to do INTERNAL charges, meaning your store is paying for the Convenience Fees, the profit amount will be reduced by the Amount of MobilePawn Convenience Fees charged.