1. Automated update of Spreadsheet from Daily Deposits
Narrative: As deposits are made and recorded in Quickbooks, each check or Credit card deposit is applied to one or more invoices. There are occasional exceptions to this rule due to the fact that we receive royalties from Sage and others. But, 98% of the checks we receive are associated with an Invoice and received against an Invoice in Quickbooks when the deposit is recorded. Furthermore, the vast majority of the checks we receive are for Services related invoices. Frequently, a single check from a Customer will cover two or more invoices and when the check is applied in Quickbooks the invoices to which it applies are indicated.
Purpose of Automation: The purpose of this automation is to eliminate a manual and time consuming process whereby a person wishing to update the USG Cash Allocation Spreadsheet must manually:
a) record the deposit in the spreadsheet and note for each check received, which invoices the check paid and the amount of each invoice;
b) determine which invoices were related to Services work performed by a 1099 Subcontractor and set aside $ for payment of the Subcontractor by indicating the amount , the invoice number and the name of the 1099 Subcontractor and adding the amounts to a cell in the spreadsheet representing a running total of $ set aside for Subcontractor payments.
c) Determine which checks are related to Product Sales and set aside $ for Sales Tax payments (if any), set aside $ to Pay Cost of Goods sold, and set aside $ to pay Sales commissions.
d) Determine which checks or parts of checks are related to Prepaid Services and set aside $ for Prepaid Services to be moved out of the Cash Operating Bank account into a Prepaid Retainer Holding Bank Account.
SEE Attached file for the full description with screen shots and examples