Using the completed breakeven analysis (below) for the Specialty Cake business, prepare the 12 month cash forecast (below)
Sales by Month
14.078.0
210.086.0
336.0946.0
457.01047.0
521.01111.0
614.51224.5
TOTAL285.0
U24 I X V fx B C D E F G H I J K L N 0 P Q R S T V X Y Z AA AB AC AD Cash Budget - Non Seasonal Business BTE 302 STEP # 2 STEP # starting point Simplified Cash Budget Enter 12 month sales in Sales Per Year data taken from units - show variation breakeven analysis over 12 month period MONTH 1 MONTH 2 MONTH 3 MONTH 4 MONTH 5 MONTH 6 MONTH 7 MONTH 8 MONTH 9 MONTH 10 MONTH 11 MONTH 12 TOTAL In Units spreadsheet Units Sold STEP # 3 Cash Inflows Enter breakeven data must equal after unit sales by month entered 12 Unit Sale Price per unit Other Revenues Interest Income 15 Total Inflows Indicates data entry required 16 17 Cash Outflows Data taken from breakeven analysis spreadsheet 18 19 Variable Costs 20 Outflow 1 per unit Outflow 2 per unit 22 Outflow 3 per unit Fixed Costs III I I Outflow 1 25 Outflow 2 per month per month 26 Outflow 3 27 Outflow 4 per month 28 Outflow 5 29 Outflow 6 I I per month per month per month III III I I I I I I Outflow 7 per month 1 1 1 1 1 1 1 1 1 1 Total Cash Outflows Total Cash outflows 35 Cash Surplus or (Deficit) Cash Surplus (Deficit) ending cash balance must Beginning Balance Beginning Balance 2, 500 2, 500 2, 500 2, 500 2, 500 2, 500 2, 500 2, 500 2, 500 2, 500 2, 500 2, 500 equal profit component + start up cash of 2,500 (may be off slightly due to Ending Balance Ending Balance 2, 500 2, 500 2, 500 2, 500 2, 500 2, 500 2, 500 2, 500 2, 500 2, 500 2, 500 2, 500 rounding error) 40 Start up cash default =$2,500 42 46Contribution Margin 1 Enter sale price per unit Sale price per unit 300.00 100% Variable costs per unit materials 60.00 2 labor 50.00 Enter variable costs description upplies 20.00 and per unit cost XXXXX xXXXX note: Data entry is required Total Variable Costs 130.00 43% in a minimum of one row Contribution margin per unit S 170.00 57% Contribution margin percentage Fixed Costs equip 2,400 sal 24,000 axe 4,800 Data entry required Enter fixed cost adv 1,200 description and annual cost 3 rent 5,000 note: Data entry is required in a minimum of one row Total Fixed Costs 38,400 Profit Component 4 Enter profit before income tax Profit before income tax 10,000 + Data entry optional Breakeven Analysis Formula Breakeven Point Sales needed to cover Fixed costs + Profit BFIT Units Dollars fixed expenses + profit component Contribution margin 285 5 85,412 Required Sales Volume Sales per Month II II 24 5 7,118 Sales per Week 5 1,643 Sales per Day II 234 Proof Income Statement Sales in units 285 Sale price per unit 300.00 Total sales 85,412 Sales in units 285 Variable cost per unit S 130.00 Total Variable costs 37,012 Contribution margin/Gross Profit S 48,400 Fixed expenses 38,400 Profit before income tax 10,000