Hi Vjvgoe,

Are you able to help me with this problem please? I would need to see it 7/16/17 since it's due that same week but I will put the deadline on 7/29/17. I have attached all of the information that has been provided to me. Could you please use the template that I have attached since that is the one that I must use. On the template there are purple color cells is for the answers/formulas that need to go in. The blue and white/blank cells is information provided in the prompt.

Buns Bakery Basic Information For Year Ended December 31, Year 2 Case Data = Signifies original case assumptions. Sales Information: Estimated % drop: Sales: 20% Muffins Estimated Growth: Cookies Bread 5% Estimated Additional Holiday In Sales Numbers Trasposed for Cop Year 1 Q4 45,000 65,000 85,000 Muffins 2015 Year 2 Q1 Q2 Q3 Q4 36,000 37,800 39,690 47,628 52,000 54,600 57,330 68,796 68,000 71,400 74,970 89,964 43,758 45,946 63,206 66,367 82,654 86,787 Q1 36,000 Cookies 2015 Q1 52,000 Year 3 Q1 Q2 Sales Price/Item: $6.00 $5.25 $5.75 Bread 2015 Q1 68,000 Collection Information: Credit Policy: Cash Sales Credit Sales Collection in Current Quarter Collection in Following Quarter Collection in 3rd Quarter Prior Year Current Year 10% 10% Collection Schedule Transposed for Copying 90% 90% Q1 Q2 30% 30% 37.0% 40.5% 45% 45% 25% 25% Collections from Year 1 Sales: Quarter Q3 Q4 Sold $802,000 $1,002,500 Received $621,550 $370,925 Manufacturing Information: Ending Inventory Goal: 2% Raw Materials Goal: 15% Q1 $180,450 $406,013 $586,463 Payments in Current Year Q2 Q3 $0 $0 $225,563 $0 $225,563 $0 Buns Bakery Basic Information For Year Ended December 31, Year 2 Raw Materials: Muffins Materials: Flour Margarine Sugar Eggs Milk Mini Chips Baking Packet Units Needed 0.5 Cost per Unit $0.15 Total Cost $0.08 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 Cookies Materials: Flour Margarine Sugar Eggs Cocoa PB chips Baking Packet Units Needed 0.5 Cost per Unit $0.15 Total Cost $0.08 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 Bread Materials: Flour Sugar Milk Shortening Baking Packet Units Needed 3 Cost per Unit $0.15 Total Cost $0.45 $0.00 $0.00 $0.00 $0.00 Accounts Payable Payments in Current Quarter Payments in Following Quarter Payments in 3rd Quarter Prior Year Current Year 85% 85% Collection Schedule Transposed for Copying 15% Q1 Q2 0% 85.0% 0.0% Payments on Year 1 Purchases: Quarter Q3 Q4 Purchases Paid Q1 Payments in Current Year Q2 Q3 NA $210,984 $31,648 $31,648 $0 $0 Direct Labor: Dozens Made Per 15 Min. Dozens Made Per Hour Hours Per Dozen Cost Per Dozen $0 $0 Buns Bakery Basic Information For Year Ended December 31, Year 2 Muffins Mixing Baking Packaging Cookies Mixing Baking Packaging Bread Mixing Baking Packaging 8 4 2 32 16 8 0.031 0.063 0.125 $0.23 $0.00 $0.00 0 0 0 0 #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! 0 0 0 0 #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! Variable OH Allocation per DL hr: $2.00 Fixed OH per Quarter: Amount of Fixed OH from Depr: $160,000 8% Manufacturing OH Information: Selling and Administrative Expense Information: Variable S&A for Muffins: Variable S&A for Cookies: Variable S&A for Bread: Fixed S&A Expenses per Quarter: Advertising Cleaning Supplies Janitorial Services Office Staff Salaries Office Supplies Rent - Office Sales Salaries Top Management Salaries Utilities - Office 1% 1% 1% $0.06 $0.05 $0.06 $40,000 Cash Flow and Investment Information: Mortgage Beginning Balance: Interest Rate: Quarterly Interest Rate: Quarterly Payment: $1,109,969 6% 1.5% $20,000 Quarterly Interest Breakdown: Q1 Q2 Q3 Q4 Buns Bakery Basic Information For Year Ended December 31, Year 2 Beginning Bal: Interest Paid: Principal Paid: $1,109,969 $16,649.54 $3,350.47 $1,106,619 $16,599.28 $3,400.72 $1,103,218 $16,548.27 $3,451.73 $1,099,766 $16,496.49 $3,503.51 Requirements Minimum Cash Balance: Dividends Required Dividend Payment each Qtr: Planned Expansion (Purchase of Equipment) Q1 Q2 Q3 Q4 Equipment Purchase Schedule Transposed for Co Q1 Q2 $75,000 $0 $75,000 Line of Credit Information: Line of Credit Max: Interest Rate on LofC: Quarterly Interest Rate Common Stock information: Original Shares of Stock Outstanding: New Shares Sold: Ending Shares of Stock Outstanding Cash from Sale of Stock: 0 Miscellaneous Information: The company's tax rate is: Last year the company paid taxes of: Amount of last year's tax to pay this year: Total tax to prepay this year: Payment due each quarter: $0 $0 Buns Bakery Basic Information For Year Ended December 31, Year 2 Henri Boulangerie Balance Sheet As of December 31, 2014 timated Additional Holiday Increase: 15% Assets umbers Trasposed for Copying 2015 Q2 37,800 Q3 39,690 Q4 47,628 2016 Q1 Q2 43,758 45,946 2015 Q2 54,600 2016 Q3 57,330 Q4 68,796 Q1 63,206 2015 Q2 71,400 Q3 74,970 Q4 89,964 2016 Q1 Q2 82,654 86,787 Q2 66,367 Current Assets Cash Accounts Receivable Raw Materials Inventory Finished Goods Inventory Total Current Assets Property, Plant, and Equipment Land Building Equipment Accumulated Depr - Equipment Total PPE Total Assets Liabilities and Stock Holder's Equity ansposed for Copying Q3 22.5% Year Q4 $0 $0 $0 Liabilities Accounts Payable Mortgage Payable Total Liabilities Stockholder's Equity Common Stock (no par value; 150,000 shares outstanding) Retained Earnings Total Stockholder's Equity Total Liabilities and Stockholder's Equity Profit Ma 10% bo Buns Bakery Basic Information For Year Ended December 31, Year 2 ansposed for Copying Q3 0.0% Year Q4 $0 $0 DL Wage Per Hour Buns Bakery Basic Information For Year Ended December 31, Year 2 $7.50 chedule Transposed for Copying Q3 Q4 $0 $0 Buns Bakery Basic Information For Year Ended December 31, Year 2 Buns Bakery Basic Information For Year Ended December 31, Year 2 gerie eet 31, 2014 $40,000 $13,831 $53,831 $76,000 $567,000 $643,000 $696,831 lder's Equity $31,648 $1,109,969 $1,141,617 ($444,786) ($444,786) $696,831 EPS: CFO: Profit Margin: 10% bonus: #DIV/0! #DIV/0! #DIV/0! #DIV/0! Buns Bakery Basic Information For Year Ended December 31, Year 2 Buns Bakery Basic Information For Year Ended December 31, Year 2 Buns Bakery Basic Information For Year Ended December 31, Year 2 Table 1 - Sales Budget Quarter Q1 Q2 Q3 Q4 Total 2015 Muffins Budgeted Sales Selling price per dozen Total Sales Revenue from Muffins 36,000 $6.00 $216,000 37,800 $6.00 $226,800 39,690 $6.00 $238,140 47,628 $6.00 $285,768 Cookies Budgeted Sales Selling price per dozen Total Sales Revenue for Cookies 52,000 $5.25 $273,000 54,600 $5.25 $286,650 57,330 $5.25 $300,983 68,796 232,726 $5.25 $5.25 $361,179 $1,221,812 Bread Budgeted Sales Selling price per loaf Total Sales Revenue for Bread 68,000 $5.75 $391,000 71,400 $5.75 $410,550 74,970 $5.75 $431,078 89,964 304,334 $5.75 $5.75 $517,293 $1,749,921 Total Sales Revenue $880,000 $924,000 $970,200 $1,164,240 $3,938,440 161,118 $6.00 $966,708 Table 2 - Schedule of Cash Collections Quarter Collections from last year Q1 Q2 Q3 Q4 Total Cash Collections Q1 $586,463 $325,600 $0 $0 $0 $912,063 Q2 $225,563 $356,400 $341,880 $0 $0 $923,843 Total Q3 Q4 $0 $0 $812,025 $198,000 $0 $880,000 $374,220 $207,900 $924,000 $358,974 $392,931 $751,905 $0 $430,769 $430,769 $931,194 $1,031,600 $3,798,699 2016 Q1 Q2 43,758 $6.00 $262,549 45,946 $6.00 $275,677 63,206 $5.25 $331,833 66,367 $5.25 $348,425 82,654 $5.75 $475,263 86,787 $5.75 $499,026 $1,069,646 $1,123,128 Table 3 - Production Budget Quarter Q1 Q2 Q3 Q4 Total 2015 Muffins Budgeted Sales Desired Ending Inventory Total Units Needed Less: Beginning Inventory Required Muffin Production 36,000 840 36,840 (800) 36,040 37,800 882 38,682 (840) 37,842 39,690 1,058 40,748 (882) 39,866 47,628 972 48,600 (1,058) 47,542 161,118 972 162,090 (800) 161,290 Cookies Budgeted Sales Desired Ending Inventory Total Units Needed Less: Beginning Inventory Required Cookie Production 52,000 1,213 53,213 (1,156) 52,058 54,600 1,274 55,874 (1,213) 54,661 57,330 1,529 58,859 (1,274) 57,585 68,796 1,405 70,201 (1,529) 68,672 232,726 1,405 234,131 (1,156) 232,975 Bread Budgeted Sales Desired Ending Inventory Total Units Needed Less: Beginning Inventory Required Bread Production 68,000 1,587 69,587 (1,511) 68,076 71,400 1,666 73,066 (1,587) 71,479 74,970 1,999 76,969 (1,666) 75,303 89,964 1,837 91,801 (1,999) 89,802 304,334 1,837 306,171 (1,511) 304,660 2016 Q1 43,758 1,021 44,779 (972) 43,807 45,946 63,206 1,475 64,681 (1,405) 63,277 66,367 82,654 1,929 84,583 (1,837) 82,746 86,787 Table 4 - Raw Materials Budget Quarter Q2 Q1 Required Production Muffins Cookies Bread Raw Materials Needed Muffins Flour Margarine Sugar Eggs Milk Mini Chips Baking Packet Cookies Flour Margarine Sugar Eggs Cocoa PB chips Baking Packet Bread Flour Sugar Milk Shortening Baking Packet Totals Flour Margarine Sugar Eggs Milk Mini Chips Baking Packet Cocoa PB chips Shortening Desired Ending Inventory Flour Margarine Sugar Q3 Total 2015 Q4 36,040 0 0 0 0 0 0 0 0 Eggs Milk Mini Chips Baking Packet Cocoa PB chips Shortening Total Needs Flour Margarine Sugar Eggs Milk Mini Chips Baking Packet Cocoa PB chips Shortening Less: Beginning Inventory Flour Margarine Sugar Eggs Milk Mini Chips Baking Packet Cocoa PB chips Shortening Raw Materials to be Purchased Flour Margarine Sugar Eggs Milk Mini Chips Baking Packet Cocoa PB chips Shortening Cost per unit of Raw Materials Flour Margarine Sugar 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 $0.15 $0.00 Eggs Milk Mini Chips Baking Packet Cocoa PB chips Shortening Cost of Raw Materials to be purchased Flour Margarine Sugar Eggs Milk Mini Chips Baking Packet Cocoa PB chips Shortening Total Cost $0 $0 Table 5 - Schedule of Cash Payments Accounts Payable, 1/1/15 Q1 Q2 Q3 Q4 Total Cash Paid for Materials Q1 $31,648 $0 $0 $0 $0 $31,648 Quarter Q2 $0 $0 $0 $0 $0 $0 Total Q3 Q4 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $31,648 $0 $0 $0 $0 $31,648 2016 Q1 0 Table 6 - Direct Labor Budget Quarter Q1 Required Production Muffins Cookies Bread DL hours per Unit Muffins Mixing Baking Packaging Cookies Mixing Baking Packaging Bread Mixing Baking Packaging Total DL hours needed Muffins Mixing Baking Packaging Cookies Mixing Baking Packaging Bread Mixing Baking Packaging Total Mixing Baking Packaging DL Cost Per Hour Mixing Baking Packaging DL Cost Mixing Baking Packaging Total DL Cost 36,040 - 0.031 0.063 0.125 #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! 1,126 2,253 4,505 #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! $7.50 $0.00 $0.00 #DIV/0! #DIV/0! #DIV/0! #DIV/0! Q2 Total Q3 Q4 Table 7 - Manufacturing Overhead Budget Budgeted Direct Labor hours Variable Overhead rate Total Variable Overhead Fixed Overhead Total Overhead Q1 #DIV/0! $2.00 #DIV/0! $160,000 #DIV/0! Quarter Q2 Total Q3 Q4 #DIV/0! $2.00 #DIV/0! $160,000 #DIV/0! Total Manufacturing Overhead Budgeted Direct Labor Hours Predetermined Overhead Rate for 2007 #DIV/0! #DIV/0! #DIV/0! Table 8 - Schedule of Cash Payments for Manufacturing Overhead Total Overhead Less: Depreciation and Amortization Cash Payments for Overhead Q1 #DIV/0! ($12,800) #DIV/0! Quarter Q2 Total Q3 Q4 #DIV/0! ($12,800) #DIV/0! Table 9 - Ending Inventory Budget Item Production Cost per Dozen Muffins Direct Materials Flour Margarine Sugar Eggs Milk Mini Chips Baking Packet Total DM Direct Labor Mixing Baking Packaging Total DM OH Quantity 0.5 Cost $0.15 each 0.031 $7.50 per hour #DIV/0! per DL hour Budgeted Finished Goods Inventory - Muffins Ending Finished Goods Inventory Unit Production Costs Ending finished goods inventory in dollars OH 0.5 $0.15 each $0.08 $0.08 #DIV/0! $7.50 per hour #DIV/0! #DIV/0! #DIV/0! hours #DIV/0! per DL hour Budgeted Finished Goods Inventory - Cookies Ending Finished Goods Inventory Unit Production Costs Ending finished goods inventory in dollars Production Cost per Dozen Loaves of Bread Direct Materials Flour 3.00 Sugar Milk Shortening Baking Packet Total DM OH #DIV/0! #DIV/0! 972 #DIV/0! #DIV/0! Unit Production Cost Direct Labor Mixing Baking Packaging Total DM $0.23 $0.23 0.031 hours Direct Labor Mixing Baking Packaging Total DM $0.08 $0.08 Unit Production Cost Production Cost per Dozen Cookies Direct Materials Flour Margarine Sugar Eggs Cocoa PB chips Baking Packet Total DM Total #DIV/0! #DIV/0! #DIV/0! 1,405 #DIV/0! #DIV/0! $0.15 each $0.45 $0.45 $7.50 per hour #DIV/0! #DIV/0! #DIV/0! hours Unit Production Cost #DIV/0! per DL hour #DIV/0! #DIV/0! Budgeted Finished Goods Inventory - Bread Ending Finished Goods Inventory Unit Production Costs Ending finished goods inventory in dollars 1,837 #DIV/0! #DIV/0! Total Ending Inventory #DIV/0! Table 10 - Selling and Administrative Budget Quarter Budgeted sales (by Item) Muffins Cookies Bread Variable S&A Expense Muffins Cookies Bread Total Variable S&A Muffins Cookies Bread Total Variable S&A Budgeted Fixed S&A Expenses Advertising Cleaning Supplies Janitorial Services Office Staff Salaries Office Supplies Rent - Office Sales Salaries Top Management Salaries Utilities - Office Total Budgeted Fixed S&A Expense Total S&A Expense Total Q1 Q2 Q3 Q4 36,000 37,800 39,690 47,628 161,118 $0.06 $0.06 $0.06 $0.06 $0.06 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $40,000 $40,000 $40,000 $40,000 $0 $0 $80,000 $40,000 $40,000 $0 $0 $80,000 $80,000 Table 11 - Cash Budget Cash Balance, Beginning Add Receipts: Cash Collected from Customers Total Cash Available Less: Manufacturing Outflows Direct Materials Direct Labor Variable OH Fixed OH Total Manufacturing Outflows Less: Selling & Administrative Outflows Variable Selling and Administrative Fixed Selling and Administrative Total Selling and Administrative Outflows Total Cash Outflows from Operations Cash Available from Normal Operations Less: Investments Equipment Purchases Cash Available after Investments Miscellaneous Cash Flows Income Taxes Mortgage Payments Interest Principal Sale of Stock, issue of other debt, etc. Dividends Total Miscellaneous Cash Flows Total Cash before Line of Credit Line of Credit Beginning Loan Balance Interest (accumulated) Loans Repayments Principal Interest Ending Cash Balance Q1 $40,000 Quarter Q2 Q3 #DIV/0! #DIV/0! Total Q4 #DIV/0! $40,000 $912,063 $952,063 $923,843 #DIV/0! $931,194 #DIV/0! $1,031,600 #DIV/0! $3,798,699 $3,838,699 ($31,648) #DIV/0! #DIV/0! ($147,200) #DIV/0! $0 $0 $0 ($147,200) ($147,200) $0 $0 $0 ($147,200) ($147,200) $0 $0 $0 ($147,200) ($147,200) ($31,648) #DIV/0! #DIV/0! ($588,800) #DIV/0! $0 ($40,000) ($40,000) $0 ($40,000) ($40,000) $0 $0 $0 $0 $0 $0 $0 ($80,000) ($80,000) #DIV/0! #DIV/0! ($187,200) #DIV/0! ($147,200) #DIV/0! ($147,200) #DIV/0! #DIV/0! #DIV/0! ($75,000) #DIV/0! $0 #DIV/0! $0 #DIV/0! $0 #DIV/0! ($75,000) #DIV/0! $0 $0 $0 $0 $0 ($16,650) ($3,350) $0 $0 ($20,000) #DIV/0! ($16,599) ($3,401) $0 $0 ($20,000) #DIV/0! ($16,548) ($3,452) $0 $0 ($20,000) #DIV/0! ($16,496) ($3,504) $0 $0 ($20,000) #DIV/0! ($66,294) ($13,706) $0 $0 ($80,000) #DIV/0! $0 #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! $0 $0 #DIV/0! $0 $0 #DIV/0! #DIV/0! #DIV/0! #DIV/0! $0 $0 #DIV/0! #DIV/0! #DIV/0! #DIV/0! Table 12 - Pro Forma Income Statement and Accompanying Calculations Henri Boulangerie Pro Forma Income Statement For Year Ended December 31, 2015 Sales Cost of Goods Sold* Gross Profit Less Selling and Administrative Expenses Income before Interest and Taxes $3,938,440 #DIV/0! #DIV/0! ($80,000) #DIV/0! Interest Expense* Income before Taxes #DIV/0! Income Taxes Expense Net Income #DIV/0! #DIV/0! #DIV/0! EPS #DIV/0! *Accompanying Calculations Calculating COGS Beginning FG Inventory Plus: Direct Materials Beginning Raw Materials Inv. Plus: Purchased Less: Ending Raw Materials Inv. (See below) Total DM Plus: Direct Labor Plus: Manufacturing Overhead Total Cost of Goods Available for Sale Less: Ending Inventory COGS $13,831 $0 $0 $0 $0 $0 #DIV/0! #DIV/0! #DIV/0! #DIV/0! Calculating Ending Direct Materials Inventory Ingredient Units on Hand Cost per Unit Flour 0 $0.00 Margarine 0 $0.00 Sugar 0 $0.00 Eggs 0 $0.00 Milk 0 $0.00 Mini Chips 0 $0.00 Baking Packet 0 $0.00 Cocoa 0 $0.00 PB chips 0 $0.00 Shortening 0 $0.00 Total Ending Direct Materials Inv. Calculating Interest Expense Line of Credit interest accrued Mortgage interest accrued Total Interest Expense #DIV/0! $66,294 #DIV/0! Total Cost $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 Table 13 - Pro Forma Balance Sheet and Accompanying Calculations Henri Boulangerie Pro Forma Balance Sheet As of December 31, 2015 Assets Current Assets Cash Accounts Receivable Raw Materials Inventory Finished Good Inventory Total Current Assets Calculation References (a.) (b.) (c.) (d.) #DIV/0! $951,766 $0 #DIV/0! #DIV/0! Property, Plant, and Equipment Land Building Equipment Accumulated Depr - Building and Equipment Total PPE $76,000 (e.) (f.) (g.) (h.) $0 $76,000 Total Assets #DIV/0! Liabilities and Stock Holder's Equity Current Liabilities Accounts Payable Interest Payable Income Tax Payable Total Current Liabilities $0 #DIV/0! #DIV/0! (i.) (j.) (k.) #DIV/0! Long-term Liabilities Bonds Payables Loan Payable Total Long-term Liabilities Total Liabilities ($13,706) #DIV/0! (l.) (m.) #DIV/0! #DIV/0! Stockholder's Equity Common Stock (200,000 shares outstanding) Retained Earnings Total Stockholder's Equity (n.) (o.) #DIV/0! #DIV/0! Total Liabilities and Stockholder's Equity #DIV/0! Accompanying Calculations (a.) from the Cash Budget, ending balance (b.) from the sales budget, the amounts from Q3 and Q4 that have not yet been collected: Quarter Sold Collected Remaining Q3 $970,200 $751,905 $218,295 Q4 $1,164,240 $430,769 $733,471 $951,766 (c.) from income statement calculations (d.) from the ending inventory budget (e.) from prior year, no change (f.) from prior year, no change (g.) total from prior year, plus the current investment (shown in the cash budget.) Year 1 Q1 Q2 Q3 Q4 Total $75,000 $0 $0 $0 $75,000 (h.) total from prior year, plus the current year's depreciation (shown in the OH budget) Year 1 Q1 Q2 Q3 Q4 Total $0 $0 $0 $0 $0 (i.) from the raw materials budget, the amount from Q4 that has not yet been paid: Quarter Purchased Paid Remaining Q4 $0 $0 $0 (j.) Interest accrued (from Interest accrued Interest paid Interest Pay the income statement), less interest paid #DIV/0! #DIV/0! #DIV/0! (k.) the difference between income tax expense (income statement) and income tax paid (cash budget) Inc Tax Accrued #DIV/0! Inc Tax Paid $0 Inc Tax Pay #DIV/0! (l.) Beginning Balance less Principal repaid Beginning Bal. Principal Paid ($13,706) Ending Bal ($13,706) (m.) the difference between loans taken out and repayments Loans #DIV/0! Repayments #DIV/0! Loan Balance #DIV/0! (n.) Beginning Balance less Principal repaid (o.) New retained earnings is the old retained earnings (Last Year Bal Sheet) + net income dividends paid Retained Earn. ($444,786) + Net Income #DIV/0! - Dividends Paid $0 New RE #DIV/0! Table 14 - Pro Forma Statement of Cash Flows with Accompanying Calculations Henri Boulangerie Pro Forma Statement of Cash Flows For Year Ended December 31, 2015 Cash Flows from Operating Activities Net Income Adjustments: Change in Accounts Receivable* Change in Raw Materials Inventory* Change in Finished Good Inventory* Change in Accounts Payable* Change in Interest Payable* Change in Income Tax Payable* Depreciation Total Cash Flows from Operating Activities Cash Flows from Investing Activities Cash Paid for Equipment Total Cash Flows from Investing Activities Cash Flows from Financing Activities Cash Received from Loans Cash Paid on Loans and Mortgage Cash Received from Sale of Stock Cash Paid as Dividends Total Cash Flows from Financing Activities #DIV/0! ($951,766) $0 #DIV/0! ($31,648) #DIV/0! #DIV/0! $12,800 ($75,000) ($75,000) #DIV/0! #DIV/0! $0 $0 #DIV/0! Total Change in Cash Cash Balance, 1/1 Cash Balance, 12/31 #DIV/0! $40,000 #DIV/0! * Accompanying Calculations Beginning A/R Ending A/R Change to Cash from Accounts Receivable Beginning Raw Materials Inv. Ending Raw Materials Inv. Change to Cash from Raw Materials Beginning Finished Goods Inv. Ending Finished Goods Inv. Change to Cash from Finished Goods Ending A/P Beginning Change to Cash from Accounts Payable #DIV/0! #DIV/0! $0 $951,766 ($951,766) $0 $0 $0 $13,831 #DIV/0! #DIV/0! $0 $31,648 ($31,648) Ending Int Payable Beginning Int Payable Change to Cash from Interest Payable #DIV/0! $0 #DIV/0! Ending Inc Tax Payable Ending Inc Tax Payable Change to Cash from Inc Tax Payable #DIV/0! $0 #DIV/0! Exhibit 1 Summary of Ingredients Cookies Ingredients Muffins Cost / Lb. Lbs./Dozen Total / Dozen Lbs./Dozen Total / Dozen Flour $0.15 0.50 $0.08 0.50 $0.08 Margarine $0.25 0.75 $0.19 0.25 $0.06 Sugar $0.20 1.00 $0.20 0.50 $0.10 Eggs (each) $0.05 2.00 $0.10 2.00 $0.10 Milk (per gallon) $1.25 0.10 $0.13 Cocoa $1.50 0.25 $0.38 Peanut Butter Chips $0.75 1.00 $0.75 Mini Chocolate Chips $0.75 1.00 $0.75 Shortening $0.50 Baking Packet* $0.10 1.00 $0.10 1.00 $0.10 $1.79 $1.31 * The Baking Packet consists of ingredients too small to be purchased by the pound, so the bakery buys them in prepared p Bread Lbs./Dozen Total / Dozen 3.00 $0.45 0.25 $0.05 0.25 $0.31 0.25 $0.13 1.00 $0.10 $1.04 e bakery buys them in prepared packets. Exhibit 2 List of Selling and Administrative Expenses S&A Expense Advertising Cost / quarter $40,000 Cleaning supplies 1,000 Janitorial service 6,000 Office staff salaries Office supplies 25,000 3,000 Rent - Office 9,000 Sales salaries 35,000 Top management salarie 80,000 Utilities - Office Total 1,800 $200,800 Exhibit 3 Henri Boulangerie Balance Sheet As of December 31, 2014 Assets Current Assets Cash Accounts Receivable Raw Materials Inventory Finished Goods Inventory Total Current Assets $40,000 812,025 21,098 13,831 $886,954 Property, Plant, and Equipment Land Building Equipment Accumulated Depr - Equipment Total PPE $76,000 567,000 750,000 (90,000) 1,303,000 Total Assets $2,189,954 Liabilities and Stockholders' Equity Liabilities Accounts Payable Mortgage Payable Total Liabilities $31,648 1,109,969 $1,141,617 Stockholders' Equity Common Stock (no par value; 150,000 shares outstanding) Retained Earnings Total Stockholders' Equity $150,000 898,337 1,048,337 Total Liabilities and Stockholders' Equity $2,189,954 Henri Boulangerie: Creating and Using a Master Budget BACKGROUND PART I: CREATING THE BUDGET MEETINGS WITH DIVISIONAL MANAGERS Henri Boulangerie is a medium-sized regional bakery that specializes in providing orders to grocery and convenience stores. Because of the popularity of its brand, it has also opened a small caf for walk-in business. In order to maintain its high quality standard, Henri produces only three products: breakfast muffins, fresh bread, and chocolate chip cookies. Although business has been good in the past few years, a lucky contact with a large chain has recently allowed it to expand its brand out of the local region. Growth has been high since the new contract went into effect. Andy Griff, the chief executive officer (CEO) and founder, has arranged a meeting with a venture capital firm next week. Hopefully the meeting will result in the sale of some of Henri's stock and an opportunity to establish a significant line of credit with the venture capital firm. These extra funds, if Andy can secure them, should provide sufficient money to meet Henri's growth targets for the next few years. The venture capital firm's assessment team has asked Andy to provide a quarterly master budget for the year that just began, complete with pro forma financial statements, at the meeting. They have expressed special interest in Henri's earnings per share (EPS), cash flow from operations, and profit margins, indicating that good numbers in these areas will be essential for final approval. In typical managerial style, Andy immediately assigned the task of creating the budget to Nicole Quarterman, who has just been hired as Henri's controller. Since this project is her first assignment, Nicole started by making appointments with each of the divisional managers to gather information for the budget and also to learn more about the company. MEETING WITH THE SALES DEPARTMENT Walking down the hallway towards the office of Jeff Barza, the sales manager, Nicole read the results for last quarter which ended on December 31, 2014. Henri Boulangerie sold 45,000 one-dozen packages of muffins for $5.50 each, 65,000 one-dozen packages of cookies for $4.75 each, and 85,000 one-dozen loaves of bread for $5.25 each. When Nicole got to Jeff's office, he motioned her in to have a seat. \"Is it time for our meeting already?\" he asked. \"Where does the day go?\" \"Who knows? It seems like one minute I'm having my morning muffin and the next I'm saying good-bye to everyone,\" Nicole said with a sigh. \"There's never time to get everything done. And now I get to do the budget.\" Jeff started to laugh. \"Thanks,\" she muttered. \"I knew I could count on your support.\" \"I'm sorry. I just have to laugh at the amount of time you are going to put into something that isn't really used anyway, except for setting bonuses, of course.\" \"Not really used? I don't know how it's been around here in the past, but this year, at least, the budget will prove to be a valuable tool.\" Nicole waved away Jeff's retort. \"Anyway, one way or another I have to create one and, as you know, the process always starts with projected sales. Do you have a copy of last quarter's results?\" \"Yes, right here somewhere,\" Jeff said, shuffling papers around on his desk. \"Got it!\" he exclaimed, waving it gently as he pulled it from under a stack of other papers. \"Now, what do you want to know exactly?\" \"Well, Andy thinks that since we have established a strong following both locally and in our new markets, we 1 Jeff smiled. \"Yep, but we do sell on credit to the business customers. If we didn't, they'd definitely go somewhere else. So, we give our business customers a lot of leeway in paying us. It makes it a little hard on us, but it keeps them loyal. Anyway, we collect 30 percent of the credit sales within the current quarter, 45 percent in the following quarter, and 25 percent in the quarter after that. The good news is that we don't have any bad debt. Our customers are mostly large chains with strong sales and even better reputations. Since they are large companies, they take their time paying small companies like us, but we get the money from all of them in the end.\" \"Then I have only two more questions. What were total sales during the third and fourth quarters of last year, and are we still collecting any of that money?\" Jeff pulled up a file. \"Total sales were $802,000 and $1,002,500, respectively, and we are still collecting quite a bit of that money based on our collection breakdown.\" \"I think that does it, then. If I've forgotten something, I'll come back and bug you later. It's more fun to interrupt you several times anyway. And you owe me one now.\" can raise our prices slightly next year without a sharp drop in sales. He was thinking $6.00 for muffins, $5.25 for cookies, and $5.75 for bread. What do you think?\" \"I agree,\" Jeff said eagerly. \"I've been pushing that for years. Of course, I think that sales will drop some in the first quarter of next year. They always drop off a bit after the holidays anyway, but with the increase in sales price . . . I'd say a 20 percent drop from the fourth quarter results we have here.\" He looked up questioningly and raised an eyebrow. Nicole frowned. \"That sounds kind of high. Based on what I saw in the dairy industry, I was thinking the drop would only be about 10 percent.\" Jeff looked a little uncomfortable and shuffled around in his chair. \"Well, it's a little different for a Boulangerie. Our price is a little more elastic than dairy products. Besides, 20 percent is a more conservative estimate and in the past we wanted no surprises.\" He looked at her and challenged, \"Are you going to change that?\" \"Yeah, but we'll be using this master budget to create a cash flow budget and pro forma financial statements to show our new investor. We need to look good, not bad.\" Nicole frowned. She didn't want to start making changes and enemies in her first few months. \"I guess so. But, lookmy bonus is tied to how well I meet my estimates. If we estimate low results and then go up . . .\" Seeing the look on her face, he quickly changed direction. \"Besides, Nicole, we are raising the prices. A 10 percent drop is normal after Christmas, but couple that with the increased prices, and 20 percent is reasonable.\" Nicole frowned, and then sighed. She didn't quite accept his reasoning, but it would be better to have him on her side until she understood the company politics a bit better. \"Okay, Jeff. I'll take your word for it. We'll use 20 percent. After all, you're the expert.\" \"You've got that right!\" Jeff said, trying to hide his relief. He was obviously really counting on that bonus. He looked at a couple of sales reports and market projections on the desk in front of him. \"After that, I think sales will grow steadily at about 5 percent a quarter with these new prices. Fourth-quarter sales will be high because of the holidays let's say 20 percent, instead of 5 percent, from the third to the fourth quarter. The first quarter of the following year will continue the 5 percent growth as though the holiday jump didn't occur. And I'm not messing with those estimates. That's really my best guess, given what I've seen in the past.\" He looked up. \"Does that give you all you need?\" \"Just a few more questions. Have you made any changes to the credit policy? The information I have from last year says that we make about 10 percent of our sales through our caf and that we don't sell to those customers on credit.\" MEETING WITH THE PRODUCTION DEPARTMENT Nicole sighed as she headed to her meeting with Phil Mainster, Henri's head chef. She wasn't sure about that large drop Jeff wanted her to use, but as the new member of the staff she wasn't sure what she should do. Of course, she didn't have much time to think about it now anyway. She had met Phil before, so she knew that it was going to be an interesting meeting. As she had suspected, she found Phil in the Boulangerie instead of his office. \"Phil,\" she called as she hurried towards him, \"did you forget our meeting?\" \"Me, forget?\" Phil asked in a surprised voice. \"I never forget anything!\" Nicole had to chuckle at the large streak of flour across his face. \"You said you wanted to see our production facility, and I'm ready to show it to you.\" Nicole shook her head. \"No, Phil. I didn't say I wanted to see the production facility; I said I wanted to talk to you about the budget for next year.\" \"Oh, of course you did.\" Phil's round face had turned a deeper shade of pink. \"Then why don't we go to my office and talk?\" Nicole sighed. \"That's a great idea, Phil.\" As they sat down, Nicole asked her first question. \"Okay, Phil, I need to know how much inventory we keep on hand.\" \"Well, we can't keep much in the way of finished goods on hand. My cookies and bread would dry out if we kept them too long. I'd say that we normally keep only about two days' worth of inventory on hand to avoid shipping issues or problems with the caf.\" 2 \"Okay and you make your estimates based on a 90-day quarter?\" Phil nodded impatiently. \"Please, Nicole, don't ask obvious questions.\" \"I'm sorry. Let's talk about your pantry. You take care of purchasing too, don't you?\" \"Yessirree. We decided it would be easier for me to run purchasing than to have a separate manager do it. After all, I do everything else around here.\" \"Well, we want it done right.\" Phil chuckled. \"I'll have to remember that one. Martha will love it. Okay, let's talk raw materials. Some days we have to produce a lot to meet our orders, so I normally try to keep 15 percent of the next quarter's raw materials on hand at all times.\" \"Is that what we've got on hand now for the coming year?\" \"Of course. Jeff and I had already talked about the possibility of raising prices and his estimate of a 20 percent drop in demand, so I'm ready to go.\" Nicole considered telling Phil that she was unsure the 20 percent drop would really materialize, but changed her mind. There would be time to get the extra ingredients ordered if sales only dropped 10 percent, and she didn't want anyone to think she had caved in to peer pressure. \"Good. Can you give me some estimates of how long it takes to make each package of cookies, bread, and muffins?\" start to finish. We do them in large batches, so I have no idea how long each final package takes.\" Seeing Nicole's frown, he quickly went on. \"But, I can tell you that one of my mixers can mix together either 12 dozen cookies, 8 dozen muffins, or 4 dozen loaves of bread in 15 minutes. The bakers then take another half an hour to get the dough ready and bake it.\" \"The batch sizes are the same for each product?\" \"Yep. I try to keep things as standard as possible. The packaging department is the slowest. They have to double wrap the cookies and muffinsonce to keep them fresh and once in the fancy packages marketing came up withso it takes 15 minutes to package either two one-dozen packages of cookies or two one-dozen packages of muffins. The bread is a little faster. In 15 minutes we can package about eight dozen loaves of bread.\" \"Do you happen to know what we are paying each group of employees?\" Phil grabbed a piece of paper. \"We pay the mixers $7.50 an hour, the bakers $8.00 an hour, and the packers $6.50 an hour.\" \"Perfect. Then I just have one more question.\" \"Let me guess. You want a breakdown of ingredients for each item we bake.\" \"You must be psychic, Phil.\" \"No, I just remember being bugged about this by the last controller.\" He handed Nicole a piece of paper with a table on it. \"Here they all are. Just make sure you don't let it out of the building! I don't want my secret recipes to get out.\" \"Don't worry. I'll be careful.\" Nicole glanced down at the price sheet. \"Wow. I wish I could buy my groceries at these prices.\" \"Are you kidding? We don't really move each item from Phil chuckled. \"So do I. You have to remember, though, Exhibit 1 Summary of Ingredients Cookies Ingredients Cost / Lb. Lbs./Dozen Muffins Total / Dozen Lbs./Dozen Bread Total / Dozen Flour $0.15 0.50 $0.08 0.50 $0.08 Margarine $0.25 0.75 $0.19 0.25 $0.06 Sugar $0.20 1.00 $0.20 0.50 $0.10 Eggs (each) $0.05 2.00 $0.10 2.00 $0.10 Milk (per gallon) $1.25 0.10 $0.13 Cocoa $1.50 0.25 $0.38 Peanut Butter Chips $0.75 1.00 $0.75 Mini Chocolate Chips $0.75 1.00 $0.75 Shortening $0.50 Baking Packet* $0.10 1.00 $0.10 1.00 $0.10 $1.79 $1.31 Lbs./Dozen Total / Dozen 3.00 $0.45 0.25 $0.05 0.25 $0.31 0.25 $0.13 1.00 $0.10 $1.04 * The Baking Packet consists of ingredients too small to be purchased by the pound, so the bakery buys them in prepared packets. 3 that we buy in bulk, lots and lots of bulk. That lets us get some great deals from our local vendors.\" \"I guess that makes sense. Thanks for taking time to see me.\" \"Just make sure you don't leave without taking a cookie or two.\" Phil held out a plate loaded with perfect, if two-day old, cookies. \"If we don't eat them, they go into the trash!\" \"My pleasure!\" labor hours to calculate a predetermined overhead rate when calculating the unit cost.\" \"Unit cost?\" asked Bob. \"Oh, wait,\" he said nodding, \"I remember. We have to include direct materials, direct labor, and manufacturing overhead to get the cost of producing each unit. Direct materials are calculated from the recipe and direct labor cost from the employee information that Phil gave you. But we need to multiply the number of hours it takes to make each product by the predetermined overhead rate so that we can figure a per-unit applied overhead amount. Sorry to interrupt.\" MEETING WITH THE ACCOUNTING DEPARTMENT Nicole hurried back to her own office. She had a staff meeting in 15 minutes. She should be able to get most of the information she still needed from Sarah, since she wrote the checks. Even though Sarah only worked part-time, she'd been with the Boulangerie from the beginning and seemed to know just about everything about the accounting system. Anything Sarah didn't know, Bob, their new summer intern, would have found out for her by now. He was very good at digging up information once he was pointed in the right direction. \"We thought you were going to stand us up,\" Sarah said as Nicole hurried into the office. \"Actually, we hoped you were,\" Bob quipped. \"We don't want to get stuck doing the budget, so we hoped that you would forget to come.\" \"Don't worry,\" Nicole said with a sigh. \"Andy wants me to take care of it personally. He seems to think it would be good for me to get to know the company or something. So, have you gathered all the information that I asked for?\" \"Of course,\" Sarah said. \"Where do you want us to start?\" \"Let's start with our accounts payable.\" \"That's me,\" Bob said. \"Most of our vendors require that we pay for everything within 30 days of making our purchase. That means that 85 percent of our purchases are paid for within the quarter they are made. And, before you ask, we ordered $210,984 worth of inventory during the last quarter last year, so we still owe 15 percent of that, or $31,648.\" \"Thanks, Bob, but I actually knew that last part. After all, it's right there in the balance sheet.\" \"Oh, yeah,\" Bob said turning pink. \"I forgot about that.\" Sarah laughed. \"So, you calculated it by hand?\" \"Well, yeah. I wanted to be prepared for the meeting today.\" \"All right, you two,\" said Nicole, jumping in before Sarah could pick on the young man any more. \"Let's move on to our overhead assumptions.\" \"Sure,\" Sarah said. \"Last year we allocated variable overhead at $1.50 for each direct labor hour. This year, I think that we're going to need to increase that to $2.00 to cover increases in security fees, utility rates, and energy prices. We also spend about $160,000 a quarter in fixed overhead. Also, don't forget that we usually use total direct \"No problem.\" Nicole nodded approvingly at the young intern while finishing up her notes. \"Just one last question, Sarah. How much of that overhead is from depreciation?\" \"Eight percent of the fixed amount.\" \" B ob, tell me about our sales costs.\" \"Well, we don't really have that much in variable sales costs. We give a one percent commission to our sales staff.\" \"Is that based on profit or sales price?\" Sarah asked. \"Total sales price. Sorry, I forgot to mention that. The commission is paid both for business sales and sales in the caf. Also, here's the table of fixed selling and administrative expenses.\" Exhibit 2 List of Selling and Administrative Expenses S&A Expense Advertising Cost / quarter $40,000 Cleaning supplies 1,000 Janitorial service 6,000 Office staff salaries Office supplies Rent - Office 25,000 3,000 9,000 Sales salaries 35,000 Top management salar 80,000 Utilities - Office Total 1,800 $200,800 Nicole took the paper. \"Thanks. Okay, Sarah, tell me about our debt.\" \"Well, at the end of last year, we secured a $1,109,969 mortgage at 6 percent interest. Our payment each quarter is $20,000. Since it's a mortgage, the calculations are kind of fun. Each payment requires us to pay a bunch of interest and a little bit of principal. To break up the $20,000 into the two parts, we have to multiply the current mortgage value by 6 percent and divide by 4. . .\" 4 \"Divide by 4?\" asked Bob. \"Well, yeah, 6 percent is the annual rate. Since we make quarterly payments, we divide the annual rate by 4.\" \"Oh,\" Bob said sheepishly. \"I should have remembered that.\" \"Yes, you should have,\" Nicole said with a smile. She was very pleased with how well Bob was progressing during his summer with the firm. Hiring an intern had been one of her first changes, and it seemed to be working out well. If the company continued to grow, maybe he could be hired full-time once he graduated in a couple of years. \"Go ahead, Sarah.\" \"Right. So, our first payment will be made at the end of the upcoming quarter. We'll end up paying $16,650 as interest and $3,350 in principal. This means that the value of the mortgage in the second quarter will be $1,106,619. That's the original $1,109,969 minus the $3,350, Bob.\" \"Thanks, Sarah. I appreciate the help,\" Bob retorted, rolling his eyes. \"I appreciate it, too,\" Nicole said. \"If I remember right, we have to pay the $20,000 each quarter. Our contract prohibits us from paying any additional principal for the first three years.\" Sarah nodded. \"Yep, kind of a bummer, but that was the only way we could get that 6 percent interest rate.\" \"Okay,\" Nicole said. \"The last thing is a recap of how we handle income taxes. I think that has pretty much stayed the same?\" \"It sure has,\" Bob responded, rifling through a tax folder. \"Our corporate tax rate is 30 percent and a portion of our estimated taxes must be paid each quarter to avoid late fees. Our policy is to pay 110 percent of the taxes that we owed last year over the course of the current year. Since we paid $15,000 last year, we will need to pay $16,500 this year.\" \"And we'll pay that equally over the four quarters?\" \"Right. At the end of the year, we calculate our actual taxes owed as 30 percent of net income. Any difference between the cash we paid for taxes over the year and actual income tax expense on the income statement is put into income taxes payable if we haven't paid enough and into deferred tax assets if we paid too much.\" \"Right,\" Nicole said. \"I think that's about it.\" \"Don't forget the balance sheet from last year,\" Bob said, handing her a sheet of paper. \"Thanks. I'm starting to lose track of everything. I must be getting old.\" \"Oh, I wouldn't say that,\" Bob quipped, then added with a grin, \"at least, not as long as you're my boss.\" Exhibit 3 Henri Boulangerie Balance Sheet As of December 31, 2014 Assets Current Assets Cash Accounts Receivable Raw Materials Inventory Finished Goods Inventory Total Current Assets $40,000 812,025 21,098 13,831 $886,954 Property, Plant, and Equipment Land Building Equipment Accumulated Depr - Equipment Total PPE $76,000 567,000 750,000 (90,000) 1,303,000 Total Assets $2,189,954 Liabilities and Stockholders' Equity Liabilities Accounts Payable Mortgage Payable Total Liabilities $31,648 1,109,969 $1,141,617 Stockholders' Equity Common Stock (no par value; 150,000 shares outstanding) Retained Earnings Total Stockholders' Equity Total Liabilities and Stockholders' Equity $150,000 898,337 1,048,337 $2,189,954 MEETING WITH THE CEO \"So, how goes the battle, Nicole?\" Andy asked as she came into his office. \"Oh, it's going. Actually, I think we're just about there. I just need to check some numbers with you, and I'll be all set. Then it's just a matter of actually creating the budget. That's the fun part, you know.\" Andy laughed. \"Right. That's why you're the accountant and I'm not. So, what do you need?\" 5 simple interest calculation.\" \"I think you lost me somewhere in there.\" \"Sorry about that. Sometimes I go too fast. To get our interest payments when we repay our line of credit (assuming that we have any to repay and the funds to make a payment), I will multiply the amount I'm repaying times the quarterly interest rate times the number of quarters the money has been outstanding. So, if we draw $1,000 on the line of credit in the second quarter and repay it in the third quarter, I will multiply $1,000 by 2 percent and again by 2 percent for the two quarters that I assume it's been outstanding. Does that help?\" \"Not really, but I think I understand enough that I can explain your assumptions if I have to.\" \"Well, let me try again . . .\" \"No, don't worry about it,\" Andy said quickly. \"As long as I know about what you're doing, and I don't have to do it myself, I'm good with just about anything.\" Nicole grimaced. \"Thanks. I think I'll estimate my salary up a couple hundred thousand,\" she said jokingly. \"First, I just want to confirm a couple of things from some earlier meetings. You told me a couple of weeks ago that the board of directors now wants us to have $40,000 worth of cash on hand at all times and to pay $25,000 in dividends each quarter. Is that still the plan?\" \"Yes it is. I think it's a little restrictive myself, but sometimes we have to do as we're told. Because of the expansion, though, we are going to have to issue another 50,000 shares of common stock to the venture capital firm in the first week of the third quarter. We won't plan on changing our dividend payment schedule this year, but we will probably have to increase the amount we pay in future years. For now, though, the big factor is the capital infusion of $400,000 we'll get from selling our stock.\" \"In the third quarter? Why are they waiting that long?\" Andy shrugged. \"Because that's when they will have the money to make the investment. They're waiting for another deal to go through.\" \"Okay, so increase common stock issued by 50,000 shares and paid-in-capital by $400,000 in the third quarter, got it. My next question is about the expansion to our PPE that you just mentioned. I estimate that we will need to buy $75,000 worth of new equipment in the first quarter, $100,000 in the second, $50,000 in the third, and $35,000 in the fourth. Since many of our long-term assets have already been fully depreciated, this new expansion shouldn't significantly change my depreciation estimates. Does that sound about right to you?\" \"Assuming we get this arrangement settled, it sounds perfect.\" \"Can you give me a few more details about what else we're hoping to get from these new investors? I'll need to include those estimates.\" \"Sure. What we are really hoping for, other than the purchase of 50,000 shares of stock of course, is a $1 million revolving line of credit. Basically, if we need additional funding we can pull on the line of credit. The interest rate on the new credit line will be 8 percent and they will require that we pay off any accumulated interest before we repay any principal.\" PART II - USING THE MASTER BUDGET MEETING OF THE SENIOR STAFF (TWO WEEKS LATER) \"Alright, everyone, let's settle down and get to work.\" Everyone took their seats around the table as Andy, Henri Boulangerie's CEO, shuffled through his papers. \"As you know, the venture capital firm we are hoping to work with has indicated that it will not approve the deal unless we can demonstrate a strong projected EPS, cash flow from operations, and profit margin. Since you have all had a chance to review our new master budget and our pro forma financial statements, you know that we're in bit of trouble along those lines. To put it bluntly, the numbers we are currently showing are not good enough for the deal. No deal, no funds. No funds, no growth. No growth, no big bonuses.\" He paused for a moment. \"So, does anyone have any ideas for ways that we can legitimately improve our numbers?\" \"What exactly do you mean by 'legitimately'?\" Phil, the head baker, asked. \"I mean ways that we can change our policies or procedures.\" \"I guess that means my idea of robbing a bank is out,\" Phil said dryly. \"And my idea of simply randomly changing numbers,\" agreed Sarah, the part-time staff accountant. \"Well, I think that gives me everything I need. Just so you know, I am going to use simple interest calculations for the interest estimates. It's not 100 percent accurate, but it is typical for creating a master budget. It also simplifies things considerably and ensures that information flows through the budget easily. I'll also assume that any additional debt from the line of credit is taken out on the first day of the quarter and any payments are made at the end of the quarter. That ensures that the interest estimates should be fairly accurate, even with the \"Well,\" Jeff, the head of marketing, said, \"I think I have a legitimate idea. We could increase our sales commissions to 2 percent. That should motivate our sales force to sell more. I'd 6 say that would increase our sales growth from 5 to 8 percent each quarter.\" \"For my part,\" Phil jumped in, \"we could switch to a JIT inventory system, keeping only about 3 percent of our needed raw materials on hand. That would cut down on some of our costs, but it would also require us to pay for our entire inventory in the quarter it is purchased rather than paying 15 percent in the following quarter like we do now.\" Nicole, Henri's new controller, shook her head. \"I think our best bet is to speed up our collections. We're too loose with our credit. If we were to add an additional collections specialist to our office staff, we could improve our collections to be 80 percent in the first quarter, 15 percent in the second quarter, and 5 percent in the third quarter. That would certainly improve our cash flows. Given the job market right now, I think we could hire a good collections specialist for $30,000 a year.\" \"They might help collections,\" argued Jeff, \"but those kinds of tactics could hurt our sales. Our relaxed collections policy is one of the things that set us apart from other vendors. If you decide to try that, Andy, you'd better plan on an additional 3 percent drop in sales the first quarter.\" The table started to buzz with conversation as the managers discussed the different options that had been presented. In the confusion, Nicole took her chance to lean over to Jeff. \"Don't you want to tell them?\" \"Tell them what?\" he said innocently. \"That you have us dropping our sales by too much in the first quarter! If we changed our current 20 percent estimate to a more realistic drop, it would take care of everything! Based on the research I've been doing in the industry, we could use 10 percent instead of 20. Think about it. Our EPS would be higher and so would our cash flow from operations. Why, even our profit margin would increase because our fixed costs would be allocated over more units.\" Jeff frowned at her. \"It wouldn't improve everything, Nicole. It would totally kill my bonus. Look, the raise in price is a good idea with these other changes we are making. I mean, we're going to need the extra cash, but that is going to cost us some sales. I'd much rather be conservative and get a great bonus than give them a rosy number and get fired.\" Nicole sighed. \"Jeff . . .\" she tried again. \"Another option,\" Jeff said loudly, before Nicole could start in on him again, \"would be to not raise our prices as drastically. Let's say we only increased our prices to $5.75 for muffins, $5.00 for cookies, and $5.50 for bread. By my calculations, that would lead to only a 12 percent drop in sales in quarter one with 7 percent growth in each of the following quarters.\" Nicole frowned. Given Jeff's pattern, it would be more like a 2 percent drop, not 12 percent. Then she sighed. She couldn't win this argument with Jeff, especially not in the middle of a meeting with everyone else watching and listening. Besides, if she brought it up now, they would wonder why she hadn't brought it up before. They might even think that she'd been trying to get a bigger bonus for herself. And she would certainly make an enemy out of Jeff. Their relationship was already strained. No, she couldn't say anything here. She'd just have to let it go and hope that one of the other ideas would work out. Besides they really would look better if they pulled off a significant improvement this first year. And if that happened, would it really matter that Jeff had manipulated his way to a nice fat bonus? \"Well,\" she said after a few more minutes, \"I think these are all good ideas, but I'm not sure that we'll want to try all of them. If we change too much at one time the assessment team might think that we are just trying to fake our numbers to give them what they want. I would suggest making one or maybe two of these changes for now, then provide them with a written explanation of the other ideas we want to try moving forward.\" \"I agree, Nicole,\" Andy said. \"Why don't you run the numbers, including how these changes would affect our use of the line of credit, to see which of the changes will give us the most bang. We'll go ahead and make that change now and add the others to our improvement plan. That way, we can go to them with a current improvement and a plan to keep improving.\" He looked around the table. \"And if anyone gets any other ideas, let us know. The more improvements we take to the table, the better our chances of signing the deal.\" This Henri Boulangerie case is based upon an original IMA case written by Jason Porter and Teresa Stephenson. About the IMA With a worldwide network of more than 70,000 professionals, IMA is the world's leading organization dedicated to empowering accounting and finance professionals to drive business performance. IMA provides a dynamic forum for professionals to advance their careers through Certified Management Accountant (CMA) certification, research, professional education, networking and advocacy of the highest ethical and professional standards. For more information about IMA, please visit www.imanet.org. 7 Buns Bakery Basic Information For Year Ended December 31, Year 2 Case Data = Signifies original case assumptions. Sales Information: Estimated % drop: Sales: 20% Muffins Estimated Growth: Cookies Bread 5% Estimated Additional Holiday In Sales Numbers Trasposed for Cop Year 1 Q4 45,000 65,000 85,000 Muffins 2015 Year 2 Q1 Q2 Q3 Q4 36,000 37,800 39,690 47,628 52,000 54,600 57,330 68,796 68,000 71,400 74,970 89,964 43,758 45,946 63,206 66,367 82,654 86,787 Q1 36,000 Cookies 2015 Q1 52,000 Year 3 Q1 Q2 Sales Price/Item: $6.00 $5.25 $5.75 Bread 2015 Q1 68,000 Collection Information: Credit Policy: Cash Sales Credit Sales Collection in Current Quarter Collection in Following Quarter Collection in 3rd Quarter Prior Year Current Year 10% 10% Collection Schedule Transposed for Copying 90% 90% Q1 Q2 30% 30% 37.0% 40.5% 45% 45% 25% 25% Collections from Year 1 Sales: Quarter Q3 Q4 Sold $802,000 $1,002,500 Received $621,550 $370,925 Manufacturing Information: Ending Inventory Goal: 2% Raw Materials Goal: 15% Q1 $180,450 $406,013 $586,463 Payments in Current Year Q2 Q3 $0 $0 $225,563 $0 $225,563 $0 Buns Bakery Basic Information For Year Ended December 31, Year 2 Raw Materials: Muffins Materials: Flour Margarine Sugar Eggs Milk Mini Chips Baking Packet Units Needed 0.50 0.25 0.50 2.00 0.10 1.00 1.00 Cost per Unit $0.15 $0.25 $0.20 $0.05 $1.25 $0.75 $0.10 Total Cost $0.08 $0.06 $0.10 $0.10 $0.13 $0.75 $0.10 Cookies Materials: Flour Margarine Sugar Eggs Cocoa PB chips Baking Packet Units Needed 0.50 0.75 1.00 2.00 0.25 1.00 1.00 Cost per Unit $0.15 $0.25 $0.20 $0.05 $1.50 $0.75 $0.10 Total Cost $0.08 $0.19 $0.20 $0.10 $0.38 $0.75 $0.10 Bread Materials: Flour Sugar Milk Shortening Baking Packet Units Needed 3.00 0.25 0.25 0.25 1.00 Cost per Unit $0.15 $0.20 $1.25 $0.50 $0.10 Total Cost $0.45 $0.05 $0.31 $0.13 $0.10 Accounts Payable Payments in Current Quarter Payments in Following Quarter Payments in 3rd Quarter Please check: This needs to be filled in? Please: This needs to be filled in? Prior Year Current Year 85% 85% Collection Schedule Transposed for Copying 15% Q1 Q2 0% 85.0% 0.0% Payments on Year 1 Purchases: Quarter Q3 Q4 Purchases Paid Q1 Payments in Current Year Q2 Q3 NA $210,984 $31,648 $31,648 $0 $0 Direct Labor: Dozens Made Per 15 Min. Dozens Made Per Hour Hours Per Dozen Cost Per Dozen $0 $0 Buns Bakery Basic Information For Year Ended December 31, Year 2 Muffins Mixing Baking Packaging Cookies Mixing Baking Packaging Bread Mixing Baking Packaging 8 4 2 32 16 8 0.031 0.063 0.125 $0.23 $0.50 $0.81 12 6 2 48 24 8 0.021 0.042 0.125 $0.16 $0.33 $0.81 4 2 8 16 8 32 0.063 0.125 0.031 $0.47 $1.00 $0.20 Variable OH Allocation per DL hr: $2.00 Fixed OH per Quarter: Amount of Fixed OH from Depr: $160,000 8% Manufacturing OH Information: Selling and Administrative Expense Information: Variable S&A for Muffins: Variable S&A for Cookies: Variable S&A for Bread: Fixed S&A Expenses per Quarter: Advertising Cleaning Supplies Janitorial Services Office Staff Salaries Office Supplies Rent - Office Sales Salaries Top Management Salaries Utilities - Office 1% 1% 1% $0.06 $0.05 $0.06 $40,000 $1,000 $6,000 $25,000 $3,000 $9,000 $35,000 $80,000 $1,800 Cash Flow and Investment Information: Mortgage Beginning Balance: Interest Rate: Quarterly Interest Rate: Quarterly Payment: $1,109,969 6% 1.5% $20,000 Quarterly Interest Breakdown: Q1 Q2 Q3 Q4 Buns Bakery Basic Information For Year Ended December 31, Year 2 Beginning Bal: Interest Paid: Principal Paid: $1,109,969 $16,649.54 $3,350.47 Minimum Cash Balance: $40,000 Dividends Required Dividend Payment each Qtr: $25,000 Planned Expansion (Purchase of Equipment) Q1 Q2 Q3 Q4 $75,000 $100,000 $50,000 $35,000 $1,106,619 $16,599.28 $3,400.72 $1,103,218 $16,548.27 $3,451.73 $1,099,766 $16,496.49 $3,503.51 Requirements Line of Credit Information: Line of Credit Max: Interest Rate on LofC: Quarterly Interest Rate $1,000,000 8% 2% Common Stock information: Original Shares of Stock Outstanding: New Shares Sold: Ending Shares of Stock Outstanding Cash from Sale of Stock: 150,000 50,000 200,000 $400,000 Equipment Purchase Schedule Transposed for Co Q1 Q2 $75,000 $100,000 Miscellaneous Information: The company's tax rate is: Last year the company paid taxes of: Amount of last year's tax to pay this year: Total tax to prepay this year: Payment due each quarter: 30% $15,000 110% $16,500 $4,125 Buns Bakery Basic Information For Year Ended December 31, Year 2 Henri Boulangerie Balance Sheet As of December 31, 2014 timated Additional Holiday Increase: 15% Assets umbers Trasposed for Copying 2015 Q2 37,800 Q3 39,690 Q4 47,628 2016 Q1 Q2 43,758 45,946 2015 Q2 54,600 2016 Q3 57,330 Q4 68,796 Q1 63,206 2015 Q2 71,400 Q3 74,970 Q4 89,964 2016 Q1 Q2 82,654 86,787 Q2 66,367 Current Assets Cash Accounts Receivable Raw Materials Inventory Finished Goods Inventory Total Current Assets Property, Plant, and Equipment Land Building Equipment Accumulated Depr - Equipment Total PPE Total Assets Liabilities and Stock Holder's Equity ansposed for Copying Q3 22.5% Year Q4 $0 $0 $0 Liabilities Accounts Payable Mortgage Payable Total Liabilities Stockholder's Equity Common Stock (no par value; 150,000 shares outstanding) Retained Earnings Total Stockholder's Equity Total Liabilities and Stockholder's Equity Profit Ma 10% bo Buns Bakery Basic Information For Year Ended December 31, Year 2 Please check: This needs to be filled in? ansposed for Copying Q3 0.0% Please check: This needs to be filled in? Year Q4 $0 $0 DL Wage Per Hour Buns Bakery Basic Information For Year Ended December 31, Year 2 $7.50 $8.00 $6.50 chedule Transposed for Copying Q3 Q4 $50,000 $35,000 Buns Bakery Basic Information For Year Ended December 31, Year 2 Buns Bakery Basic Information For Year Ended December 31, Year 2 gerie eet 31, 2014 $40,000 $812,025 $21,098 $13,831 $886,954 $76,000 $567,000 $750,000 ($90,000) $1,303,000 $2,189,954 lder's Equity $31,648 $1,109,969 $1,141,617 $150,000 $898,337 $1,048,337 $2,189,954 EPS: $0.34 CFO: ($20,220) Profit Margin: 1.7% 10% bonus: $15,748 Buns Bakery Basic Information For Year Ended December 31, Year 2 Buns Bakery Basic Information For Year Ended December 31, Year 2 Buns Bakery Basic Information For Year Ended December 31, Year 2 Table 1 - Sales Budget Quarter Q1 Q2 Q3 Q4 Total 2015 Muffins Budgeted Sales Selling price per dozen Total Sales Revenue from Muffins 36,000 $6.00 $216,000 37,800 $6.00 $226,800 39,690 $6.00 $238,140 47,628 $6.00 $285,768 Cookies Budgeted Sales Selling price per dozen Total Sales Revenue for Cookies 52,000 $5.25 $273,000 54,600 $5.25 $286,650 57,330 $5.25 $300,983 68,796 232,726 $5.25 $5.25 $361,179 $1,221,812 Bread Budgeted Sales Selling price per loaf Total Sales Revenue for Bread 68,000 $5.75 $391,000 71,400 $5.75 $410,550 74,970 $5.75 $431,078 89,964 304,334 $5.75 $5.75 $517,293 $1,749,921 Total Sales Revenue $880,000 $924,000 $970,200 $1,164,240 $3,938,440 161,118 $6.00 $966,708 Table 2 - Schedule of Cash Collections Quarter Collections from last year Q1 Q2 Q3 Q4 Total Cash Collections Q1 $586,463 $325,600 $0 $0 $0 $912,063 Q2 $225,563 $356,400 $341,880 $0 $0 $923,843 Total Q3 Q4 $0 $0 $812,025 $198,000 $0 $880,000 $374,220 $207,900 $924,000 $358,974 $392,931 $751,905 $0 $430,769 $430,769 $931,194 $1,031,600 $3,798,699 2016 Q1 Q2 43,758 $6.00 $262,549 45,946 $6.00 $275,677 63,206 $5.25 $331,833 66,367 $5.25 $348,425 82,654 $5.75 $475,263 86,787 $5.75 $499,026 $1,069,646 $1,123,128 Table 3 - Production Budget Quarter Q1 Q2 Q3 Q4 Total 2015 Muffins Budgeted Sales Desired Ending Inventory Total Units Needed Less: Beginning Inventory Required Muffin Production 36,000 840 36,840 (800) 36,040 37,800 882 38,682 (840) 37,842 39,690 1,058 40,748 (882) 39,866 47,628 972 48,600 (1,058) 47,542 161,118 972 162,090 (800) 161,290 Cookies Budgeted Sales Desired Ending Inventory Total Units Needed Less: Beginning Inventory Required Cookie Production 52,000 1,213 53,213 (1,156) 52,058 54,600 1,274 55,874 (1,213) 54,661 57,330 1,529 58,859 (1,274) 57,585 68,796 1,405 70,201 (1,529) 68,672 232,726 1,405 234,131 (1,156) 232,975 Bread Budgeted Sales Desired Ending Inventory Total Units Needed Less: Beginning Inventory Required Bread Production 68,000 1,587 69,587 (1,511) 68,076 71,400 1,666 73,066 (1,587) 71,479 74,970 1,999 76,969 (1,666) 75,303 89,964 1,837 91,801 (1,999) 89,802 304,334 1,837 306,171 (1,511) 304,660 2016 Q1 43,758 1,021 44,779 (972) 43,807 45,946 63,206 1,475 64,681 (1,405) 63,277 66,367 82,654 1,929 84,583 (1,837) 82,746 86,787 Table 4 - Raw Materials Budget Q1 Required Production Muffins Cookies Bread Raw Materials Needed Muffins Flour Margarine Sugar Eggs Milk Mini Chips Baking Packet Cookies Flour Margarine Sugar Eggs Cocoa PB chips Baking Packet Bread Flour Sugar Milk Shortening Baking Packet Totals Flour Margarine Sugar Eggs Milk Mini Chips Baking Packet Cocoa PB chips Shortening Desired Ending Inventory Flour Margarine Sugar Quarter Q2 Q3 Q4 Total 2015 36,040 52,058 68,076 37,842 54,661 71,479 39,866 57,585 75,303 47,542 68,672 89,802 161,290 232,975 304,660 18,020 9,010 18,020 72,080 3,604 36,040 36,040 18,921 9,461 18,921 75,684 3,784 37,842 37,842 19,933 9,967 19,933 79,733 3,987 39,866 39,866 23,771 11,886 23,771 95,084 4,754 47,542 47,542 80,645 40,323 80,645 322,581 16,129 161,290 161,290 26,029 39,043 52,058 104,116 13,014 52,058 52,058 27,330 40,996 54,661 109,321 13,665 54,661 54,661 28,792 43,189 57,585 115,170 14,396 57,585 57,585 34,336 51,504 68,672 137,344 17,168 68,672 68,672 116,488 174,731 232,975 465,950 58,244 232,975 232,975 204,227 17,019 17,019 17,019 68,076 214,438 17,870 17,870 17,870 71,479 225,910 18,826 18,826 18,826 75,303 269,405 22,450 22,450 22,450 89,802 913,979 76,165 76,165 76,165 304,660 248,276 48,053 87,097 176,196 20,623 36,040 156,173 13,014 52,058 17,019 260,689 50,456 91,452 185,005 21,654 37,842 163,982 13,665 54,661 17,870 274,635 53,155 96,344 194,902 22,812 39,866 172,754 14,396 57,585 18,826 327,512 63,389 114,893 232,428 27,205 47,542 206,015 17,168 68,672 22,450 1,111,112 215,054 389,785 788,531 92,294 161,290 698,925 58,244 232,975 76,165 39,103 7,568 13,718 41,195 7,973 14,452 49,127 9,508 17,234 45,267 8,761 15,880 45,267 8,761 15,880 Eggs Milk Mini Chips Baking Packet Cocoa PB chips Shortening 27,751 3,248 5,676 24,597 2,050 8,199 2,680 29,235 3,422 5,980 25,913 2,159 8,638 2,824 34,864 4,081 7,131 30,902 2,575 10,301 3,368 32,125 3,760 6,571 28,475 2,373 9,492 3,103 32,125 3,760 6,571 28,475 2,373 9,492 3,103 Total Needs Flour Margarine Sugar Eggs Milk Mini Chips Baking Packet Cocoa PB chips Shortening 287,379 55,622 100,814 203,946 23,871 41,716 180,771 15,064 60,257 19,699 301,885 58,429 105,903 214,241 25,076 43,822 189,895 15,825 63,298 20,694 323,762 62,664 113,578 229,767 26,893 46,998 203,657 16,971 67,886 22,193 372,779 72,151 130,773 264,553 30,965 54,113 234,490 19,541 78,163 25,553 1,156,379 223,815 405,665 820,656 96,054 167,861 727,400 60,617 242,467 79,268 Less: Beginning Inventory Flour Margarine Sugar Eggs Milk Mini Chips Baking Packet Cocoa PB chips Shortening (37,241) (7,208) (13,065) (26,429) (3,093) (5,406) (23,426) (1,952) (7,809) (2,553) (39,103) (7,568) (13,718) (27,751) (3,248) (5,676) (24,597) (2,050) (8,199) (2,680) (41,195) (7,973) (14,452) (29,235) (3,422) (5,980) (25,913) (2,159) (8,638) (2,824) (49,127) (9,508) (17,234) (34,864) (4,081) (7,131) (30,902) (2,575) (10,301) (3,368) (37,241) (7,208) (13,065) (26,429) (3,093) (5,406) (23,426) (1,952) (7,809) (2,553) Raw Materials to be Purchased Flour Margarine Sugar Eggs Milk Mini Chips Baking Packet Cocoa PB chips Shortening 250,138 48,414 87,750 177,517 20,778 36,310 157,345 13,112 52,448 17,147 262,781 50,861 92,185 186,490 21,828 38,146 165,298 13,775 55,099 18,013 282,567 54,690 99,126 200,531 23,471 41,018 177,744 14,812 59,248 19,369 323,652 62,642 113,539 229,688 26,884 46,982 203,588 16,966 67,863 22,186 1,119,137 216,607 392,601 794,227 92,961 162,455 703,974 58,664 234,658 76,715 Cost per unit of Raw Materials Flour Margarine Sugar $0.15 $0.25 $0.20 $0.15 $0.25 $0.20 $0.15 $0.25 $0.20 $0.15 $0.25 $0.20 $0.15 $0.25 $0.20