Takt Time Finder

What is Takt Time? Takt time is the rate at which you need to complete a product in order to meet customer demand. It comes from the German word “Takt,” meaning beat or pulse in music. This is the heartbeat and rate of time between product deliveries.

Purpose: The purpose of this project was to create a program that would print a takt time pattern by calculating a repeating pattern of time (number of whole integer days) between product deliveries. The takt time is simple to calculate when you need to produce 120 products in a year and have 240 working days to do it. You simply need to complete a product every other day. But what happens when you need 73 products and have 240 working days? You need to complete a product every 3.288 days or as this logic will find, you need to follow a repeating pattern of 3,4,3,3,4,3,3 days between product deliveries to make it by the end of the time period. This repeating pattern works because the average takt time of the pattern is 3.286 days, very close to 3.288 days.

Note: this function was designed for situations where you have less products than days since the pattern only finds whole days. If you need more than one product per day, you can put in the number of working hours and it should provide you with the number of hours between deliveries.

To solve this problem, I thought of it as a basic control system like cruise control in a vehicle or a thermostat. If the vehicles speed is below the set cruise control limit, the cruise control system increases the speed of the vehicle by accelerating. If the speed of the vehicle is above the set cruise control speed limit, the cruise control system lowers the speed of the vehicle by coasting (de-accelerating). This function for finding a takt time pattern is a very basic form of that logic.

Here is a screenshot of the Excel user interface:

How it works: First, this function begins by calculating the goal takt time. This is simply the days divided by the number of units to be built. Once the goal takt time is established, the function uses the goal takt time to set up the lower and upper bounds. To keep the repeating pattern as uniform as possible, the upper and lower bound are the integers directly above and below the goal takt time.

Next, the function creates an 1D array containing the lower and upper bound and then takes the average of that array. If the average of the array (average of the takt time pattern) is lower than the goal takt time, the upper bound value is added to the array and the average is recalculated. If the average takt time of the pattern is above the goal takt time, the lower bound value is added to the array and the average is recalculated. Here is an animation describing that process:

This loop continues (up to 1000 times) until the average takt time of the pattern is within 0.01 of the goal takt time. The tolerance is one sided by making sure the takt found is slightly faster than the goal takt. This way the pattern will always finish on-time or slightly early, but never late. Once within tolerance of the goal takt time, the function stops the loop and lists that array pattern so that when repeated over and over again throughout the year, you will finish the required number of products needed within the time duration specified.

Here is the Excel VBA code for this logic:

Public Goal As Double
Public Upper As Long
Public Lower As Long
Public Takt As Double

Sub TaktFinder()
'
' TaktFinder Macro
'

Application.ScreenUpdating = False
i = 0 'reset x
Sheet1.Columns(5).Clear

Goal = Sheet1.Cells(2, 3).Value / Sheet1.Cells(3, 3).Value 'Working Days divided by Number of Units to Build
Upper = Application.WorksheetFunction.RoundUp(Goal, 0) 'Round up for upper bound
Lower = Application.WorksheetFunction.RoundDown(Goal, 0) 'Round down for lower bound

Dim TaktPattern() As Variant 'create array with upper and lower bound value
ReDim TaktPattern(1)
TaktPattern(0) = Lower
TaktPattern(1) = Upper

Takt = Application.WorksheetFunction.Average(TaktPattern)

For i = 1 To 1000
If Abs(Takt - Goal) > 0.01 Or Takt > Goal Then

If Takt > Goal Then 'Decide if lower or upper bound value needs to be added
ReDim Preserve TaktPattern(UBound(TaktPattern) + 1) 'Redim array, add lower bound value, reaverage array
TaktPattern(UBound(TaktPattern)) = Lower
Takt = Application.WorksheetFunction.Average(TaktPattern)
Else: ReDim Preserve TaktPattern(UBound(TaktPattern) + 1) 'Redim Array, add upper bound value, reaverage array
TaktPattern(UBound(TaktPattern)) = Upper
Takt = Application.WorksheetFunction.Average(TaktPattern)
End If

Else: MsgBox "Takt Goal: " & Round(Goal, 2) & vbNewLine & "Takt Found: " & Round(Takt, 2) & vbNewLine & "Delta: " & Round(Goal - Takt, 3)

Sheet1.Cells(2, 5).Value = "Takt Pattern"
For j = 0 To UBound(TaktPattern) 'Print Array to column 5 starting at row 3 (E3)
Sheet1.Cells(j + 3, 5).Value = TaktPattern(j)
Next j
GoTo 2 ' Jump out of loop
End If
Next i

2: Application.ScreenUpdating = True

End Sub