Please can you help me with question 3?

3.Prepare a cash budget for 2007. Note: You may find it useful to reference your production budget from question 1 and 2.

KATRINA'S KIDS Katrina's Kids is a non-profit formed in the aftermath of the fury that hit the New Orleans area in A ugust 2005. The mission of this organization is to make library grants to schools in any of the areas hit by Hurricane Katrina. Funds are being raised through the manufacture and sale of popular beaded bracelets to retailers throughout the U.S. Katrina'sKids hopes to spread word of the disastrous impact on education for these children by advertising their mission via store displays. In the current year, 2006, Katrina's Kids (KK) anticipates a surplus of $1,100,000 based upon sales of $6,840,000. Given a very successful first year with little effort, key members of the management team feel that they can expand the market significantly if they could use a publicity campaign that would make written materials more available throughout the nation. This would increase selling and administrative expenses by $125,000, however it is believed that this will result in a 17% increase in bracelet sales in 2007. This bump in sales could be realized at a critical time asKKplanstobegin disseminating funds sometime during the next year. Other proposed changes for 2007 include changes inthe stringing process that would result in variable manufacturing overhead of $1.95/bracelet. This major change would cost approximately $500,000 for machinery, which is expected to have a userl life of 10 years. Management believes that this machinerywculd serve to increase factory capacity from 750,000 units to 1,100,000 units. KKwouldpurchase this machinery in the rst quarter (January) of 2007 and pay for it in March. All bracelets are sold to retail outlets on credit. Collections for bracelets are typically made as follows: 50% in the month of sale and 50% in the month following the month of sale. Depreciation is computed using the straight-line method and, a hill year of depreciation will be taken in the rst year. Cash disbursements for raw materials are made in the m o nth after the purchase. Other expenses arepaid for as incurred. KKkeeps a supply of bracelets on hand at the end of every month equivalent to one month's sales. Raw materials are purchased on an as needed basis, so at the end of the month there is no balance in raw m aterials inventory. Required 1) Prepare a production budget for 2007 showing the number of units produced. Note: It will be useful to prepare the budget by month where you assume production is evenly distributed throughout the year. 2) Determine the budgeted cost of goods manufactured and cost of goods sold for 2007. Note: You may find it useil to reference your production budget from question I. 3) Prepare a cash budget for 2007. Note: You may find it useful to reference your production budgetom question I and 2. 4) Prepare a budgeted income statement for 2007. 5) Katrina's Kids is concemed that the expansion of production capacity may require external sources of nancing. What is the amount of nancing that Katrina's Kids will need to arrange and by when? What do you recommend Katrina Kids do to secure the requisite nancing? 6) Based on your budgeted income statement for 2007, do you recommend Katrina's Kids undertake the proposed changes? Why? Katrina's Kids Sales (bracelets) 570,000 Selling Price $12.00 Fixed Selling & Admin Expenses $986,000.00 (includes depreciation of $36,000) Commissions (as % of Sales $$$) 5.00% Shipping (as % of Sales $$$) 3.00% Variable Manufacturing Costs (per bracelet): Direct Materials $4.00 Direct Labor $1.00 Variable Overhead $2.00 Fixed Manufacturing Costs (total, includes $180,000.00 depreciation of $60,000) Total Production (bracelets) 600,000 December's Production (bracelets) 51,000 Inventory Summary: (bracelets) Finished Goods Inventory, Jan 1 Finished Goods Inventory, Dec 31 21,000 51,000 Budgeted cash balance, Dec 31 $50,000 Katrina's Kids Statement of Cost of Goods Manufactured & Sold Year Ended December 31, 2006 Cost of Goods Manufactured: Direct Materials $2,400,000.00 Direct Labor 600,000.00 Variable Overhead 1,200,000.00 Fixed Manufacturing Cost 180.000.00 $4,380,000.00 Add: Finished Goods Inventory Jan 1 198,000.00 Total Available for Sale $4,578,000.00 Less: Finished Goods Inventory Dec 31 (372,300.00) Cost of Goods Sold $4 205.700.00 Income Statement: Year Ended December 31, 2006 Sales $6,840,000.00 less: Cost of Goods Sold (4.205, 700.00) Gross Margin $2,634,300.00 Fixed Selling & Admin Expense (986,000 .00) Variable Selling & Admin Expense (547,200.00) Surplus $1.101,100,00The amount of financing that Katrina's Kids will need to arrange for the first quarter of 2007 = $460,384 Step-by-Step explanation Sales units in 2006 = 570,000 units Increase in 2007 in sales units = 17% Budgeted sales units in 2007 = 570,000 units * (1 + 0.17) = 666,900 units Budgeted sales units per month in 2007 = 666,900 units / 12 = 55,575 units Budgeted sales (in $) per month in 2007 = 55,575 units * $12 per unit = $666,900 Budgeted sales in first quarter of 2007 = 55,575 units * 3 = 166,725 units Budgeted Sales (in $) in first quarter of 2007 = 166,725 units *$12 per unit = $2,000,700 Production Budget for first quarter of 2007: Production Budget Calculation Jan Feb Mar Total Budgeted unit Sales 55,575 55,575 55,575 166,725 Budgeted Closing Inventory 100% of following month's budgeted sales 55,575 55,575 55,575 55,575 Total needs Sales + Closing Inventory 111,150 111,150 111,150 222,300 Less: Opening Inventory Closing inventory of 2006 51,000 55,575 55,575 51,000 Required production in units Total needs - Opening Inventory 60,150 55,575 55,575 171,300Schedule of Expected cash collections from Sales: Schedule of Expected cash collections from Sales for the first quarter of 2007 Cash Collections Total Amount Jan Feb Mar Total % Amount % Amount % Amount Beginning accounts receivable $ 285,000 100% $ 285,000 0% $ 0% $ $ 285,000 January Sales S 666,900 50% S 333,450 50% 333,450 0% S 666,900 February Sales 666,900 0% $ 50% 333,450 50% $ 333,450 S 666,900 March Sales S 666,900 0% S 0% S 50% S 333,450 S 333,450 Total cash collections $ 618,450 666,900 $ 666,900 1,952,250 Sales of Dec, 2006 = Sales of 2006 / 12 = $6,840,000 / 12 = $570,000 Beginning accounts receivable = Sales of Dec, 2006 * 50% = $570,000 * 50% = $285,000 Schedule of Expected payment for Raw materials: Schedule of Expected payment for Raw materials for the first quarter of 2007 Raw Materials Cash Payments Units consumption Jan Feb Mar Production Total ($4 per unit) % Amount % Amount % Amount December 2006 purchases of raw materials 51,000 $ 204,000 100% $ 204,000 0%| $ 0%| $ 204,000 January purchases of raw materials 60,150 240,600 $ 100% $ 240,600 S 240,600 February purchases of raw materials 55,575 222,300 0% S 100% S 222,300 222,300 Total cash collections $ 204,000 $ 240,600 222,300 666,900Cash Budget: Cash Budget for the first quarter of 2007 Beginning Cash balance S 50,000 Add: Cash receipts Cash collections from customers 1,952,250 Total Cash available $ 2,002,250 Less: Cash disbursements Direct Materials S 666,900 Direct Labor ($1 * 171,300) S 171,300 Variable Manufacturing overhead [($2 + $1.95) * 171,300 bracelets S 676,635 Fixed Manufacturing costs [($180,000 - $60,000) / 4] S 30,000 Fixed Selling and Admin Expenses [($986,000 + $125,000) / 4] S 277,750 Commission ($2,000, 700 * 5%) S 100,035 Shipping ($2,000,700 * 2%) S 40,014 Cost of New Machinery S 500,000 Total Cash disbursements 2,462,634 Excess (deficiency) of cash available over disbursements $ (460,384) Conclusion: The amount of financing that Katrina's Kids will need to arrange for the first quarter of 2007 = $460,384