Excel Based Customer Resources Management System

Brief

For a small company who has neither CRM system nor IT support, what is the proper solution for it to manage the customer information and the project profiles? Will it be possible to support the request by using MS Excel? The answer is yes. My solution is share the information between different files using macro to build up the linkage. Reference to the exciting profiles and eliminate the multiple data entry and redundancy. A summary report will be generated automatically to consolidate the key elements from each project profiles.

Customer request

1. My client who has an excel file containing customer contact information, wants to eliminate the data entry when setting up the project profile. They refused to use any data base since they are lacking of IT support.
2. The project profile must have a separate sheet to record the project status which must be protected from being erased or deleted.
3. Different user will be assigned to different role to update the proper information.
4. A project summary report must be generated automatically to consolidate all the information from each project.

Solution:

• Create a share folder in server and assign access to different users
• Save the existing customer profile in the share folder and named it as “Contacts List”. Don’t change the file name. This file will be protected by password which will be controlled by the authorized user.
• Create a folder named “Proj Profiles” under the share folder to save the project profiles.
• One Excel file for each project with the naming convention like “yyyymmdd-Company Name-Project Name-Project Category”.
• Every time needs to run the project summary, users need to open the file named “Projects Summary” in the share folder. The system will consolidate the report automatically by running macro from the backend prompting by the message “Done with the consolidation!”.
• Need to save the result in the folder named “Project Summary Logs” ending with the date when running the report. Suggest saving as xlsx to remove the macro.

Details in project profile:

• Contact List: Customer information for this project. All the record will be protected by password after the file being saved. The columns in this sheet will be:

1. Contact: Dropdown list refer to the Contact List. User needs to choose the name from the list. No data entry required.
2. Company: Refer to the Contact List filling by clicking the button of “Filling Inform”
3. Title: Refer to the Contact List filling by clicking the button of “Filling Inform”
4. Business Type: Refer to the Contact List filling by clicking the button of “Filling Inform”
5. Phone: Refer to the Contact List filling by clicking the button of “Filling Inform”
6. Cell: Refer to the Contact List filling by clicking the button of “Filling Inform”
7. FAX: Refer to the Contact List filling by clicking the button of “Filling Inform”
8. Email: Refer to the Contact List filling by clicking the button of “Filling Inform”
9. Role in the Project: Enter by the user.

• Project Log: User needs to fill in with the project status updates and save the they as log record which are not allow to revise nor delete after file saving.

1. User needs to update the project status by choosing the value from the dropdown list at cell A3. The meaning of each value in the dropdown list is: I：Initiate. Star from the first contact; end with the time official quotation provided. Q：Quotation. Start from the time official quotation provided; end with the point of contract signed. O：Order Preparation. Starting from the point of contract signed; end with the effective date of the contract. F：Order Fulfillment. Starting from the effective date of the contract; end with the SA gained. S：Service. Staring from the SA gained; end with the last payment received. P：Post Service. Any service after the last payment received
2. Each log record must have date associated which must be in format as yyyymmdd. No log record can be revised after file saving

• Financial Summary: Records for each payment. Log file cannot revised after file saving.

1. The payment cannot be blank for each line even if the line is for capturing the project amount. Need to enter 0 for this case.
2. Type of the payment must be chosen from the dropdown list
3. All the cells in orange, columns G, H, I and Cells A3, A7, will be generated automatically. Do not erase the formula in these cells.

• Quotation: Suggest pasting each official quotation in one sheet. Name the sheet as “Quotation yyyymmdd”
• Contract: Suggest pasting the contract in this work sheet
• Amendment: Suggest pasting the contract in this work sheet