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!
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
modelling cycles
- Thread starter dhazi
- Start date
Robin McLean
New member
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
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
| years | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 |
| cost of replacement lights | $0 | $233 | $222 | $212 | $202 | $192 | $183 | $174 | $166 |
| Total Hours | 4380 | 6570 | 8760 | 10950 | 13140 | 15330 | 17520 | 19710 | |
| Number of replacements | 0 | 1 | 1 | 2 | 3 | 3 | 4 | 5 | 5 |
| New bulbs | 1 | 0 | 1 | 1 | 0 | 1 | 1 | 0 | |
| Cost of replacement bulbs | 233 | 0 | 212 | 202 | 0 | 183 | 174 | 0 |
| 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) |