Author Topic: Kai's Batch Sparging Spreadsheet - Efficiency Help  (Read 2157 times)

Offline thehorse

  • Cellarman
  • **
  • Posts: 30
    • View Profile
Kai's Batch Sparging Spreadsheet - Efficiency Help
« on: April 04, 2011, 01:10:18 PM »
So first of all, I'm not concerned with increasing my efficiency as I don't really care.  I just want to be as consistent as possible and predict what it would be based on the grain bills of new recipes.  I downloaded and started playing with Kai's Batch Sparging spreadsheet, which is really great.  I just don't get some of the results I'm getting.

Rough Recipe
  21.2 lbs of grain

Kai's Spreadsheet Settings
  80% extract potential (I know this is rough)
  100% conversion efficiency
  .12 gal/lb absorption
  Results = Says I should get 87% efficiency with an OG (After 15% boil off) of 1.046

Brewing Software - Brew Target and iBrewMaster - Same results
  Same grain bill at 80% efficiency it tells my my OG will be 1.052

How can I have a a higher predicted efficiency and a lower OG with the same post boil volume.

Does anyone else use this spreadsheet?

Offline hokerer

  • I spend way too much time on the AHA forum
  • ********
  • Posts: 2639
  • Manassas, VA
    • View Profile
Re: Kai's Batch Sparging Spreadsheet - Efficiency Help
« Reply #1 on: April 04, 2011, 01:29:06 PM »
A missing piece of info (that's necessary calculating efficiencies) are your volumes
Joe

Offline thehorse

  • Cellarman
  • **
  • Posts: 30
    • View Profile
Re: Kai's Batch Sparging Spreadsheet - Efficiency Help
« Reply #2 on: April 04, 2011, 01:40:45 PM »
Sorry

11.5 gallon batch
1.3 gal/hr boil off = 13.45 pre-boil
8 gallons Mash
8 gallons Sparge

Offline hokerer

  • I spend way too much time on the AHA forum
  • ********
  • Posts: 2639
  • Manassas, VA
    • View Profile
Re: Kai's Batch Sparging Spreadsheet - Efficiency Help
« Reply #3 on: April 04, 2011, 02:04:17 PM »
Quickest way to sanity check the calcs is to use the concept of "gravity points".  That is, since we don't know the details of the grist, we'll just assume 36ppg (points per pound per gallon).  Some grains might be different than that but it's a pretty good average value to use.  So, 21.2 lbs of grain at 36 ppg yields 763.2 "gravity points".  If you divide that by your final volume (11.5 G), you could theoretically end up with a OG of 1.06637 if you had 100% efficiency.

Since you ended up with 1.046, it looks like your efficiency would be 46 / 66.37 = 69.3 %

80% would give you 66.37 * 0.80 = 53 (OG = 1.053) so the softwares look to be pretty close.
Joe

Offline thehorse

  • Cellarman
  • **
  • Posts: 30
    • View Profile
Re: Kai's Batch Sparging Spreadsheet - Efficiency Help
« Reply #4 on: April 04, 2011, 02:11:04 PM »
Thanks for the reply, your calculations are exactly what I keep coming up with.  I really like the idea of using Kai's spreadsheet to predict efficiency and also gravity based on that efficiency, but I keep coming up with a higher efficiency and lower gravity than what I would expect?

Maybe I'm plugging in the wrong values into the spreadsheet, but I don't know how?

Has anyone else out there used this spreadsheet?

Offline Malticulous

  • Brewer
  • ****
  • Posts: 367
    • View Profile
Re: Kai's Batch Sparging Spreadsheet - Efficiency Help
« Reply #5 on: April 04, 2011, 03:46:16 PM »
I have used it. The boil off numbers seemed off.

Offline thehorse

  • Cellarman
  • **
  • Posts: 30
    • View Profile
Re: Kai's Batch Sparging Spreadsheet - Efficiency Help
« Reply #6 on: April 04, 2011, 06:03:28 PM »
So I actually figured it out.  There is a miscalculation in the 1st plus 2nd runoff section.  The wort gravity is not correct.  However, even if you don't have a 3rd runoff that section appears to be correct.

Offline Steve

  • Assistant Brewer
  • ***
  • Posts: 177
  • Been there, done that. And now I've returned
    • View Profile
    • Kettle and Cask Beer Blog
Re: Kai's Batch Sparging Spreadsheet - Efficiency Help
« Reply #7 on: April 06, 2011, 10:09:31 AM »
I've been looking all over his site and can't find the spreadsheet.  Anyone have the link to Kai's Batch Sparging spreadsheet?
Steve
 
  "Because beer is food: in cooking, at the table and by the glass. " Lucy Saunders

Offline tom

  • Senior Brewmaster
  • ******
  • Posts: 1110
  • Denver, CO
    • View Profile
Brew on