Full Break Even Analysis

ONLY WHITE BOXES NEED TO BE COMPLETED A COMPANY / PROJECT NAME B Currency Symbol: QUICK PRICE FLEX - Insert Price Variation to be used to see the Effect of a Price Change on the Break Even Point C >> Insert Price Information >> C >> Insert Overhead Details >> C >> Insert Variable Cost Details >> D >> View Break Even Chart >> D >> View Break Even Analysis >> D >> View BEP Sensitivity Output >> D >> View Profit Sensitivity Output >> G >> Use the Fast Flex Page >> SUMMARY RESULTS Unit Price Variable Unit Costs Contribution per Unit Fixed Costs Break Even Revenues Units to Break Even NEW BEP SALES % CHANGE £ XYZ Co Ltd £ 20% E A. Insert Company / Project Name B. Insert Currency symbol to be used if not GBP C. Enter Data (click to open sheet) D. Results will show in output sheets (click) E. Use QUICK FLEX on Price to see BEP effect F. OPTION - Insert a Profit Objective G. Use the FAST FLEX page to test options quickly £ £ #DIV/0! #DIV/0! Flex 1 Lower Price 0 80% #DIV/0! #DIV/0! £ #DIV/0! #DIV/0! £ Flex 2 Higher Price 0 120% #DIV/0! #DIV/0! #DIV/0! #DIV/0! F QUICK PROFIT CALCULATOR Insert Annual Profit Objective Revenues to Achieve Profit Units to Achieve Profit Objective PRICE FLEX RESULTS - NEW BEP: Base Price NEW PRICES £ 0 % CHANGE 100% NEW UNIT BEP % CHANGE #DIV/0! 100% #DIV/0! 100% £ £ 0.00 #DIV/0! #DIV/0! DO NOT COPY - Download More Free Copies at innovateur.co.uk Subscribe to our FREE fortnightly update - no junk mail - just mail us For support e-mail us : price-freeware@innovateur.co.uk This Freeware is provided in goodfaith - innovateur accepts no liability whatever and by downloading it and using it you agree to this. << BACK TO MASTER .:: PRICES ::. OVERHEADS >> YOU ONLY NEED TO COMPLETE THE WHITE BOX PRICES ACHIEVED - AND DISTRIBUTION CHANNELS USED (IF ANY) CONSUMER PRICE (inc VAT) VAT (or GSP in USA) % VAT / GSP NET CONSUMER PRICE AVERAGE NET UNIT PRICE ACHIEVED 17.5% - VAR COSTS >> 1. Insert Price Including VAT / GSP 2. Change VAT rate if in USA OPTIONAL BLUE BOXES - IF USING DIFFERENT CHANNELS: 3. Insert any % sales not sold direct, by channel 4. Insert Retailer Margin % on sale if applicable 5. Insert Wholesaler Margin on Sale if Applicable OPTION OF USING DIFFERENT SALES CHANNEL - ONLY COMPLETE LIGHT BLUE BOXES DIRECT SALES RETAILER CHANNEL CHANNEL % OF UNIT SALES PER CHANNEL 100% 0% CONSUMER PRICE (inc VAT) VAT (or GSP in USA) % VAT / GSP NET CONSUMER PRICE RETAILER MARGIN % ON SALE RETAILER MARGIN AMMOUNT NET PRICE SOLD TO RETAILER WHOLESALER MARGIN % WHOLESALER MARGIN UNIT PRICE ACHIEVED PER CHANNEL CONTRIBUTION PER UNIT PER CHANNEL MEAN NET PRICE ACHIEVED MEAN CONTRIBUTION PER UNIT 17.5% 17.5% 0.0% - WHOLESALER CHANNEL 0% 17.5% 0.0% 0.0% CONSUMER PRICE (inc VAT) RETAILER MARGIN % ON SALE WHOLESALER MARGIN % - - UNIT PRICE ACHIEVED PER CH CONTRIBUTION PER UNIT PER NAVIGATION BAR USING DIFFERENT CHANNELS: direct, by channel sale if applicable n Sale if Applicable CONSUMER PRICE (inc VAT) RETAILER MARGIN % ON SALE WHOLESALER MARGIN % UNIT PRICE ACHIEVED PER CHANNEL CONTRIBUTION PER UNIT PER CHANNEL Operating costs that will remain pretty much fixed for the level of operations being reviewed 1. Insert Overhead & Fixed Costs Per Year (change or add categories) OPTION - COMPLETE IF YOU WANT TO CONSIDER DIFFERENT FIXED COST LEVELS 2. OPTION - Insert Costs at two further business stages - as % increase OR figures FIXED COSTS Cost Area Offices & Rates Salaries & Employer NI Utilities Telephones Insurance Stationery & Post Office Equipment Rental Website Costs Accountancy Costs Legal & Professional Fees Travel & Subsistence Motor Expenses Sundry Expenses Other Services Marketing Costs Research Costs Depreciation Loan Interest Charges Other Fixed Costs Per Year Per Month - OR Actual Figure 0% Of FIRST Stage OR Actual Figure 0% Of FIRST Stage Stage 2 FIXED COSTS - Stage 3 FIXED COSTS - << BACK TO MASTER VARIABLE COSTS .:: VARIABLE COSTS ::. ONLY COMPLETE WHITE BOXES << PRICE DETAILS << OVERHEAD DETAILS 1. Insert UNIT VARIABLE COSTS based on monetary value 2. Insert any costs that have been AGREED AS A PERCENTAGE OF SALES value - IF ANY VARIABLE COSTS / UNIT SET UNIT COSTS Components Packaging Production Wastage Other Unit Costs COST / UNIT #DIV/0! % OF PRICE #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! V.COSTS OF PRICE FLEX 1 Price 1 80% COST #DIV/0! PRICE % #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! V.COSTS OF PRICE FLEX 2 Price 2 120% COST #DIV/0! PRICE % #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! -50% Flex COST #DIV/0! PRICE % #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! -20% Flex COST #DIV/0! PRICE % #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! COST - 0% Flex #DIV/0! PRICE % #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! 20% Flex COST #DIV/0! PRICE % #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! 50% Flex COST #DIV/0! PRICE % #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! 300% Flex COST #DIV/0! PRICE % #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! ANY COSTS AGREED AS % OF SALES % Sales Costs % Distribution Costs % Bad Debt Other % based costs COST / UNIT - % OF PRICE 0.0% 0.0% 0.0% 0.0% 0.0% 0.0% COST - PRICE % 0.0% 0.0% 0.0% 0.0% 0.0% 0.0% COST - PRICE % 0.0% 0.0% 0.0% 0.0% 0.0% 0.0% COST - PRICE % 0.0% 0.0% 0.0% 0.0% 0.0% 0.0% #DIV/0! COST - PRICE % 0.0% 0.0% 0.0% 0.0% 0.0% 0.0% #DIV/0! COST - PRICE % 0.0% 0.0% 0.0% 0.0% 0.0% 0.0% #DIV/0! COST - PRICE % 0.0% 0.0% 0.0% 0.0% 0.0% 0.0% #DIV/0! COST - PRICE % 0.0% 0.0% 0.0% 0.0% 0.0% 0.0% #DIV/0! COST - PRICE % 0.0% 0.0% 0.0% 0.0% 0.0% 0.0% #DIV/0! PRICE CONTRIBUTION / UNIT << BACK TO MASTER :: FAST FLEX :: Summary Information at the Base Case Level: Price £ Break Even Point BEP Revenues £ #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! NEW PROFIT OBJECTIVE Units to Achieve Revenues to Achieve CALCULATE PRICE No. of Units We Can Sell Unit Price Required to BEP £ £ FAST FLEX - QUICK ANSWERS WITHOUT AFFECTING THE REST OF THE MODEL ONLY COMPLETE WHITE BOXES - INSERT NEW VALUES NEW PRICE BEP Units BEP Revenues NEW FIXED COSTS BEP Units BEP Revenues NEW UNIT VARIABLE COSTS BEP Units BEP Revenues NEW VARIABLE COSTS % BEP Units BEP Revenues £ £ £ £ £ £ % £ £ CALCULATE PROFIT FOR UNITS SOLD No. of Units Sold Profit Achieved £ CALCULATE PROFIT FOR REVENUES ACHIEVED Sales Income £ Profit Achieved £ CALCULATE PRICE WITH A UNIT & PROFIT OBJECTIVE No. of Units We Can Sell Profit Objective £ Unit Price Required £ CALCULATE BEP WITH NEW FIXED AND VARIABLE COSTS New Fixed Costs £ New Variable Costs £ BEP Units #DIV/0! This page is ONLY OPERATIONAL AFTER the Price, Overhead, and Unit Variable Costs SHEETS have been completed t the Base Case Level: 0.00 #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! OR UNITS SOLD #DIV/0! #DIV/0! OR REVENUES ACHIEVED #DIV/0! #DIV/0! TH A UNIT & PROFIT OBJECTIVE #DIV/0! #DIV/0! SUMMARY OF BREAK EVEN ANALYSIS XYZ Co Ltd PRICE INFORMATION Price Achieved per Unit £ - UNIT COSTS & CONTRIBUTION Variable Costs Per Unit Contribution Per Unit £ £ - FIXED COSTS & OVERHEADS Annual Overheds & Fixed Costs £ per annum £ per month - BREAK EVEN POINT Number of Units Required to Break Even Sales Revenues Required to Break Even £ per annum #DIV/0! #DIV/0! Units pa £ per month #DIV/0! #DIV/0! Units pcm Produced with INNOVATEUR.CO.UK Financial Modelling Software BREAK EVEN SENSITIVITY XYZ Co Ltd Base Case Price & Break Even Information Price Achieved per Unit Variable Costs Per Unit Contribution Per Unit Annual Overheds & Fixed Costs Number of Units Required to Break Even Sales Revenues Required to Break Even £ £ £ £ £ Per Year #DIV/0! #DIV/0! Units pa SENSITIVITY OF BREAK EVEN POINT: CHANGE FROM BASE CASE PRICES Contribution per Unit BEP Revenues BEP Units £ £ #DIV/0! #DIV/0! -50% #DIV/0! #DIV/0! #DIV/0! #DIV/0! £ £ #DIV/0! #DIV/0! -20% #DIV/0! #DIV/0! #DIV/0! #DIV/0! £ £ Base Case #DIV/0! #DIV/0! 0% #DIV/0! #DIV/0! #DIV/0! #DIV/0! £ £ #DIV/0! #DIV/0! 20% #DIV/0! #DIV/0! #DIV/0! #DIV/0! £ £ #DIV/0! #DIV/0! 50% #DIV/0! #DIV/0! #DIV/0! #DIV/0! £ £ 300% #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! CHANGE FROM BASE CASE FIXED COSTS Contribution per Unit BEP Revenues BEP Units £ £ #DIV/0! #DIV/0! -50% #DIV/0! #DIV/0! #DIV/0! #DIV/0! £ £ #DIV/0! #DIV/0! -20% #DIV/0! #DIV/0! #DIV/0! #DIV/0! £ £ Base Case #DIV/0! #DIV/0! 0% #DIV/0! #DIV/0! #DIV/0! #DIV/0! £ £ #DIV/0! #DIV/0! 20% #DIV/0! #DIV/0! #DIV/0! #DIV/0! £ £ #DIV/0! #DIV/0! 50% #DIV/0! #DIV/0! #DIV/0! #DIV/0! £ £ 300% #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! CHANGE FROM BASE CASE VARIABLE COSTS Contribution per Unit BEP Revenues BEP Units £ £ #DIV/0! #DIV/0! -50% #DIV/0! #DIV/0! #DIV/0! #DIV/0! £ £ #DIV/0! #DIV/0! -20% #DIV/0! #DIV/0! #DIV/0! #DIV/0! £ £ Base Case #DIV/0! #DIV/0! 0% #DIV/0! #DIV/0! #DIV/0! #DIV/0! £ £ #DIV/0! #DIV/0! 20% #DIV/0! #DIV/0! #DIV/0! #DIV/0! £ £ #DIV/0! #DIV/0! 50% #DIV/0! #DIV/0! #DIV/0! #DIV/0! £ £ 300% #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! Produced with INNOVATEUR.CO.UK Financial Modelling Software PROFIT ANALYSIS ALONG THE BREAK EVEN CURVE XYZ Co Ltd Price & Break Even Information Price Achieved per Unit Variable Costs Per Unit Contribution Per Unit Annual Overheds & Fixed Costs Number of Units Required to Break Even Sales Revenues Required to Break Even £ £ £ £ £ Per Year #DIV/0! #DIV/0! Units pa Per Month #DIV/0! Units #DIV/0! pcm PROFIT / LOSS AT VARIOUS SALES LEVELS % of BEP Units Variable Costs / Unit Fixed Costs Total Costs at this Level Sales Revenues at this Level Profit / Loss per Further / Lesser Unit Sold Profit (Loss) % #DIV/0! £ £ £ £ £ £ #DIV/0! #DIV/0! 0 #DIV/0! #DIV/0! #DIV/0! #DIV/0! 0 #DIV/0! 50% #DIV/0! #DIV/0! #DIV/0! 0 #DIV/0! 100% #DIV/0! #DIV/0! #DIV/0! 0 #DIV/0! 150% #DIV/0! #DIV/0! #DIV/0! 0 #DIV/0! 200% #DIV/0! #DIV/0! #DIV/0! 0 250% 300% #DIV/0! - Produced with INNOVATEUR.CO.UK Financial Modelling Software £ XYZ Co Ltd Break Even Chart 1 1 1 1 1 1 0 0 0 0 0 0 0 0 0 0 Units Sold Fixed Costs Total Costs at this Level Sales Revenues at this Level Profit (Loss) Produced with INNOVATEUR.CO.UK Financial Modelling Software