EXCEL book programming provided with macros for customer management and invoice and report generation.
The following functionalities are included in the quoted version:
CUSTOMER MANAGEMENT
From the customer master table, with the fields that appear in the example table sent in the file "04_Customer and properties.xlsx", it will be possible to visualize the card of a certain customer, with the possibility of locating it through a form as a search engine, edit its data, register new customers and delete them. In the customer master table, individual customers will be differentiated from companies by means of a specific column. Customers who have more than one property will appear in more than one row of the table, with the same customer name and different property name. The client file will be found on a separate sheet of the book. By means of the SEARCH button a search form will be opened, provided with a filter field, where it will be possible to introduce a word or part of it that is included in the name of the client to be located. Under the filter field, a list of matches will be shown, where you will be able to choose one and transfer the data of the chosen client to the file. By means of the button NEW CLIENT the card will be cleaned so that the user can introduce the data of the new client. By means of the button SAVE, the data of the card will be registered in the master table of clients, either of a new client or of an already existing client, for whom the data are being edited. By means of the REMOVE button, the customer displayed in the card will be removed from the master table, after a confirmation message has been accepted.
CAPTURE OF COMMISSIONS AND CLEANING PRICES PER APARTMENT
The following sheets will be available, where the user will be able to paste, once a month, four data lists: - Reservations (BookingList file) - Booking (rstatement file) - Airbnb (file airbnb_1) - Other portals The four lists must always be pasted into the same cell, respecting the header and data start rows, as well as the position of all columns. By means of the button EXTRA COMMISSIONS the commissions of the sheets Booking, Airbnb and other portals will be transferred to the sheet Reservations, to the client that corresponds in each case, being located this one by means of the name. To do this, the customer's name must be identical on all three sheets. The amount of the cleaning will be taken from the master table of customers and properties (defined for each property). The data captured from the sheets Booking, Airbnb and other portals, will be recorded temporarily in the sheet Reservations, which will automatically add a series of columns to the right of the list, with the commissions and price of cleaning. Since the data will be monthly, the user will paste the four lists and capture the commissions and price of cleaning with a monthly frequency through the method described above. By means of the button CLEAN LISTINGS, located in the Reservations page, the four lists will be automatically cleaned so that the user can paste the following ones, month by month. The data that will be permanently recorded in the book will be the reports generated (Historical bookings sheet) and the main data (no breakdown) of the invoices issued (Historical invoices sheet).
REPORT GENERATION
Once the four listings have been pasted and commissions and cleaning prices have been captured using the procedure described above, the reports can be generated. In the REPORT sheet, the user will be able to choose an owner client in a drop-down menu, and his report will be generated on screen with all the apartments that have been booked in the month, according to the content of the four entry tables. The report will have the following concepts: - Real estate brokerage services - Cleaning Services - Gardening Services - Maintenance service - Decoration service - Urgent service - Supplied - Commission on on-line bail system - Other extra expenses - Initial setup costs The amounts of the first three items will be automatically filled in from the input data. The rest of the concepts can be entered manually by the user. Once the report has been generated and completed, it can be registered in the Reservations History sheet, by clicking on the REGISTER button. The report will be generated on screen, in the report template and will have a design according to the corporate colors of the company. Depending on whether the client owner is an individual or a company, the report will correspond to one model or another (showing prices with or without IGIC). A number will be proposed for each invoice, corresponding to the correlative following the highest number registered, with format AA-NNN where AA is the last two digits of the year and NNN is a three digit correlative number.
