Looking for Google spreadsheets Automation

We have retail sales executives who work at retail stores & sell products.These retail executives sell FMCG products like cooking oil,ice cream,cosmectics etc.Retail executive report to Supervisors who fill there opening stock & closing stock for each product in google form.This data is captured in google spreadsheet in backend.MIS team will copy this data & then put formulas in there excel sheet to fetch results like day sales(which is opening minus closing stock),till date sales(sum of everyday sales),target achievement etc.We want someone to create format wherein all these google spreadsheet of different supervisors are linked & data entered by them should come automatically in master google spreadsheet Master sheet should have formulas applied which which should give results as required.Also dashboards should be there in master sheet where changes happen as data is entered by supervisors in google form.Master sheet also highlight wrong data entry by supervisors