The Case Study: Buns Bakery. Refer to attached file: Buns Bakery Case Study.
Required: Part 1.
Read Part I of the case carefully and create a spreadsheet page for data input as you go. This is where you will find all the numbers necessary to create your budgets as you move through the case. It is important that you think through what numbers you will need and lay those out logically with clear labels. This serves two purposes:first, it will make it easier to create the budgets, and second, when you turn this budget over to management, they may wish to change some of the assumptions and see what results flow through the budget. To this end, you must show all assumptions on the first worksheet.
You will need to create the following items:
- Data Input Sheet
- Cash Flows Budget
- Production Budget
- Projected Balance Sheet
- Projected Profit and Loss Statement
- Sales Budget
Youmayalsoneedtocreatesupportingschedulesandtables. Pleasegroupthese logically on worksheets within your document and label the tabs so that the owners and managers can find whatever budget or schedule they need. Think about the logical order of budgets. Your data input sheet should be the first worksheet. What should be the second worksheet? Make the spreadsheet document flow logically.
Have the spreadsheet show most dollar amounts as whole dollars (in some cases, such as the price of an ingredient at $0.15, it won’t make sense to do so –use your judgment). Similarly, use your judgment on how many decimals to display and what format to use ($ or % for example) in each cell. Remember, that the calculations will still be accurate as Excel uses the decimal part of the number even if it isn’t showing. Also, a good use of bold, italics, colour, and borders will make the spreadsheet more visually appealing and, if used correctly, easier to understand and use.
As a check for you, the Profit after Tax for the budget year should equal $67,524, the closing balance of Accounts Receivable should be $951,766, the closing balance of Raw Materials Inventory should be $38,465, and the cash on hand at the end of the budget year should be $46.074.
Read Part II of the case carefully. Using the Master Budget you created for Part I of the project, answer the following questions:
- What will Buns’ Earnings Per Share, cash flow from operations, and profit margin be for each of the options?
- Increase the sales commissions to 2%;
- Move to a JIT inventory system, keeping only 3% of the needed raw materialson hand;
- Improve the collections rate to 80% in the first quarter, 15 % in the secondquarter, and 5% in the third quarter;
- Revise the price increase to reduce the impact on sales volume.
- If Buns can only make one of the proposed changes, which change do you recommend the company make? Explain? Provide as much supporting argument and analysis as appropriate in support of your recommendation.
- If Buns can make two of the proposed changes, which two do you recommend the company make? Explain. Provide as much supporting argument and analysis as appropriate in support of your recommendation.
- Provide two additional changes Buns could make to improve its pro forma financial statements. This is an opportunity for you to think creatively and to show your understanding of the relationship between various parts of the operation.
- Would you invest in Bun’s Bakery after seeing theoriginal numbers? What about after the changes had been made in question 2? In question 3? Provide an explanation in support of your position.
- Task Length: 2,000 words (excluding tables, spreadsheets and references).