Assignment:
- Create a financial model to support the requirements outlined below. A basic P&L statement has been provided to you as a framework.
- Modify and rework the financial model, as required.
- Create supporting tabs and schedules, as required.
- Although multiple people are collaborating in the business case, assume everything can stay in one (1) workbook for the purposes of this assignment.
- Assume that this is a mid-size company of 100-200 employees
Overview:
ABC Inc. is a software vendor that operates with a Software-as-a-Service (SaaS) business model. They have two revenue streams:
- Software Subscriptions
- Services.
Subscriptions are typically sold on a three (3) year term. Services work will follow the original software purchase and are typically completed and recognized over a three (3) month period, but could be more or less based on the amount of work. The CFO needs to create a rolling 12-month financial forecast that can be adjusted and maintained monthly. He collaborates with the VP of Sales and his Financial Analyst to bring this information together. As of Dec 2015, ABC Inc. has 150 customers that generate 6.75MM annually in subscriptions. Assume these customers all have subscriptions extending beyond 2016 and that the services component is complete as of Dec 2015. The VP of Sales is planning to add an additional 60 customers in 2016 with a high-level annual contract value target of approximately 3MM for the new subscriptions.
Problem:
-The CFO needs to create a financial Profit & Loss forecast for Jan to Dec 2016. This should follow IFRS.
-The VP of Sales needs to be able to plan new deals that are in the Sales pipeline. She wants to be able do the following:
o Enter a customer prospect name and description of the opportunity;
o Enter a commit date for when the deal is expected to sign;
o Enter a total contract value amount for the subscription as well as the number of months in the subscription term;
o Enter a total Services amount for the opportunity;
o Adjust a flag or toggle of some sort to include or exclude any given opportunity within the rest of the financial forecast.
- The forecasting model should calculate subscription revenue based on the inputs provided by the VP of Sales (as noted above), starting from the month the deal is signed.
- The forecasting model should calculate the Services revenue recognized based on the inputs provided by the VP of Sales (as noted above), starting from the month the deal is signed. The CFO wants to assume $25,000 of Services can be consumed per month, but this shall be adjustable within the model. For instance, if the services for a deal is $85,000 it should be recognized like this:
| Total Services |
Month 1 |
Month 2 |
Month 3 |
Month 4 |
| 85,000 |
25,000 |
25,000 |
25,000 |
10,000 |
- The Professional Services costs are outsourced and are projected as a percentage of Services revenue. 70% is the default but this should be adjustable within the model.
- Hosting costs for the software is a combination of fixed and variable. Fixed hosting costs shall be an input entered by the Financial Analyst. The variable costs are defaulted as $300/month per customer.
-Employee salaries, benefits, outside services, and office expenses are all currently inputs by the Financial Analyst at this time but the CFO will want these to have more robust planning models in the future.
Measurement criteria for this assignment:
-Model quality - Meets the business requirements outlined above, accuracy, integrity and completeness of formulae and business logic
-Aesthetics - Well formatted, intuitive, easy to use, pleasing to the eye, using advanced Excel functionality where appropriate (creativity welcome)
Attachment:- Modeling_Test.xlsx