Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Revenue and cost calculations based on timing of rollout criteria

Posted on 2014-04-15
10
287 Views
Last Modified: 2014-04-18
Please see attached example.

Based on the month that we will rollout our services to particular companies, I need to calculate how much we will be making in revenue and the cost associated with the deployment of hardware.  The month of the rollout will be the variable that may change so the revenue and costs all need to be tied to this variable.

Revenue:
For each company, revenue will be ongoing after each rollout.  Meaning if we rollout on Month 6 then we will continue our revenue stream on an ongoing monthly basis.  Row 26 in the attached spreadsheet will reflect the total revenue for all companies.

Cost:
The cost is for hardware at rollout and an accrual for ongoing replacement.  The cost is $1k per car in the month of rollout plus $750 for every 20 units that have already been deployed.  Look at:
Row 7 - Car installs - this identifies how many units will be installed based on the month of rollout
Row 8 - Total # of cars - this is a cumulation of all units installed
Row 27 - this is the cost based on $1k per car in the month of rollout plus $750 for every 20 units

I highlighted in yellow the cells that I would need you to calculate (again based on the variable month of rollout).  I manually input the amounts to show what they will need to be.

please let me know if you have any questions.
0
Comment
Question by:mdg1
  • 5
  • 5
10 Comments
 

Author Comment

by:mdg1
ID: 40002856
I also would like to be able to change the cost (charge) at any time going forward.  I imagine I would have to create a new column with the exact info but changing row 24
0
 
LVL 19

Expert Comment

by:Ken Butters
ID: 40004956
there is no attached file.
0
 

Author Comment

by:mdg1
ID: 40005009
ooops - here you go.  thx
Monthly-Model-Example.xlsx
0
Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

 
LVL 19

Expert Comment

by:Ken Butters
ID: 40005106
Not sure I understand exactly what you are going for here... can you post another sheet, what it would look like if Rollout Month is set to 3?
0
 

Author Comment

by:mdg1
ID: 40009212
Hi there, I added a new tab and highlighted in green everywhere the change to month 3 rollout effects.  Let me know if you need more clarification
Monthly-Model-Example.xlsx
0
 
LVL 19

Accepted Solution

by:
Ken Butters earned 500 total points
ID: 40009333
I updated the values  in Row 11...
so for example instead of Month 1  |  Month 2 | Month 3
it now contains values of 1 | 2 | 3 etc.  

That allowed the formulas I added to easily check if I was less than or equal to a particular month.

If that formatting is not acceptable, there are some alternatives... but this approach is the easiest.

I made the updates on the "Original Example" tab.

If you change the values in cells B11 through H11, you should see your other totals updated as well.
Monthly-Model-Example.xlsx
0
 

Author Comment

by:mdg1
ID: 40009636
Works great.  Can I extend my question?  In playing with it, I realize that the variables (% adoption, price, etc) may change after a certain period of time.  So can you work in start AND end months into the equation?  Then I'll add additional columns to show the new variables.

Or should I start a new question for this?

Thanks,
Janice
0
 
LVL 19

Expert Comment

by:Ken Butters
ID: 40009682
If you agree that my answer is correct for the question you asked, I'd prefer to be awarded the points for correctly answering your question...

If you want to expand with another question, I'd be happy to take a look and see if I can answer that as well.
0
 
LVL 19

Expert Comment

by:Ken Butters
ID: 40009686
You can just post the url of your new question into a comment here if you want to "notify" me that you've posted a new question.
0
 

Author Comment

by:mdg1
ID: 40009730
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

808 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question