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!
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
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)
Posting Permissions
Reply With Quote
Bookmarks