Results 1 to 2 of 2

Thread: modelling cycles

  1. #1

    modelling cycles

    Register for a FREE account, and/
    or Log in to avoid these ads!

    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!
    Attached Files Attached Files

  2. #2
    Neophyte Robin McLean's Avatar
    Join Date
    Apr 2014
    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)
    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts