BYU Student Author: @Benjamin_Lau
Reviewers: @Dallin_Gardner @Carter_Lee
Estimated Time to Solve: 50 Minutes
This challenge was written in partnership with an M&A transactional advisory professional. The data, scenario, or both were inspired by real-world business scenarios.
We provide the solution to this challenge using:
- Excel
Need a program? Click here.
Overview
As a member of the M&A transactional advisory team within a national accounting advisory firm, you have been tasked with assisting your supervising senior in conducting a comprehensive revenue waterfall analysis for a medical hospitality firm. This analysis is crucial in evaluating the revenue recognition process. You are provided a dataset with all the client’s information, the relevant dates, and dollar amounts for the analysis. This is a light version of what you do in an M&A revenue analysis. It is a test of understanding data structures and Excel functions. The usage of Excel shortcuts will be greatly beneficial.
Instructions
- Copy and paste all the names from the “RawData” sheet to the appropriate column on the “Days of Admission” sheet. Do not attempt to use lookups or remove duplicates because some patients share the same names, and some patients are admitted twice in the same year. Your task will be easier if you treat each row as a sale to each individual.
- Create a tally tracker for each patient. The “Days of Admission” sheet contains all the dates in 2023. If the patient is in the facility on a certain date, you would put a tally on that date. For example, if Mia Anderson has stayed in the facility on 1/1/2023, the cell under 1/1/2023 for Mia Anderson should be 1. Otherwise, it should be 0. This is very useful for the calculation we do in the next step to create a revenue waterfall table.
- Go to the “Revenue Waterfall” sheet. Sum up how much revenue should be recognized for each patient each month. As you notice the headings are in months now instead of days, be cautious in implementing your functions in capturing the revenue that should be recognized for each patient.
- On the far right of the “Revenue Waterfall” sheet. Fill out the columns Total Revenue, Paid, and A/R (Unearned Revenue). On the bottom (row 556) far left, fill in the amounts for A/R, Unearned Revenue, and Net.
Data Files
Suggestions and Hints
- When working on task 2, make sure you use $ for absolute or mixed cell references, so you can easily drag the formulas without any issue.
- Consider using IF and AND functions. Think through how to construct the tally tracker – what counts as a day the patient is in the facility. It should be days between the admission date and the departure date, including the admission date but excluding the departure date.
Solution