Hesketh had the better of the chances during a goalless first half but Tempest took the lead on 68 minutes before captain Josh Brierley equalised nine minutes before full time.
Peter Atkinson-Jones rose highest and headed home in the fourth minute of added time to seal the double for Dan Birkby’s side, who have also won Division One this season.
Tempest’s Josh Firth fired just over with a direct free kick from 25 yards with 10 minutes gone, before Ste Ward’s effort from a tight angle could only find the side netting.
League joint top scorer Kevin Edgar turned provider and set up Atkinson-Jones 20 minutes in with a fantastic low ball across the pitch. The forward turned his marker and forced a good save from Tempest ‘keeper Charlie Fletcher.
He had another great chance just a couple of minutes later: Fletcher was caught off his line as a through ball found the attacker but he toe-poked it just wide.
Fletcher did well to gather fierce long-range efforts from Jack Brierley and Matty Law before Tempest’s Jordan Pendlebury forced a decent stop from Andy Thorpe after a drilled effort from inside the box.
After the break, Fletcher produced a top-drawer save to deny Matty Coughlan from close range, with the ‘keeper sticking out his leg and blocking the number nine’s effort.
Tempest substitute James Dean saw his effort drag wide of the post on 62 minutes, but his side made another chance count soon after.
Another long-range pass found Ste Ward and, after Thorpe rushed out, Ward took it past the goalkeeper before tapping into an empty net to give Tempest the lead.
Hesketh equalised with less than 10 minutes to go. A great ball in from George Bond - the first of two crucial deliveries from the number five - found captain Josh Brierley and he headed home.
Thorpe made three good saves in the final stages as Tempest looked to regain the lead, first denying Oli Groocock before saving from two Joel Shorthouse efforts.
Hesketh Bank took the lead in dramatic style in the fourth minute of added time. Another Bond set piece delivery found its way to Atkinson-Jones who jumped with all his might and nodded the ball into the net, sparking wild celebrations from Hesketh’s fans and bench.
Although, there were still a few seconds for things to change and another twist in the tale was just centimetres away. Dean hit the woodwork for Tempest in one of the final touches of the game.
Post-match, Hesketh Bank’s manager Dan Birkby said: [We feel] brilliant. We took it right to them, didn't we? It was a good game.
"Credit to Tempest, you can see why they do well, finished well in the league. But I felt like we deserved it overall and played the better football during the game.
“We were probably unlucky not to score earlier on, in the first half especially, we created some really good chances. But that's how football goes.
“Before you know it, you've one-nil down. We sort of looked all over the place probably for 10, 15 minutes, [there were] two or three subs which kind of threw the lads and our shape was poor, but we got ourselves back into it with two set pieces.
“Some of the lads in the team we have got here, like Dave Brooks, he’s known throughout the league as a warrior, he went through brick walls, to be honest.
“He's been one of the best players in the West Lancs League for years, and then the two set pieces there from George Bond!
“These lads, they're a great team and they've really created something. To win the league like they have done, it's been great.”
Tempest boss Ross McNair said: “It’s difficult to take when it's a last-minute winner but it's quite raw at the minute.
“We just played against a very, very good side, I have to say that.
“To be honest with you, they should have scored two in the first half, they battered us in the first half, we didn't turn up.
“Ultimately, I think that's what lost us the game. I don't think we were in the game at all, if I'm being honest.
“In the second half, I think we were much better and we got a goal out of nothing really, but we huffed and puffed and ultimately we didn't deserve anything from it.
“Hats off to them, [they’re a] good side, good set of lads and a good management team, so I'm proud for them.”
Keep up-to-date with our exclusive email newsletters.
Subscribe