Spreadsheet: 130717d1322544389-what-if-excel-help
Sheet: Sheet1

Choc-a-bloc Candy Making Decision
Constants | 2009 | 2010 | 2011 | 2012 | 2013 | 2014
Tax Rate | NA | 0.33 | 0.33 | 0.35 | 0.35 | 0.35
Interest Rate | NA | 0.04 | 0.05 | 0.06 | 0.07 | 0.07
Price per bar | NA | 0.7 | 0.75 | 0.8 | 0.85 | 0.9
Minimum cash required | NA | 1000000 | 1000000 | 1000000 | 1000000 | 1000000
Cost of cocoa powder per ton | NA | 2839.87 | 2981.86 | 3130.95 | 3287.5 | 3451.88
Cost of sugar per pound | NA | 0.24 | 0.25 | 0.26 | 0.27 | 0.28
Cost of milk per 100 pounds | NA | 11.24 | 11.8 | 12.39 | 13.01 | 13.66
Cost of peanuts per ton | NA | 595.69 | 625.47 | 656.74 | 689.58 | 724.06
Cost of almonds per ton | NA | 3927 | 4123.35 | 4329.52 | 4546 | 4773.3
Cost of raisins per ton | NA | 1375.5 | 1444.28 | 1516.49 | 1592.31 | 1671.93
Fixed Administrative Costs | NA | 2600000 | 2700000 | 2800000 | 2900000 | 3000000
Recipe for one bar | NA | Milk | Peanut | Fruit & Nut | Dark
Cocoa powder | NA | 0.4 | 0.32 | 0.32 | 0.65
Sugar | NA | 0.2 | 0.16 | 0.16 | 0.2
Milk | NA | 0.2 | 0.16 | 0.16 | 0
Cocoa butter | NA | 0.2 | 0.16 | 0.16 | 0.15
Peanuts | NA | 0 | 0.2 | 0 | 0
Almonds | NA | 0 | 0 | 0.1 | 0
Raisins | NA | 0 | 0 | 0.1 | 0
Inputs | 2009 | 2010 | 2011 | 2012 | 2013 | 2014
Sales Growth Rate: L = Low, M = Medium, H = High | NA | 0.04 | NA | NA | NA | NA
% Milk chocolate bars made | NA | 0.4 | 0.4 | 0.4 | 0.4 | 0.4
% Milk chocolate with peanuts bars made | NA | 0.2 | 0.2 | 0.2 | 0.2 | 0.2
% Milk chocolate bars with fruit and nuts made | NA | 0.2 | 0.2 | 0.2 | 0.2 | 0.2
% Dark chocolate bars made | NA | 0.2 | 0.2 | 0.2 | 0.2 | 0.2
Summary of Key Results | 2009 | 2010 | 2011 | 2012 | 2013 | 2014
Net Income after Taxes | NA
End-of-year cash on hand | NA
Equals: End-of-year debt owed | NA
Calculations | 2009 | 2010 | 2011 | 2012 | 2013 | 2014
Cost of cocoa powder per ounce | NA
Cost of sugar per ounce | NA
Cost of milk per ounce | NA
Cost of peanuts per ounce | NA
Cost of almonds per ounce | NA
Cost of raisins per ounce | NA
Number of bars sold in a year | 4500000
Recipe for one 2 oz. bar | NA | Milk | Peanut | Fruit & Nut | Dark
Cocoa powder | NA
Sugar | NA
Milk | NA
Cocoa butter | NA
Peanuts | NA
Almonds | NA
Raisins | NA
Material costs of one candy bar | NA
Cost of milk chocolate bar | NA
Cost of milk chocolate bar with peanuts | NA
Cost of milk chocolate bar with fruit and nuts | NA
Cost of dark chocolate bar | NA
Income and Cash Flow Statements | 2009 | 2010 | 2011 | 2012 | 2013 | 2014
Beginning-of-year cash on hand | NA
Revenue | NA
Costs | NA
Milk chocolate bars | NA
Milk chocolate with peanuts bars | NA
Milk chocolate bar with fruit and nuts | NA
Dark chocolate bar | NA
Fixed Administrative Costs | NA
Total Costs | NA
Income before interest and taxes | NA
Interest Expense | NA
Income before taxes | NA
Income tax expense | NA
Net income after taxes | NA
Net Cash Position before borrowings  and repayment of debt | NA
Add: Borrowing from bank | NA
Less: Repayment to bank | NA
Equals: End-of-year cash on hand | 1000000
Debt Owed | 2009 | 2010 | 2011 | 2012 | 2013 | 2014
Beginning-of-year debt owed | NA
Add: Borrowing from bank | NA
Less: Repayment to bank | NA
Equals: End-of-year debt owed | 1000000