<table style="border-collapse: collapse; width: 552pt;" border="0" cellpadding="0" cellspacing="0" width="735"><col style="width: 25pt;" width="33"> <col style="width: 527pt;" width="702"> <tbody><tr style="height: 18pt;" height="24"> <td class="xl66" style="height: 18pt; width: 25pt;" width="33" height="24">1</td> <td class="xl68" style="width: 527pt;" width="702">Enter your name in cell B36 of the "5 Year Forecast" worksheet.</td> </tr> <tr style="height: 18pt;" height="24"> <td class="xl66" style="height: 18pt;" height="24">2</td> <td class="xl67">Complete the "5 Year Forecast" worksheet.</td> </tr> <tr style="height: 18pt;" height="24"> <td class="xl66" style="height: 18pt;" height="24">3</td> <td class="xl69">Use the cells in the Assumptions area to drive the calculations.</td> </tr> <tr style="height: 18pt;" height="24"> <td class="xl66" style="height: 18pt;" height="24">4</td> <td class="xl67">The user should be able to change any of the assumptions and the calculations should reflect the changed values.</td> </tr> <tr style="height: 18pt;" height="24"> <td class="xl66" style="height: 18pt;" height="24">5</td> <td class="xl67">"Year 1" should be replaced with the "first year of forecast" value from the assumptions area (i.e. 2010). </td> </tr> <tr style="height: 18pt;" height="24"> <td class="xl66" style="height: 18pt;" height="24">6</td> <td class="xl67">Annual Increases start in the second year.</td> </tr> <tr style="height: 18pt;" height="24"> <td class="xl66" style="height: 18pt;" height="24">7</td> <td class="xl67">Remember: Revenue - Expenses = Income (a.k.a. Earnings) (Expenses include fixed and variable expenses)</td> </tr> <tr style="height: 18pt;" height="24"> <td class="xl66" style="height: 18pt;" height="24">8</td> <td class="xl69">Remember: Variable Expenses are based upon the number of units sold!</td> </tr> <tr style="height: 18pt;" height="24"> <td class="xl66" style="height: 18pt;" height="24">
</td> <td class="xl67">
</td> </tr> <tr style="height: 18pt;" height="24"> <td class="xl66" style="height: 18pt;" height="24">9</td> <td class="xl67">Use the information below to create 3 Scenarios: Consensus, Optimistic and Pessimistic AND</td> </tr> <tr style="height: 18pt;" height="24"> <td class="xl66" style="height: 18pt;" height="24">
</td> <td class="xl67"> create a Scenario Summary on a new worksheet showing the Earnings Before Taxes (EBT) for each year.
<table style="border-collapse: collapse; width: 609pt;" border="0" cellpadding="0" cellspacing="0" width="813"><col style="width: 63pt;" width="84"> <col style="width: 161pt;" width="214"> <col style="width: 77pt;" span="5" width="103"> <tbody><tr style="height: 15.75pt;" height="21"> <td rowspan="4" class="xl110" style="border-bottom: 0.5pt solid black; height: 63pt; width: 63pt;" width="84" height="84">Income</td> <td class="xl102" style="width: 161pt;" width="214">Revenues</td> <td class="xl66" style="width: 77pt;" width="103"> </td> <td class="xl66" style="width: 77pt;" width="103"> </td> <td class="xl66" style="width: 77pt;" width="103"> </td> <td class="xl66" style="width: 77pt;" width="103"> </td> <td class="xl67" style="width: 77pt;" width="103"> </td> </tr> <tr style="height: 15.75pt;" height="21"> <td class="xl98" style="height: 15.75pt;" height="21">Jars Sold - Projected</td> <td class="xl68"> 100,000 </td> <td class="xl68">
</td> <td class="xl68">
</td> <td class="xl68">
</td> <td class="xl69"> </td> </tr> <tr style="height: 15.75pt;" height="21"> <td class="xl98" style="height: 15.75pt;" height="21">Price per jar</td> <td class="xl70" align="right">$5.00</td> <td class="xl70">
</td> <td class="xl70">
</td> <td class="xl70">
</td> <td class="xl69"> </td> </tr> <tr style="height: 15.75pt;" height="21"> <td class="xl99" style="height: 15.75pt;" height="21">Gross Revenue</td> <td class="xl72"> </td> <td class="xl72"> </td> <td class="xl72"> </td> <td class="xl72"> </td> <td class="xl73"> </td> </tr> <tr style="height: 18.75pt;" height="25"> <td class="xl82" style="height: 18.75pt;" height="25">
</td> <td class="xl84">
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td rowspan="11" class="xl113" style="border-bottom: 0.5pt solid black; height: 172.5pt;" height="230">Expenses</td> <td class="xl103">Fixed Costs</td> <td class="xl66"> </td> <td class="xl66"> </td> <td class="xl66"> </td> <td class="xl66"> </td> <td class="xl67"> </td> </tr> <tr style="height: 15.75pt;" height="21"> <td class="xl98" style="height: 15.75pt;" height="21">Production facility lease</td> <td class="xl74" align="right">$50,000</td> <td class="xl74">
</td> <td class="xl74">
</td> <td class="xl74">
</td> <td class="xl75"> </td> </tr> <tr style="height: 15.75pt;" height="21"> <td class="xl98" style="height: 15.75pt;" height="21">Fixed Labor expense</td> <td class="xl74" align="right">$40,000</td> <td class="xl74">
</td> <td class="xl74">
</td> <td class="xl74">
</td> <td class="xl75"> </td> </tr> <tr style="height: 15.75pt;" height="21"> <td class="xl98" style="height: 15.75pt;" height="21">Marketing expense</td> <td class="xl74" align="right">$70,000</td> <td class="xl74">
</td> <td class="xl74">
</td> <td class="xl74">
</td> <td class="xl69"> </td> </tr> <tr style="height: 15.75pt;" height="21"> <td class="xl98" style="height: 15.75pt;" height="21">Administration expense</td> <td class="xl74" align="right">$30,000</td> <td class="xl74">
</td> <td class="xl74">
</td> <td class="xl74">
</td> <td class="xl75"> </td> </tr> <tr style="height: 15.75pt;" height="21"> <td class="xl100" style="height: 15.75pt;" height="21">Total Fixed Costs</td> <td class="xl76">
</td> <td class="xl76">
</td> <td class="xl76">
</td> <td class="xl76">
</td> <td class="xl77"> </td> </tr> <tr style="height: 15.75pt;" height="21"> <td class="xl98" style="height: 15.75pt;" height="21"> </td> <td class="xl74">
</td> <td class="xl74">
</td> <td class="xl74">
</td> <td class="xl74">
</td> <td class="xl75"> </td> </tr> <tr style="height: 15.75pt;" height="21"> <td class="xl104" style="height: 15.75pt;" height="21">Variable Costs</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td class="xl78"> </td> </tr> <tr style="height: 15.75pt;" height="21"> <td class="xl98" style="height: 15.75pt;" height="21">Mfg cost per jar</td> <td class="xl70" align="right">$2.00</td> <td class="xl70">
</td> <td class="xl70">
</td> <td class="xl70">
</td> <td class="xl71"> </td> </tr> <tr style="height: 15.75pt;" height="21"> <td class="xl98" style="height: 15.75pt;" height="21">Variable labor cost per jar</td> <td class="xl70" align="right">$0.25</td> <td class="xl70">
</td> <td class="xl70">
</td> <td class="xl70">
</td> <td class="xl71"> </td> </tr> <tr style="height: 15.75pt;" height="21"> <td class="xl101" style="height: 15.75pt;" height="21">Total Variable Costs</td> <td class="xl72"> </td> <td class="xl72"> </td> <td class="xl72"> </td> <td class="xl72"> </td> <td class="xl73"> </td> </tr> <tr style="height: 16.5pt;" height="22"> <td class="xl83" style="height: 16.5pt;" height="22">
</td> <td class="xl83">
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 17.25pt;" height="23"> <td class="xl85" style="height: 17.25pt;" height="23"> </td> <td class="xl105">Earnings Before Taxes</td> <td class="xl79"> </td> <td class="xl79"> </td> <td class="xl79"> </td> <td class="xl79"> </td> <td class="xl80"> </td> </tr> <tr style="height: 13.5pt;" height="18"> <td style="height: 13.5pt;" height="18">
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 15.75pt;" height="21"> <td rowspan="13" class="xl116" style="border-bottom: 0.5pt solid black; height: 195.75pt;" height="261">Assumptions</td> <td colspan="2" class="xl119">Initial/First Year Values</td> <td class="xl86"> </td> <td class="xl108">Annual Increase</td> <td class="xl109">Maximum</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl106" style="height: 15pt;" height="20">First year sales (# of jars)</td> <td class="xl87"> 100,000 </td> <td class="xl88">
</td> <td class="xl89" align="right">3.0%</td> <td class="xl90"> </td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl106" style="height: 15pt;" height="20">Selling price per jar</td> <td class="xl91" align="right">$5.00</td> <td class="xl88">
</td> <td class="xl89" align="right">5.0%</td> <td class="xl92" align="right">$5.75 </td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl106" style="height: 15pt;" height="20"> </td> <td class="xl91">
</td> <td class="xl88">
</td> <td class="xl89">
</td> <td class="xl90"> </td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl106" style="height: 15pt;" height="20">Production facility lease</td> <td class="xl93" align="right">$50,000</td> <td class="xl88">
</td> <td class="xl89" align="right">8.0%</td> <td class="xl90"> </td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl106" style="height: 15pt;" height="20">Fixed labor expense</td> <td class="xl93" align="right">$40,000</td> <td class="xl88">
</td> <td class="xl89" align="right">3.0%</td> <td class="xl90"> </td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl106" style="height: 15pt;" height="20">Marketing expense</td> <td class="xl93" align="right">$70,000</td> <td class="xl88">
</td> <td class="xl89" align="right">10.0%</td> <td class="xl94" align="right">$90,000</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl106" style="height: 15pt;" height="20">Administration expense</td> <td class="xl93" align="right">$30,000</td> <td class="xl88">
</td> <td class="xl89" align="right">5.0%</td> <td class="xl90"> </td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl106" style="height: 15pt;" height="20"> </td> <td class="xl93">
</td> <td class="xl88">
</td> <td class="xl89">
</td> <td class="xl90"> </td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl106" style="height: 15pt;" height="20">Mfg cost per Jar</td> <td class="xl91" align="right">$2.00</td> <td class="xl88">
</td> <td class="xl89" align="right">5.0%</td> <td class="xl90"> </td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl106" style="height: 15pt;" height="20">Variable labor cost per jar</td> <td class="xl91" align="right">$0.25</td> <td class="xl88">
</td> <td class="xl89" align="right">4.0%</td> <td class="xl90"> </td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl81" style="height: 15pt;" height="20"> </td> <td class="xl88">
</td> <td class="xl88">
</td> <td class="xl88">
</td> <td class="xl90"> </td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl107" style="height: 15pt;" height="20">First year of forecast</td> <td class="xl95" align="right">2010</td> <td class="xl95"> </td> <td class="xl96"> </td> <td class="xl97"> </td> <td>
</td> </tr> </tbody></table>
Having so much trouble. Need to do it for 5 years
</td> </tr> </tbody></table>
</td> <td class="xl67">
</td> </tr> <tr style="height: 18pt;" height="24"> <td class="xl66" style="height: 18pt;" height="24">9</td> <td class="xl67">Use the information below to create 3 Scenarios: Consensus, Optimistic and Pessimistic AND</td> </tr> <tr style="height: 18pt;" height="24"> <td class="xl66" style="height: 18pt;" height="24">
</td> <td class="xl67"> create a Scenario Summary on a new worksheet showing the Earnings Before Taxes (EBT) for each year.
<table style="border-collapse: collapse; width: 609pt;" border="0" cellpadding="0" cellspacing="0" width="813"><col style="width: 63pt;" width="84"> <col style="width: 161pt;" width="214"> <col style="width: 77pt;" span="5" width="103"> <tbody><tr style="height: 15.75pt;" height="21"> <td rowspan="4" class="xl110" style="border-bottom: 0.5pt solid black; height: 63pt; width: 63pt;" width="84" height="84">Income</td> <td class="xl102" style="width: 161pt;" width="214">Revenues</td> <td class="xl66" style="width: 77pt;" width="103"> </td> <td class="xl66" style="width: 77pt;" width="103"> </td> <td class="xl66" style="width: 77pt;" width="103"> </td> <td class="xl66" style="width: 77pt;" width="103"> </td> <td class="xl67" style="width: 77pt;" width="103"> </td> </tr> <tr style="height: 15.75pt;" height="21"> <td class="xl98" style="height: 15.75pt;" height="21">Jars Sold - Projected</td> <td class="xl68"> 100,000 </td> <td class="xl68">
</td> <td class="xl68">
</td> <td class="xl68">
</td> <td class="xl69"> </td> </tr> <tr style="height: 15.75pt;" height="21"> <td class="xl98" style="height: 15.75pt;" height="21">Price per jar</td> <td class="xl70" align="right">$5.00</td> <td class="xl70">
</td> <td class="xl70">
</td> <td class="xl70">
</td> <td class="xl69"> </td> </tr> <tr style="height: 15.75pt;" height="21"> <td class="xl99" style="height: 15.75pt;" height="21">Gross Revenue</td> <td class="xl72"> </td> <td class="xl72"> </td> <td class="xl72"> </td> <td class="xl72"> </td> <td class="xl73"> </td> </tr> <tr style="height: 18.75pt;" height="25"> <td class="xl82" style="height: 18.75pt;" height="25">
</td> <td class="xl84">
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td rowspan="11" class="xl113" style="border-bottom: 0.5pt solid black; height: 172.5pt;" height="230">Expenses</td> <td class="xl103">Fixed Costs</td> <td class="xl66"> </td> <td class="xl66"> </td> <td class="xl66"> </td> <td class="xl66"> </td> <td class="xl67"> </td> </tr> <tr style="height: 15.75pt;" height="21"> <td class="xl98" style="height: 15.75pt;" height="21">Production facility lease</td> <td class="xl74" align="right">$50,000</td> <td class="xl74">
</td> <td class="xl74">
</td> <td class="xl74">
</td> <td class="xl75"> </td> </tr> <tr style="height: 15.75pt;" height="21"> <td class="xl98" style="height: 15.75pt;" height="21">Fixed Labor expense</td> <td class="xl74" align="right">$40,000</td> <td class="xl74">
</td> <td class="xl74">
</td> <td class="xl74">
</td> <td class="xl75"> </td> </tr> <tr style="height: 15.75pt;" height="21"> <td class="xl98" style="height: 15.75pt;" height="21">Marketing expense</td> <td class="xl74" align="right">$70,000</td> <td class="xl74">
</td> <td class="xl74">
</td> <td class="xl74">
</td> <td class="xl69"> </td> </tr> <tr style="height: 15.75pt;" height="21"> <td class="xl98" style="height: 15.75pt;" height="21">Administration expense</td> <td class="xl74" align="right">$30,000</td> <td class="xl74">
</td> <td class="xl74">
</td> <td class="xl74">
</td> <td class="xl75"> </td> </tr> <tr style="height: 15.75pt;" height="21"> <td class="xl100" style="height: 15.75pt;" height="21">Total Fixed Costs</td> <td class="xl76">
</td> <td class="xl76">
</td> <td class="xl76">
</td> <td class="xl76">
</td> <td class="xl77"> </td> </tr> <tr style="height: 15.75pt;" height="21"> <td class="xl98" style="height: 15.75pt;" height="21"> </td> <td class="xl74">
</td> <td class="xl74">
</td> <td class="xl74">
</td> <td class="xl74">
</td> <td class="xl75"> </td> </tr> <tr style="height: 15.75pt;" height="21"> <td class="xl104" style="height: 15.75pt;" height="21">Variable Costs</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td class="xl78"> </td> </tr> <tr style="height: 15.75pt;" height="21"> <td class="xl98" style="height: 15.75pt;" height="21">Mfg cost per jar</td> <td class="xl70" align="right">$2.00</td> <td class="xl70">
</td> <td class="xl70">
</td> <td class="xl70">
</td> <td class="xl71"> </td> </tr> <tr style="height: 15.75pt;" height="21"> <td class="xl98" style="height: 15.75pt;" height="21">Variable labor cost per jar</td> <td class="xl70" align="right">$0.25</td> <td class="xl70">
</td> <td class="xl70">
</td> <td class="xl70">
</td> <td class="xl71"> </td> </tr> <tr style="height: 15.75pt;" height="21"> <td class="xl101" style="height: 15.75pt;" height="21">Total Variable Costs</td> <td class="xl72"> </td> <td class="xl72"> </td> <td class="xl72"> </td> <td class="xl72"> </td> <td class="xl73"> </td> </tr> <tr style="height: 16.5pt;" height="22"> <td class="xl83" style="height: 16.5pt;" height="22">
</td> <td class="xl83">
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 17.25pt;" height="23"> <td class="xl85" style="height: 17.25pt;" height="23"> </td> <td class="xl105">Earnings Before Taxes</td> <td class="xl79"> </td> <td class="xl79"> </td> <td class="xl79"> </td> <td class="xl79"> </td> <td class="xl80"> </td> </tr> <tr style="height: 13.5pt;" height="18"> <td style="height: 13.5pt;" height="18">
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 15.75pt;" height="21"> <td rowspan="13" class="xl116" style="border-bottom: 0.5pt solid black; height: 195.75pt;" height="261">Assumptions</td> <td colspan="2" class="xl119">Initial/First Year Values</td> <td class="xl86"> </td> <td class="xl108">Annual Increase</td> <td class="xl109">Maximum</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl106" style="height: 15pt;" height="20">First year sales (# of jars)</td> <td class="xl87"> 100,000 </td> <td class="xl88">
</td> <td class="xl89" align="right">3.0%</td> <td class="xl90"> </td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl106" style="height: 15pt;" height="20">Selling price per jar</td> <td class="xl91" align="right">$5.00</td> <td class="xl88">
</td> <td class="xl89" align="right">5.0%</td> <td class="xl92" align="right">$5.75 </td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl106" style="height: 15pt;" height="20"> </td> <td class="xl91">
</td> <td class="xl88">
</td> <td class="xl89">
</td> <td class="xl90"> </td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl106" style="height: 15pt;" height="20">Production facility lease</td> <td class="xl93" align="right">$50,000</td> <td class="xl88">
</td> <td class="xl89" align="right">8.0%</td> <td class="xl90"> </td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl106" style="height: 15pt;" height="20">Fixed labor expense</td> <td class="xl93" align="right">$40,000</td> <td class="xl88">
</td> <td class="xl89" align="right">3.0%</td> <td class="xl90"> </td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl106" style="height: 15pt;" height="20">Marketing expense</td> <td class="xl93" align="right">$70,000</td> <td class="xl88">
</td> <td class="xl89" align="right">10.0%</td> <td class="xl94" align="right">$90,000</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl106" style="height: 15pt;" height="20">Administration expense</td> <td class="xl93" align="right">$30,000</td> <td class="xl88">
</td> <td class="xl89" align="right">5.0%</td> <td class="xl90"> </td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl106" style="height: 15pt;" height="20"> </td> <td class="xl93">
</td> <td class="xl88">
</td> <td class="xl89">
</td> <td class="xl90"> </td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl106" style="height: 15pt;" height="20">Mfg cost per Jar</td> <td class="xl91" align="right">$2.00</td> <td class="xl88">
</td> <td class="xl89" align="right">5.0%</td> <td class="xl90"> </td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl106" style="height: 15pt;" height="20">Variable labor cost per jar</td> <td class="xl91" align="right">$0.25</td> <td class="xl88">
</td> <td class="xl89" align="right">4.0%</td> <td class="xl90"> </td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl81" style="height: 15pt;" height="20"> </td> <td class="xl88">
</td> <td class="xl88">
</td> <td class="xl88">
</td> <td class="xl90"> </td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl107" style="height: 15pt;" height="20">First year of forecast</td> <td class="xl95" align="right">2010</td> <td class="xl95"> </td> <td class="xl96"> </td> <td class="xl97"> </td> <td>
</td> </tr> </tbody></table>
Having so much trouble. Need to do it for 5 years
</td> </tr> </tbody></table>
