The "Overhead and operation expense" in the excel spreadsheet need to be completed using formulas. The information needed can be found in the input sheet. Thank you very much.

Kiley Robbins, Fabi Ramos, Allison Leung Name of Cookie Recipe Chocolate Chip Cookies Forcasted Sales (dozens of cookies) Jan Dozens of cookies 8572 Quarter Total Sales (doz) Optimistic/Pessimistic percent change Feb 1995 Mar Apr 4195 1336 14762 20% Raw Materials Multiple Dozen Recipe Number of dozen that recipe is for--> Direct Raw Materials: Sugars/Flour: Granulated Sugar Brown Sugar Cocoa Flour Total sugar/flour cups per doz Qty RM One Dozen RM Cost Total Materials Cost/doz 2 0.17 $0.15 0.50 $0.15 0.00 1.25 $0.15 1.92 cups $0.02 $0.08 $0.00 $0.19 $1.00 1 0.50 $2.00 0.00 0.50 cups Eggs Total eggs per dozen 2 2 1.00 $0.10 1.00 eggs $0.10 Chips/Nuts: White Chips Total Chipsuts cups per dozen Total Materials Cost 2 2 1.00 $1.25 1.00 cups $1.25 Butter/Shortening/Oil: Butter Shortening Total butter/shortening cups per dozen Manufacturing Overhead: Indirect raw materials Utilities Maintenance Depreciation Supervision Subtotal Total Fixed MOH Operating Expenses: Sales Commission Shipping Cost Salaries Depreciation 0.33 1 2.5 3.83 1 $2.64 VC/doz $0.75 $0.50 FC/Month Jan Feb $ $ $ $ 500 2,000 2,500 5,000 $ $ $ $ 500 2,000 2,500 5,000 $ $ Jan 5,000 $ 200 $ Feb 5,000 200 $0.50 $1.00 Other Subtotal Fixed Oper Expenses 2a. Total VC/doz and Total Fixed Cost $5.39 Sell Price Markup % on VC Markup $/doz 120.00% $6.46 2b. Sell price VC per dozen 2c. CM per dozen 2d. Break-even point (in dozens) for the quarter $11.85 $5.39 $6.46 6,682 Desired Ending Inventory (ex. 20%=.20) 0.10 Cash Collections Current Month Subsequent Month 0.4 0.6 Cash Disbursements Current Month Subsequent Month 0.25 0.75 Borrowing: Interest Rate per month (ex. 2%= .02) 0.01 Income Tax rate (15% = .15) 0.25 Capital contributions Jan Feb 500000 Jan Equipment Purchases Mar 0 Feb 90000 0 Mar 0 0 $ 1,800 $ 1,800 $ 7,000 $ 7,000 $12,000.00 $12,000.00 FA16 $ $ $ $ Mar 500 2,000 2,500 5,000 $ $ Mar Qtr Total 5,000 $ 15,000 200 $ 600 $ $ $ $ Qtr Total 1,500 6,000 7,500 15,000 $ 1,800 $ 5,400 $ 7,000 $ 21,000 $12,000.00 $36,000.00 Realistic Dozens of cookies>>>>>> Sales VC CM $/unit $11.85 $5.39 $6.46 % 14762 % 17714 $174,930 $79,567 $95,363 100% FC NOI MOS sales dollars MOS% Optimistic 100% 55% $36,000 19% $59,363 36% 95,730 54.7% % 11810 209,916 95,481 114,435 45% Pessimistic 100% 55% 139,944 63,654 76,290 36,000 16% 36,000 24% 78,435 47% 40,290 25% 130,716 62.3% 45% 60,744 43.4% 45% 55% Sales and Cash Collections January Expected sales in dozens of cookies Selling Price per dozen Sales in Dollars Cash Collections: Cash collected month of sale Cash collected month after sale Total Cash Collections Accounts Receivable @ 3/31 February March Quarter 14762 $11.85 $174,958 >>>feeds income statement 8572 $11.85 $101,595 1995 $11.85 $23,645 4195 $11.85 $49,719 40,638 $0 40,638 9,458 $60,957 70,415 19,888 $14,187 34,074 69,983 $75,144 145,127 >>>feeds cash budget $29,831 >>>feeds A/R on Bal sheet Raw Materials Purchases and Cash Disbursements Sugar/Flour Dozens of cookies to be produced Total cups per dozen Total production needs (cups) Plus: Desired Ending Inventory (cups) Less: Beginning Inventory (cups) Purchases Required (cups Cost per cup Total cost of raw materials purchased Butter, Shortening, Oil Dozens of cookies to be produced Total cups per dozen Total production needs (cups) Plus: Desired Ending Inventory (cups) Less: Beginning Inventory (cups) Purchases Required (cups Cost per cup Total cost of raw materials purchased Eggs Dozens of cookies to be produced Total eggs per dozen Total production needs (eggs) Plus: Desired Ending Inventory (eggs) Less: Beginning Inventory (eggs) Purchases Required (eggs) Cost per egg Total cost of raw materials purchased Chips/Nuts Dozens of cookies to be produced Total cups per dozen Total production needs (cups) Plus: Desired Ending Inventory (cups) Less: Beginning Inventory (cups) Purchases Required (cups Cost per cup Total cost of raw materials purchased Total Raw Material Cost Schedule of Expected Cash Disbursements Cash paid in month of purchase Cash paid in subsequent month Total Cash Disbursements January February March Quarter 8,572 1,995 4,195 14,762 1.92 1.92 1.92 1.92 16,415 3,820 8,033 28,269 382 803 256 256 0 382 803 0 16,797 4,242 7,486 28,525 $0.15 $0.15 $0.15 $0.15 $2,520 $636 $1,123 $4,279 January February 8,572 1,995 0.50 0.50 4,286 998 100 210 0 100 4,386 1,108 $2.00 $2.00 $8,772 $2,215 March 4,195 0.50 2,098 67 210 1,955 $2.00 $3,909 Quarter 14,762 0.50 7,381 67 0 7,448 $2.00 $14,896 January February March Quarter 8,572 1,995 4,195 14,762 1.00 1.00 1.00 1.00 8,572 1,995 4,195 14,762 200 420 134 134 0 200 420 0 8,771 2,414 4,329 14,896 $0.10 $0.10 $0.10 $0.10 $877 $241 $433 $1,490 January February 8,572 1,995 1.00 1.00 8,572 1,995 200 420 0 200 8,772 2,215 $1.25 $1.25 $10,964 $2,769 $23,133 January $5,783 $0 $5,783 $5,861 February $1,465 $17,349 $18,815 March 4,195 1.00 4,195 134 420 3,909 $1.25 $4,886 Quarter 14,762 1.00 14,762 134 0 14,896 $1.25 $18,620 $10,351 $39,284 March $2,588 $4,396 $6,984 Quarter $9,821 $7,763 $17,584 >>>feeds cash budget Total Raw Materials Ending Inventory (March 31) Accounts Payable @ 3/31 $21,699.55 >>>feeds A/P on balance Ending Inventory Cost April 1,336 1.92 2,558 $38.38 1,336 0.50 668 $133.60 1,336 1.00 1,336 $13.36 1,336 1.00 1,336 $167.50 >>>feeds cash budget $352.84 >>>feeds raw materials on balance sheet >>>feeds A/P on balance sheet Cost of Goods Sold Manufacturing Cost per unit approach One Dozen Direct Raw Materials: DM cost VMOH-indirect matls VMOH-utilities FMOH monthly quarterly Quarterly production FMOH per unit Total mfg cost per unit Number of units mfd and sold Total Cost of goods sold Total Materials Cost/Doz Manufacturing Overhead Budget Dozens of cookies to be produced Variable manufacturing overhead per dozen Budgeted variable overhead. Budgeted fixed overhead Total budgeted manufacturing overhead Less: Noncash expenses - Depreciation Cash disbursements for manufacturing overhead January February March Quarter $8,572 $1,995 $4,195 $14,762 $10,715.00 $2,493.75 $5,243.75 $18,452.50 >>>feeds cash budget Operating Expense Budget Dozens of cookies to be produced Variable operating expenses per dozen Budgeted variable expenses Budgeted fixed expenses Total budgeted operating expenses Less: Noncash expenses - Depreciation Cash disbursements for operating expenses January February $8,572 $1,995 $4,286.00 March $4,195 Quarter $14,762 >>>feeds income statement operating expenses >>>feeds cash budget erating expenses Cash Budget January February March Quarter Beginning Cash Balance Plus: Capital Contributions Plus: Cash Collections Total Cash Available Less: Cash Disbursements Raw Materials Manufacturing Overhead Operating Expenses Income Taxes Equipment Purchases Total Disbursements Excess (Deficiency) of Cash Financing: Borrowing Principle Repayments Interest Payments Total Financing Ending Cash Balance Loan balance beginning of period + Borrowing - Principle repayments =Loan balance end of period Interest Rate/month Interest payments Note: input borrowing and repayments necessar maintain a minimum cash balance of $10,00 >>>feeds balance sheet >>>feeds balance sheet >>feeds line 22 above, cash budget, and income stateme NOTE: 1. Assume borrowing takes place at the beginning of each month in increments of $1000. 2. Assume the minimum required cash balance is $10,000. 3. Assume repayments are made in increments of $1000 at the beginning of each month as well. Repayments are made when there is enough cash to maintain at least $10,000. 4. Assume the interest rate is 1% on the outstanding loan balance at the end of the month. 5. Interest is incurred and paid monthly at the end of each month on total outstanding borrowings at the end of the current mon g and repayments necessary to um cash balance of $10,000 at month-end. budget, and income statement t the end of the current month. Income Satement % Sales Less: Cost of Sales Gross Margin Less: Operating Expenses Net Operating Income (NOI) Less: Interest Expense Income before taxes Less: Income Taxes Net Income Balance Sheet Assets Current Assets: Cash Accounts Receivable Raw Materials Inventory Total Current Assets Fixed Assets (less accumulated depreciation) Total Assets Liabilities and Equity Liabilities: Accounts Payable Notes Payable Total Liabilities Equity: Capital Contributions Retained Earnings Total Equity Total Liabilities and Equity SP15