Planilha Contas a Pagar e Receber v6.0 (Excel 365): Controle Financeiro Total! | VIZUAL

Tutorial on Excel Accounts Payable and Receivable Spreadsheet

Introduction to the Tutorial

• The video tutorial introduces the Excel 6.0 accounts payable and receivable spreadsheet, specifically designed for Office 365 users.

• Viewers are encouraged to watch the entire video for comprehensive understanding but can also navigate by chapters provided in the description.

Requirements for Using the Spreadsheet

• Users must have Office 365 installed on their computers to utilize advanced features like reports and dashboards that older versions do not support.

• While data entry is possible in earlier versions, tracking through reports requires Office 365.

Overview of Features

• The "Help" menu within the spreadsheet provides essential information, including videos and product suggestions.

• The registration section consists of four tabs: account plan, stores, clients, and years.

Setting Up Account Plans

• Users need to register their account plans which can include both income (receitas) and expenses (despesas).

• There are editable options available: four plans for income and ten for expenses; up to 100 accounts can be registered per plan.

Managing Stores and Clients

• Users can manage multiple stores or branches (up to ten), allowing control over various business locations.

• Client registration allows up to 3,500 contacts categorized as either clients or suppliers, aiding in future data analysis.

Yearly Data Management

• The spreadsheet supports data management across ten years starting from any chosen year; users can input historical data as needed.

Importance of Initial Setup

• Proper setup of account names is crucial as they will appear during transaction entries; this foundational step is necessary before making any financial entries.

Understanding Transaction Entries

• A model is provided for entering transactions related to accounts payable or receivable; each line represents a separate transaction.

Understanding the Spreadsheet Functionality

Overview of Accounts Management

• The classification of accounts as either receivables or payables is essential. Users must select the appropriate account plan, which automatically directs to the corresponding account.

• A control number for invoices or receipts should be added, along with descriptions and amounts. This section allows for interest addition if applicable.

Working with Spreadsheet Protection

• The spreadsheet tabs are generally unprotected, allowing users to modify elements like font color and logos without a password.

• While some cells are protected, they do not have passwords; users can still edit designated areas while maintaining formula integrity.

Data Entry and Control Features

• Certain columns contain formulas indicated by titles; users should avoid entering data in these columns to prevent disrupting calculations.

• Automatic status updates on accounts show overdue days, payment statuses, and upcoming due dates for better tracking.

Utilizing Filters for Data Management

• Users can apply filters to titles in the spreadsheet to view specific information such as upcoming payments easily.

• Clearing filter fields allows users to reset views and access all data again.

Efficient Data Entry Techniques

• To start new entries efficiently, users receive a blank spreadsheet where they can input data directly into a structured table format.

• Navigation buttons allow quick movement between the beginning and end of the spreadsheet for easier data entry management.

Inserting and Deleting Rows Safely

• Users can create multiple rows quickly by dragging from a corner cell after entering initial data.

• Right-clicking within a cell provides options to insert or delete rows safely without damaging existing data structures.

Cautions Against Improper Modifications

• It’s crucial not to insert or delete rows outside of designated tables as it may corrupt the spreadsheet's functionality.

How to Effectively Manage Your Database

Deleting Unnecessary Information

• Emphasizes the importance of deleting unnecessary data to prevent future issues in database management.

• Suggests creating a structured database for account information, which aids in generating reports and dashboards.

• Highlights the need to use correct channels for data deletion to avoid loss of important information.

Understanding Reports

• Introduces four key report tabs: accounts receivable, accounts payable, general, and search functionalities.

• Explains how to analyze accounts receivable by selecting specific years and branches for detailed insights.

• Discusses visibility into total amounts received and pending payments, allowing users to track overdue accounts.

Analyzing Payment Trends

• Notes the significance of tracking delinquency rates (percentage of unpaid accounts), which can inform business strategies for debt collection.

• States that the same principles apply when managing accounts payable as with receivables.

Utilizing Filters in Reports

• Describes how users can filter reports by month or branch to focus on specific financial periods or locations.

• Demonstrates how filters can be applied or removed easily within reports for better analysis of client or supplier payment statuses.

Searching Specific Accounts

• Explains how users can input control numbers to find related account information quickly, enhancing efficiency in locating financial records.

• Stresses the utility of having access to all relevant details associated with an account number, such as invoices or payment receipts.

Dashboard Overview

• Introduces dashboards as a dynamic tool for visualizing financial data related to both receivables and payables.

Understanding the Financial Dashboard

Selecting Stores and Viewing Accounts Receivable

• Users can select all stores or a specific store to view relevant information in the calendar, which highlights days with accounts receivable.

• The calendar provides a quick overview of days with accounts receivable, making it easier for users to track financial obligations.

• Changing the month in the calendar updates the display to show accounts receivable for that specific month, enhancing tracking capabilities.

Analyzing Store Performance

• The dashboard allows users to select a year and store to analyze various metrics such as total accounts receivable, received payments, overdue transactions, and payment rates.

• It includes insights on delinquency rates and identifies the top five stores generating revenue as well as those with the most overdue accounts.

Client and Supplier Filtering Options

• Users can filter data by client or supplier based on their initial registration details, allowing for tailored analysis of financial interactions.

Account Plan Analysis

• The account plan feature enables users to analyze data either by account or by plan; unchecking a box switches between these views.

• When prioritizing by account, results change accordingly. Users can toggle back to view data organized by plan.

Comprehensive Financial Management Tools

• The spreadsheet combines both accounts payable and receivable functionalities into one tool while also offering separate sheets for each function if needed.