modelling cycles

dhazi

New member
Joined
Mar 20, 2013
Messages
10
Reaction score
0
Points
0
The exercise is to model a recurring cost cycle. In this example there are some light bulbs need replacing periodically based on their usage. I cannot get beyond the first cycle or at least not in a dynamic way e.g. when the bulbs are replaced in y2 the next replacement occurs in y4 etc and although it might sound simple it is not because when the operating hours change the interval of the recurring costs changes too. Anyway, I have attached a spreadsheet with a basic model with minimal moving parts in it and some help would be very much appreciated...since I am stuck. Thanks!
 

Attachments

  • help with light bulbs calc.xlsx
    12.1 KB · Views: 15

Robin McLean

New member
Joined
Apr 27, 2014
Messages
1
Reaction score
0
Points
0
Location
Edinburgh
This can be solved by adding new rows

The first row calculates the number of hours in total used for a light bulb
The second takes the total number of light bulbs used and divides by the hours for each light bulb. This gives the number of replaceents
Subtracting the number of lightbulbs required from the previous month gives the number of new bulbs in each period
Multiplying by the cost of each bulb gives the answer

years123456789
cost of replacement lights$0$233$222$212$202$192$183$174$166
Total Hours4380657087601095013140153301752019710
Number of replacements0 1 1 2 3 3 4 5 5
New bulbs10110110
Cost of replacement bulbs
233021220201831740






Total Hours=C7*$B$3
Number of replacements=INT(C11/$B$5)
New bulbs=IF(C12=B12,0,C12-B12)
Cost of replacment bulbs=ROUND(C13*C6*$B$1,0)
 

Attachments

  • help with light bulbs calc (1).xlsx
    13.1 KB · Views: 7
Top