Please help to do part II and III in guidelines.
AC 325 AC 325 Budget Project Revised 3/1/2017 The budget project for this session is the Buns Bakery: Creating and Using a Master Budget case study from the Institute of Management Accountants (IMA). The purpose of this case is to demonstrate your ability to identify relevant information, prepare the master budget, and use budgeting information to make informed business recommendations. According to the course syllabus, this project will count as 20% of your grade. The project is to be completed as follows: 1. The project must be completed using Excel or similar electronic spreadsheet software. A template is provided for completing part one of the project. Your cell formulas must demonstrate that you are using excel for doing all calculations, and that you effectively use it to present financial information (printing alignment is clean, formatting of data is consistent, and amounts are labeled). The goal is to identify assumptions used in the budget process and to prepare the budget and then reference certain totals and line items to the flexible budget. 2. Remember that several Generally Accepted Accounting Principles (GAAP) may apply to this project. Assume this company is using US GAAP. 3. Timeliness is crucial. The complete project includes a business memorandum to the controller (in Word) supported by an excel file that includes Parts I, II, and III of the project as posted in Week 5. It should be submitted to the assignment link in Canvas at the end of Week 6, not later than Sunday at MIDNIGHT (no extensions). Part II – CHANGES IN ASSUMPTIONS Please read Part II of the case carefully. Using the master budget you created for Part I of the project, answer the following questions: 1. What will Buns’ EPS, cash flow from operations, and profit margin be for each of the options? 2. If Buns can only make one of the proposed changes, which change do you recommend the company make? Explain. 3. If Buns can make two of the proposed changes, which two do you recommend the company make? Explain. 4. Provide two additional changes Buns could make to improve its pro forma financial statements. 5. If you were the venture capitalist, would you invest in Bun’s Bakery after seeing the original numbers? What about after the changes had been made in question 2? In question 3? As you answer questions 2 and 3, please keep in mind that I will be grading based on your effort and thought rather than on your specific answers. Feel free to use other ratios and important numbers to justify your decisions. Remember that the goal is to convince me to accept your proposed decision. Part II, question 4 will be graded for creativity and effort. I am not looking for a specific answer. I suspect that many of you will come up with ideas that I have not yet thought of, so I’m excited to see what you come up with. The only thing that I will not accept is a minor revision of a change we discussed in class or that is given in the reading. Your proposal should have some specific numbers (i.e., how you expect the assumptions to go up or down). In coming up with numbers, try to make them fit in with the rest of the case. Also, keep in mind that it is almost impossible to get something for nothing. In other words, it is hard to envision a scenario that improves sales or income without costing Buns Bakery something. The four changes proposed in the case are good examples of these tradeoffs. Part III – REPORT TO MANAGEMENT Prepare a 2-page business memorandum to the Board to present your analysis. 1. Analyze, summarize and explain the key components of net income as shown in the budgeted statements addressing the assumptions used for the budget. 2. Evaluate the reasonableness of the budget. The company is very eager to secure venture capital funding, but do you believe the results of the budget would meet the venture capitalist’s requirements? Are budgeted targets achievable? 3. Identify at least two specific items the company should monitor to achieve their plan. AC325 Budget Project Page 1 Basic Information Raw DataEPS:$0.34Buns Bakery Key Assumptions:CFO:($20,220)Balance Sheet Profit Margin:1.7%As of December 31, Year 1 10% bonus:$15,748 Sales Information: Estimated % drop:20%Estimated Growth:5%Estimated Holiday Increase:15%Assets Sales:MuffinsCookiesBreadSales Numbers Trasposed for CopyingCurrent Assets Cash$40,000 Year 1Accounts Receivable$812,025 Q445,00065,00085,000MuffinsRaw Materials Inventory$21,098 20072008Finished Goods Inventory$13,831 Year 2Q1Q2Q3Q4Q1Q2Total Current Assets$886,954 Q136,00052,00068,00036,00037,80039,69047,62843,75845,946 Q237,80054,60071,400CookiesProperty, Plant, and Equipment Q339,69057,33074,97020072008Land$75,000 Q447,62868,79689,964Q1Q2Q3Q4Q1Q2Building$568,000 Year 352,00054,60057,33068,79663,20666,367Equipment$750,000 Q143,75863,20682,654BreadAccumulated Depr - Equipment($90,000) Q245,94666,36786,78720072008Total PPE$1,303,000 Q1Q2Q3Q4Q1Q2 Sales Price/Item:$6.00$5.25$5.7568,00071,40074,97089,96482,65486,787Total Assets$2,189,954 Collection Information:Liabilities and Stock Holder's Equity Credit Policy:Prior YearCurrent Year Cash Sales10%10%Collection Schedule Transposed for CopyingLiabilities Credit Sales90%90%Q1Q2Q3Accounts Payable$31,648 Collection in Current Quarter30%30%37.0%40.5%22.5%Mortgage Payable$1,109,969 Collection in Following Quarter45%45%Total Liabilities$1,141,617 Collection in 3rd Quarter25%25% Stockholder's Equity Collections from Year 1 Sales:Common Stock (no par value; 150,000 shares outstanding) Payments in Current Year$150,000 QuarterSoldReceivedQ1Q2Q3Q4Retained Earnings$898,337 Q3$802,000$621,550$180,450$0$0$0Total Stockholder's Equity$1,048,337 Q4$1,002,500$370,925$406,013$225,563$0$0 $586,463$225,563$0$0Total Liabilities and Stockholder's Equity$2,189,954 Manufacturing Information: Ending Inventory Goal:2.2% Raw Materials Goal:15.0% Raw Materials: MuffinsUnitsCost perTotal Materials:NeededUnitCost Flour0.500.15$0.08 Margarine0.250.25$0.06 Sugar0.500.20$0.10 Eggs2.000.05$0.10 Milk0.101.25$0.13 Mini Chips1.000.75$0.75 Baking Packet1.000.10$0.10 CookiesUnitsCost perTotal Materials:NeededUnitCost Flour0.500.15$0.08 Margarine0.750.25$0.19 Sugar1.000.20$0.20 Eggs2.000.05$0.10 Cocoa0.251.50$0.38 PB chips1.000.75$0.75 Baking Packet1.000.10$0.10 BreadUnitsCost perTotal Materials:NeededUnitCost Flour3.000.15$0.45 Sugar0.250.20$0.05 Milk0.251.25$0.31 Shortening0.250.50$0.13 Baking Packet1.000.10$0.10 Accounts PayablePrior YearCurrent Year Payments in Current Quarter85%85%Collection Schedule Transposed for Copying Payments in Following Quarter15%15%Q1Q2Q3 Payments in 3rd Quarter0%0%85.0%15.0%0.0% Payments on Year 1 Purchases: Payments in Current Year QuarterPurchasesPaidQ1Q2Q3Q4 Q3$0 Q4$210,984$179,336$31,648$0$0$0 $31,648$0$0$0 Direct Labor: Dozens MadeDozens MadeHoursCostDL Wage Per 15 Min.Per HourPer DozenPer DozenPer Hour Muffins Mixing8320.031$0.23$7.50 Baking4160.063$0.50$8.00 Packaging280.125$0.81$6.50 Cookies Mixing12480.021$0.16 Baking6240.042$0.33 Packaging280.125$0.81 Bread Mixing4160.063$0.47 Baking280.125$1.00 Packaging8320.031$0.20 Manufacturing OH Information: Variable OH Allocation per DL hr:$2.00 Fixed OH per Quarter:$160,000 Amount of Fixed OH from Depr:8% Selling and Administrative Expense Information: Variable S&A for Muffins:1%$0.06 Variable S&A for Cookies:1%$0.05 Variable S&A for Bread:1%$0.06 Fixed S&A Expenses per Quarter: Advertising$40,000 Cleaning Supplies$1,000 Janitorial Services$6,000 Office Staff Salaries$25,000 Office Supplies$3,000 Rent - Office$9,000 Sales Salaries$35,000 Top Management Salaries$80,000 Utilities - Office$1,800 Cash Flow and Investment Information: Mortgage Beginning Balance:$1,109,969 Interest Rate:6% Quarterly Interest Rate:1.5% Quarterly Payment:$20,000 Quarterly Interest Breakdown:Q1Q2Q3Q4 Beginning Bal:$1,109,969$1,106,619$1,103,218$1,099,766 Interest Paid:$16,649.54$16,599.28$16,548.27$16,496.49 Principal Paid:$3,350.47$3,400.72$3,451.73$3,503.51 Requirements Minimum Cash Balance:$40,000 Dividends Required Dividend Payment each Qtr:$25,000 Planned Expansion (Purchase of Equipment)Equipment Purchase Schedule Transposed for Copying Q1$75,000Q1Q2Q3Q4 Q2$100,000$75,000$100,000$50,000$35,000 Q3$50,000 Q4$35,000 Line of Credit Information: Line of Credit Max:$1,000,000 Interest Rate on LofC:8% Quarterly Interest Rate2% Common Stock information: Original Shares of Stock Outstanding:150,000 New Shares Sold:50,000 Ending Shares of Stock Outstanding200,000 Cash from Sale of Stock:$400,000 Miscellaneous Information: The company's tax rate is:30% Last year the company paid taxes of:$15,000 Amount of last year's tax to pay this year:110% Total tax to prepay this year:$16,500 Payment due each quarter:$4,125 Sales Budget Table 1 - Sales Budget QuarterTotalYear 3 Q1Q2Q3Q4Year 2Q1Q2 Muffins Budgeted Sales36,00037,80039,69047,628161,11843,75845,946 Selling price per dozen$6.00$6.00$6.00$6.00$6.00$6.00$6.00 Total Sales Revenue from Muffins$216,000$226,800$238,140$285,768$966,708$262,549$275,677 Cookies Budgeted Sales52,00054,60057,33068,796232,72663,20666,367 Selling price per dozen$5.25$5.25$5.25$5.25$5.25$5.25$5.25 Total Sales Revenue for Cookies$273,000$286,650$300,983$361,179$1,221,812$331,833$348,425 Bread Budgeted Sales68,00071,40074,97089,964304,33482,65486,787 Selling price per loaf$5.75$5.75$5.75$5.75$5.75$5.75$5.75 Total Sales Revenue for Bread$391,000$410,550$431,078$517,293$1,749,921$475,263$499,026 Total Sales Revenue$880,000$924,000$970,200$1,164,240$3,938,440$1,069,646$1,123,128 Table 2 - Schedule of Cash Collections QuarterYear Q1Q2Q3Q42 Collections from 2006$586,463$225,563$0$0$812,025 Q1$325,600$356,400$198,000$0$880,000 Q2$0$341,880$374,220$207,900$924,000 Q3$0$0$358,974$392,931$751,905 Q4$0$0$0$430,769$430,769 Total Cash Collections$912,063$923,843$931,194$1,031,600$3,798,699 Production Budget Table 3 - Production Budget QuarterTotalYear 3 Q1Q2Q3Q4Year 2Q1 Muffins Budgeted Sales36,00037,80039,69047,628161,11843,75845,946 Desired Ending Inventory8408821,0589729721,021 Total Units Needed36,84038,68240,74848,600162,09044,779 Less: Beginning Inventory(800)(840)(882)(1,058)(800)(972) Required Muffin Production36,04037,84239,86647,542161,29043,807 Cookies Budgeted Sales52,00054,60057,33068,796232,72663,20666,367 Desired Ending Inventory1,2131,2741,5291,4051,4051,475 Total Units Needed53,21355,87458,85970,201234,13164,681 Less: Beginning Inventory(1,156)(1,213)(1,274)(1,529)(1,156)(1,405) Required Cookie Production52,05854,66157,58568,672232,97563,277 Bread Budgeted Sales68,00071,40074,97089,964304,33482,65486,787 Desired Ending Inventory1,5871,6661,9991,8371,8371,929 Total Units Needed69,58773,06676,96991,801306,17184,583 Less: Beginning Inventory(1,511)(1,587)(1,666)(1,999)(1,511)(1,837) Required Bread Production68,07671,47975,30389,802304,66082,746 DM Budget Table 4 - Raw Materials Budget QuarterYearYear 3 Q1Q2Q3Q42Q1 Required Production Muffins36,04037,84239,86647,542161,29043,807 Cookies52,05854,66157,58568,672232,97563,277 Bread68,07671,47975,30389,802304,66082,746 Raw Materials Needed Muffins Flour18,02018,92119,93323,77180,64521,903 Margarine9,0109,4619,96711,88640,32310,952 Sugar18,02018,92119,93323,77180,64521,903 Eggs72,08075,68479,73395,084322,58187,614 Milk3,6043,7843,9874,75416,1294,381 Mini Chips36,04037,84239,86647,542161,29043,807 Baking Packet36,04037,84239,86647,542161,29043,807 Cookies Flour26,02927,33028,79234,336116,48831,638 Margarine39,04340,99643,18951,504174,73147,457 Sugar52,05854,66157,58568,672232,97563,277 Eggs104,116109,321115,170137,344465,950126,553 Cocoa13,01413,66514,39617,16858,24415,819 PB chips52,05854,66157,58568,672232,97563,277 Baking Packet52,05854,66157,58568,672232,97563,277 Bread Flour204,227214,438225,910269,405913,979248,239 Sugar17,01917,87018,82622,45076,16520,687 Milk17,01917,87018,82622,45076,16520,687 Shortening17,01917,87018,82622,45076,16520,687 Baking Packet68,07671,47975,30389,802304,66082,746 Totals Flour248,276260,689274,635327,5121,111,112301,780 Margarine48,05350,45653,15563,389215,05458,409 Sugar87,09791,45296,344114,893389,785105,867 Eggs176,196185,005194,902232,428788,531214,167 Milk20,62321,65422,81227,20592,29425,067 Mini Chips36,04037,84239,86647,542161,29043,807 Baking Packet156,173163,982172,754206,015698,925189,830 Cocoa13,01413,66514,39617,16858,24415,819 PB chips52,05854,66157,58568,672232,97563,277 Shortening17,01917,87018,82622,45076,16520,687 Desired Ending Inventory Flour39,10341,19549,12745,26745,267 Margarine7,5687,9739,5088,7618,761 Sugar13,71814,45217,23415,88015,880 Eggs27