Weekday Hotel Pick Up Analysis Report

We are in need of an Excel report that can consolidate data from a few reports that can be run by macro, pivot, or formula by integrating these original reports easily. The report will show what picked up night before for upcoming 90 and 365 each week day for all days of week with a breakdown (what picked up and at what rate).