You now have the information you need to create a budget that will allow you to...
Fantastic news! We've Found the answer you've been seeking!
Question:
Transcribed Image Text:
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 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
Expert Answer:
Answer rating: 100% (QA)
The spreadsheet contains the following information Month The month for which the budget is being prepared Sales Projections The number of dozens of cookies that are projected to be sold in that month ... View the full answer
Related Book For
Accounting concepts and applications
ISBN: 978-0538745482
11th Edition
Authors: Albrecht Stice, Stice Swain
Posted Date:
Students also viewed these accounting questions
-
You are trying to create a budget to optimize the use of a portion of your disposable income. You have a maximum of $ 1,500 per month to be allocated to food, shelter, and entertainment. The amount...
-
Pedro and Lucy started to create a budget (based on last years income and expense statement) but got stuck. They know that you have learned how to create budgets and are asking for your help. They...
-
The following table presents information on how many cookies can be produced from eggs and a mixture of other ingredients (measured in ounces): Recently, you found a new way to mix ingredients with...
-
For the following exercises, use shells to find the volume generated by rotating the regions between the given curve and y = 0 around the x-axis. 130. y = 1-x,x = 0, and x = 1 131. y = x, x = 0, and...
-
Imagine a world in which the rule for the l quantum number is that values start with 1 and go up to n. The rules for the n and ml quantum numbers are unchanged from those of our world. Write the...
-
How are the weighted-average and FIFO process-costing methods applied to transferred-in costs?
-
In a survey of 8-to 18-year-old heavy media users in the United States, 47% said they get fair or poor grades (C and below). You randomly select forty-five 8- to 18-year-old heavy media users in the...
-
Blooper's analysts have come up with the following revised estimates for its magnoosium mine Conduct a sensitivity analysis using the revised data. Label your answers as follows: Range Initial...
-
Createe a qualitative and quantitative data analysis for Jeff Bezos Amazon be sure to include figures and outcomes....
-
Tea has been in the limelight recently thanks to increasing global demand, especially in emerging economies like China and India. China, India, Kenya, and Sri Lanka are some of the largest producers...
-
Question 1. Name five main questions you should ask yourself to determine whether a source that you may use in your secondary research assignment is reliable? Be specific. Explain shortly what you...
-
John takes the bus with probability 0.3 and the subway with probability 0.7. He is late 40% of the time when he takes the bus, but only 20% of the time when he takes the subway. What is the...
-
What was unusual about U.S. fiscal policy from 1980 to 1995?
-
How is a projects net present value (NPV) calculated?
-
What is Tobins q, and what does it have to do with investment?
-
Explain why an increase in the interest rate reduces the amount of residential investment.
-
Respond to Acquiring the organization via equity rather than debt would have helped in the early stages of the development in an effort to reduce the worry of repayment of debt. Though there is a...
-
You are the newly appointed tax practitioner to complete Emilys tax return and have downloaded the prefill report for Emilys tax return (hint, you can read what a prefill report is here (Links to an...
-
What type of account is Discount on Bonds?
-
Sam Love is vice president and chief lending officer of the Meeker First National Bank. Recently, Bill McCarthy, a new farmer, moved to town. Sam has not dealt with Bill previously and knows little...
-
The total cost of materials, labor, and overhead assigned to a job was $603. Busch Company transferred this job to its finished goods warehouse. Make the necessary journal entry to record this...
-
What are the percentages of observations in each of the five job satisfaction categories?
-
If the 146 customer visits is used to estimate the number of appliance department visits at the larger store population, what are the lower and upper limits of this estimate?
-
How many individuals are in each of the five different credit card categories?
Study smarter with the SolutionInn App