You now have the information you need to create a budget that will allow you to show the banker your plans for the coming year. This budget also will help you to understand your sales and the collection on those sales. You will be able to determine how much money you need to purchase the ingredients for your cookies and to pay your overhead and operating expenses. You realize, "It all begins with sales. If I can estimate how many dozens of cookies I can sell, then I can calculate how many ingredients to buy and how much my overhead and operating expenses will be. Well, I had better get that sales number as accurate as possible." Requirements for Part 2a: Exhibit 4 presents information regarding your sales projections, expected collection patterns, purchasing and payment patterns for the first several months of the year. 1. Use a new tab on your revised spreadsheet from part 1 to prepare the following operating budgets for each month and the quarter in total: a. Sales budget/Cash collections Budget b. Production budget C. Direct materials budget d. Direct materials cash disbursements budget e. Direct labor cash payments budget, assuming that all labor is paid in the month incurred. f. Manufacturing overhead disbursements budget g. Operating expenses disbursements budget Note: When preparing the budgets, you should make maximum use of cell referencing and formulas in the Excel spreadsheets. You should not have to enter the same data more than one time (for example, you should enter monthly sales projections on the sales budget and then use cell referencing from the sales budget to incorporate this information in all of the other budgets). You should use formulas in Excel to perform all of your mathematical calculations. Part of your grade for this assignment will be based on your ability to use these Excel tools. 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 00 Direct Materials Per Dozen: Direct Labor Per Dozen: MOH Per Dozen: Operating Expenses: Total Costs Per Dozen: Fixed Costs Total: A. Total Variable Costs Per Dozen Cookies B. Sales Price per Dozen Cookies C. Contribution Margin per Dozen Cookies D1. Breakeven Point for the Quarter (Dollars) D2. (Units) E. Units to Sell to have an income of $10,000 per month F. Month January February March April May Quarter Total: Total: Quarter Profit: Total: G. Margin Of Safety (Quarter) Dollars Units Sales $4.53 $2.85 $0.65 $0.35 $8.38 $8,450 $8.38 $17.60 $9.22 $48,395.45 2750.054242 1,132.50 2365 5987 1050 5548 4238 9402 19188 $61,317.64 $117,060.94 6651.945758 Revenue $41,619.27 $105,359.23 $18,477.90 $97,633.70 $74,580.32 $165,456.40 $337,670.42 Profit Per Batch. Total Variable Cost Contribution Margin Fixed Costs $21,800.57 $55,188.17 $9,678.90 $51,141.46 $39,065.88 $19,818.70 $50,171.06 $8,799.00 $46,492.24 $35,514.44 $78,788.76 $9.22 $160,795.44 $86,667.64 $176,874.98 $8,450 $8,450 $8,450 $8,450 $8,450 $25,350 $42,250