Google Sheets & BI Expert Needed for Financial Tracking System

---

---

Build Financial Tracking & Revenue Forecasting System for Debt Settlement Company Project Overview We are a debt settlement company seeking an experienced freelancer to build a comprehensive financial tracking and revenue forecasting system from scratch. Our business model involves collecting weekly payments from clients under complex fee arrangements, and we currently rely on manual spreadsheets to track everything. We need a robust solution that can organize our historical data, automate our reporting, and provide clear forecasts as we scale from ~70 clients to several hundred. Background Our company manages client programs with three different fee structures, which makes revenue tracking challenging. Each client is on one of the following plans: Plan 1: 15% retainer fee (paid weekly) + 15% success fee upon debt settlement (the success fee is a fixed percentage of the total enrolled debt, paid out in variable installments once settlements are reached). Plan 2: 30% success fee only (no weekly retainer; the success fee is 30% of the enrolled debt, collected in variable weekly installments as debts are settled). Plan 3: 15% enrollment fee (paid over the initial weeks) + either a 15% settlement fee or a 10% restructure fee (paid in installments over time as each debt is settled or restructured). Because of these models, each client’s payment schedule and amounts can vary greatly. We receive weekly ACH payments from clients and need to allocate each payment to the correct fee type (retainer, success, enrollment, settlement, or restructure). In addition, we pay commissions to our marketing partners (10% of the fees from deals they sourced), which we also need to track. Current Process: We export data from two systems — Forth CRM (client and debt information) and Reliant ACH (payment transaction records) — as CSV/Excel reports. Then we manually combine these to figure out how much revenue was collected, what is still owed, and what to expect in the future. This manual process is tedious and error-prone. We want to streamline and automate this reporting. (In the future, these systems may offer API access, so the solution should be designed to easily integrate APIs when available.) Objectives The new tracking and forecasting system must enable us to: Track all incoming payments by client and by fee type (retainer, success, enrollment, settlement, restructure, etc.). Monitor revenue on a daily, weekly, and monthly basis with up-to-date totals. Calculate total revenue collected vs. outstanding amounts (how much of each fee type has been paid vs. how much is still to be collected for each client and in aggregate). Forecast future revenue on a weekly and monthly timeline, based on each client’s payment plan and progress (e.g. predict when success fees or settlement fees will likely be received). Track marketer commissions (10% of relevant fees) to see how much we owe to each marketing partner based on the deals they brought in. Ultimately, we want a clear picture of our cash flow and revenue projections, which will help in decision-making and identifying any issues early (like clients falling behind on payments). Scope of Work We are looking for a freelancer who can take ownership of the entire build. Specifically, you will be responsible for: Platform Recommendation: Evaluating and recommending the best platform or combination of tools for our needs. This could be Google Sheets with Looker Studio dashboards, Microsoft Power BI, Tableau, Zoho Analytics, or another suitable platform. The key is that it should be user-friendly, scalable, and easy to maintain for a non-technical team. Data Architecture & Setup: Designing and implementing the data structure to organize all information. This includes: Creating a centralized payments table that consolidates historical payment data from our CSV reports (and is ready to ingest new data regularly). Setting up client profile records that include each client’s details, their fee structure (which of the three plans they are on), total enrolled debt, start date, etc. Encoding the fee schedule logic for each plan so that the system knows, for example, when a success fee should be counted as earned/paid or how an enrollment fee is distributed over time. Incorporating marketer attribution, linking clients (or deals) to the marketers who referred them, so the 10% commission can be calculated for each marketer. Dashboard & Report Creation: Building interactive and easy-to-understand visual dashboards and reports that allow us to monitor: Revenue Collection: Daily/weekly/monthly revenue received, broken down by fee type and in total. Outstanding Amounts: How much money is still expected from each client and in total, segmented by fee type (e.g., seeing that Client X still owes $Y of success fees). Forecasted Revenue: Projections of how much revenue we expect in upcoming weeks and months, based on clients’ payment schedules and anticipated settlement dates. This might involve projecting when success fees or settlement fees will come due. Marketer Commissions: A report or dashboard section showing commissions accrued for each marketer (and total commissions payable), updating as revenue comes in. Forecasting Logic: Implementing the calculations or models to forecast future revenue. This will likely involve: Utilizing each client’s current payment status and fee plan to project their future payments. For example, if a client is paying a weekly retainer, the system projects those weekly retainer payments forward. If a client has a success fee due, the system might project when that will be paid based on the client's program timeline or settlement progress. Allowing for adjustments or assumptions (perhaps a way to input expected settlement dates or completion percentages to improve forecast accuracy). Summing individual client forecasts into an overall revenue forecast for the company. Automation & Scalability: Ensuring the solution requires minimal manual effort to update: Setting up easy import processes (like a defined CSV format we can drop in, or scripts to load the data if using a tool like Power BI). If using Google Sheets, possibly using Google Apps Script or other means to automate data refreshing. Designing the system so that adding new clients or changing a fee structure is straightforward. Building with the future in mind: for instance, placeholder or pipeline for direct API integration once available, so we can eventually automate data retrieval from Forth CRM and Reliant ACH. Testing and Validation: Thoroughly testing the system with our historical data to ensure accuracy. We should be able to reconcile the numbers from the new system with our existing spreadsheets to verify that all calculations (revenue, outstanding balances, commissions, forecasts) are correct. Documentation & Training: Providing clear documentation on how the system is built and how to use it. This includes: A guide on how to import new data (for now, via CSV exports from our CRM and ACH portal). An explanation of all calculated fields and metrics (so we understand where the numbers come from). Instructions for maintaining the system or making minor tweaks (for example, if we introduce a new fee structure or want to adjust a report). If applicable, a brief training session or walkthrough for our team via Zoom or a recorded video (not required but would be appreciated). Throughout the project, we'll expect regular communication, especially in the initial design phase, to make sure the framework you set up captures all our requirements. Preferred Platform & Tools We are currently inclined to use Google Sheets coupled with Google Looker Studio (formerly Data Studio) for the dashboards, mainly because of ease of use and our familiarity with these tools. Many of our internal processes are already in Google Workspace, and we appreciate the sharing and collaboration features of Google’s platform. However, we are open to other solutions if they offer significant advantages. For example, Microsoft Power BI or Tableau might handle larger data sets and complex calculations more efficiently, and Zoho Analytics or other BI tools could offer built-in connectors to our systems. Part of your role will be to help us decide on the best tech stack: balancing usability, scalability, cost, and the effort of implementation. If you recommend a platform outside of Google Sheets/Looker Studio, please ensure it still meets our criteria (easy web access for our team, can be updated without specialized software, etc.). Required Skills & Experience To be a successful candidate for this project, you should have: Financial Data Expertise: Strong experience in building financial tracking, accounting, or revenue management systems. You should understand concepts like revenue recognition, accounts receivable, and forecasting. Dashboard & Data Visualization Skills: Proven ability to create clear and interactive dashboards using tools such as Google Sheets with Looker Studio, Power BI, Tableau, or similar. Please be prepared to show examples of past dashboards or reports you’ve built. Data Integration & Automation: Experience working with CSV/Excel data imports and ideally setting up automated data pipelines. You should know how to transform and clean data from multiple sources into a unified format. Experience with APIs and data integration is a plus (even if we might not use it immediately). Analytical & Problem-Solving Skills: The ability to translate complex business rules (like our fee structures and commission rules) into a working data model. An attention to detail is critical because financial calculations must be exact. Scalability Mindset: Experience designing solutions that can grow. For example, if you’ve built systems that went from handling dozens to thousands of records, mention how you ensured performance and ease of maintenance. Communication & Documentation: Fluency in English and the ability to communicate clearly. You should be comfortable explaining your approach, asking clarifying questions, and documenting your work for others to understand. Bonus Skills/Experience: Familiarity with the debt settlement industry or consumer finance would help you ramp up faster. If you’ve worked with CRM data (especially from systems like Forth CRM) or payment processors (ACH systems) before, or if you have insight into typical fee arrangements in debt relief programs, please highlight that experience. While it’s not required, it would definitely be an advantage. Deliverables By the end of the project, we expect the following deliverables: 1. Fully Functional Tracking & Forecasting System: A complete system (in the agreed-upon platform) that ingests our data and produces the required outputs. This includes all underlying data tables, calculations, and logic for tracking payments, computing outstanding balances, and generating forecasts. We should be able to use this system immediately with our current data. 2. Interactive Dashboards/Reports: A set of user-friendly dashboards accessible online that visualize the key metrics: Revenue Received: How much we’ve collected, with filters or views by date range, fee type, and client. Outstanding Revenue: How much is still expected (unpaid) for each fee category, and which clients have outstanding balances. Future Projections: Graphs or tables showing expected revenue each week/month into the future, based on current client programs. Marketer Commissions: A report showing total commissions earned by each marketer (and possibly what has been paid out vs. pending, if applicable). 3. Data Import Process: A defined process (and any necessary scripts or templates) for updating the system with new data. For now, this might be as simple as “paste new CSV data into Google Sheet and refresh the dashboard,” but it should be well-defined. If using a BI tool, it might involve setting up scheduled refreshes or an easy way for us to upload new files. 4. Documentation: Written documentation (and/or brief video walkthroughs) that explains: How the data is structured (e.g., definitions of each table or sheet, and how they relate). How calculations are performed (e.g., how the success fee forecast is calculated for a client on Plan 2). Step-by-step instructions to add new data each week and to maintain the system. Troubleshooting tips for common issues (e.g., if numbers don’t look right, what to check). 5. Future Integration Plan (Optional): Although not required for the initial build, we’d value a short proposal or recommendations for integrating with Forth CRM and Reliant ACH APIs later. For example, note which parts of the system would benefit most from automation and how you would approach it when the time comes. We consider the project complete when we can take your delivered system, use it with our latest data, and get accurate reports & forecasts without additional development. Additional Information Timeline: We would like to get this system up and running as soon as possible, ideally within a few weeks. However, we are willing to discuss the timeline based on your assessment of the work involved. Quality and accuracy are more important than speed. Communication: We’re available to answer questions quickly throughout the project. We can schedule check-in meetings to review progress and make sure everything is on track. Data Confidentiality: You will be working with sensitive financial data (client payments and debts). We will require standard confidentiality measures (an NDA may be necessary) and expect all data to be handled securely. How to Apply If you’re interested in this project, please include the following in your proposal: Relevant Experience: Briefly describe similar projects you have worked on. Highlight any projects involving financial dashboards, forecasting, or complex data modeling. If you have experience with debt settlement or finance industry data, let us know. Example Work: Provide links or screenshots of dashboards/reports or systems you’ve built in the past that are relevant. Explain what your role was in those projects (did you build the entire system, what tools were used, etc.). Your Approach: Share a short outline of how you would tackle this project. You don’t need to give a full solution, but we’d like to know what platform or tools you suggest, and any initial thoughts on organizing the data and implementing the forecasts. Questions: Feel free to ask any questions you have about our requirements. If anything is unclear or if you have suggestions to improve our plan, we welcome your insight. We’re excited to work with a professional who can bring this project to life. Thank you for considering our job posting, and we look forward to hearing from you!

Keyword: App Marketing

Price: $45.0

Data Visualization Business Intelligence Looker Studio Google Sheets Excel Formula ETL Data Analytics & Visualization Software