Validating Data on a spreadsheet to produce a score for a business project

We are a company who uses a merchandising (in store vendor) to manage a retail network to maintain/order our products and our displays. Currently we receive a report with a line item for each store and we believe that we can manage this better with finding someone who can create formulas to validate the data they are sending us to grade each store so we can find the weak stores. Once we have achieved the basic result we'd like to expand on this in future with the same freelancer to possibly design a GUI / database for this data so we can better analyse it in future. Overall Goal: Improve in-store vendor performance using spreadsheet provided by merchandising company Short Term Goal: Grade the merchandisers performance on a weekly basis Gather data to do random spot checks on stores Stretch Goal/Long Term Goal: Long term data analysis on merchandiser performance by store Manage sales trends Find poor performing stores What to filter Out: Dates to separate visits week by week Remove duplicates Data to retrieve and associate a score to Standard Questions: Number of stores covered that week Number of orders/value that week Out of stocks entry/exit Y/N Order Value PO Number High Priority: Display stand Marketing Material Y/N Functioning Display Y/N Have you shown someone the training video Y/N Has any team member received any training Y/N Physical Stock Count X/X 0 1 2 3 4 5+ Did you take a photo - How do we link this Low Priority Did you speak with someone Y/N Tool Shop or Service Counter Tool/Service Results required Each store can be scored individually: Information is based on High priority checklist Each item Physical stock count 0 to 4 Flags for any N answer on High Priority to contact store - Graded by colour RED - ORANGE - YELLOW - GREEN Overall grade for the week To be discussed May require manual copy and paste to get those scores providing the format is the same Automatic report once the file is downloaded to give a rating Monthly report against stores to check status change or improvement Can we turn this into a pivot table that can handle all the stores and then be updated