Excel Sheets Automation with Macros

I need help creating new Excel sheets and implementing various functionalities.

Key tasks include:
- Data entry and validation
- Implementing complex formulas for calculations
- Creating macros for automation

Ideal skills and experience:
- Advanced Excel proficiency
- Experience with data validation and complex formulas
- Strong knowledge of Excel macros and automation techniques.
Please see attached.

Brief: A local small retail company needs a spreadsheet to record their Customers (10), Products (20) and Sales (10). Give a short description of the problem and a proposed solution, identifying the source of data that will be used and explain how the client requirements will be met. The company currently has paper forms for customers to fill in to complete orders. All information is currently handwritten in books, there is no central location for all of the company's important information. You have been asked to create a spreadsheet that will allow the company to record important customer information, product information, and calculate VAT liability.

PART A - Project - Design - Assignment - Word Document (15 marks)
1. Explain how customer information will be recorded in the new spreadsheet. Identify a source of data to use in the new spreadsheet. (5 Marks)
* What information will you use to create the Customer worksheet? (for example current manual customer Information forms being used by the company)
* What columns will you need to include to capture all the information?
* Identify how data will be collected going forward.
* What formulas will be used to make populating the information easier?
2. Explain how product information will be recorded. Identify a source of data to use in the new spreadsheet. (5 Marks)
* What information will you use to create the Product worksheet? (for example current Supplier invoices, Product Information, VAT Paid
* What columns will you need to include to capture all the information?
* Identify how data will be collected going forward.
* What formulas will be used to make populating the information easier?
3. Explain how Sales will be recorded. Identify a source of data to use in the new spreadsheet. (5 Marks)
* What information will you use to create the Sales worksheet? (for example Invoices, customer order forms, supplier invoices, VAT calculations)
* What columns will you need to include to capture all the information?
* Identify how data will be collected going forward.
* What formulas will be used to make populating the information easier?

PART B - Project - Implementation - Business Operations Spreadsheet - Excel (33 marks)
You now must implement the Business Operations Spreadsheet and set it up with all the required worksheets, information, formulas and macros detailed below. A project template has been provided to you to assist you with completing this task.
1. Set up the relevant data and fields in each worksheet (5 marks):
* Customer
* Products c.
Sales
1. Set up the relevant formulas and functions in each worksheet to bring efficiencies to the company's process for tracking customers, sales and products. Formulas to include are VLookups, SUM, VAT Calculations and any other formulas you feel would benefit the company. Suggested functions to add have been included in the Project template in each worksheet. (5 marks)
2. Customise and format the worksheets design to include the company logo, relevant header and footers, Title Rows to make the information easy to read and follow. (5 marks
3. In the worksheet 4. VAT Calculation, record a macros that will record all steps taken to calculate the VAT Liability. (7 marks)
4. In the worksheet 4. VAT Calculation, use a simple IF statement to determine whether the organisation is due a VAT refund. (4 marks)
5. Before you make the modifications, print the entire workbook (all spreadsheets), showing headers and footers into a PDF document. Save the PDF document and call it Event ID XXXXX - Your Name - Project - Before Modifications and submit it with your project. (2 marks)
6. Make 2 modifications to improve the workbook (5 marks)
* Include Filters & Sorting (mandatory); and one of the following
* Freeze Panes, Graphs, Formatting, Functions (Date & Time), Preformatted template etc.
PART C - Project - Modification - Assignment - Word Document (2 marks)
Discuss two modifications you could make to the spreadsheet and explain why they would be beneficial to the company. You can add this to the same document you answered Part A in.
1. Discuss why adding Filters & Sorting (mandatory) would be beneficial to the company and which worksheets you would apply this modification to and why; and one of the following.
Discuss another modification you would add and explain why. For example you might modify by using Freeze Panes, Graphs, Formatting, Functions (Date & Time), Preformatted template etc.
You need to submit the following documents for your project submission:
1: Business Operations Spreadsheet - Excel file - Pre-Modification.
2: Business Operations Part A & Part C - word document.
3: PDF Print out of Business Operations Spreadsheet - prior to modification
4: Business Operations Spreadsheet - Excel file - After-Modifications

Looking for a freelancer who can deliver efficient and well-structured Excel sheets tailored to my needs.