Introduction The management at Muesli AG fully understands the importance of budgeting not only to help reach company goals, but also to keep the company focused on its plans for achieving those goals. Muesli AG uses a participative budget; that is managers at all levels within the company contribute to the budget based on their areas of expertise and operational responsibilities. The company budgets for a one-year time period and budgets two quarters in advance for the new year so that it never has less than 6 months of budget to work with at any given time. The budgeting process starts when the budgeting department sends out a request for managers to complete the various budget worksheets. The worksheets are then returned to Budgeting and consolidated into the master budget which is then presented at the monthly managers meeting for questions, adjustments, and finally for approval. Once approved, the budget is distributed to the various units of the company. Once a quarter, actual operational results are compared to the budget and a variance report is prepared. Managers in each responsible area use the variances to look for inefficiencies, areas that can be improved, and operations that worked as planned. The variance report is used as feedback to inform the next round of planning and budgeting.

Problem Statement: Case Details Use the following data to create a quarterly budget for Muesli AG. (Additional instructions follow.) For purposes of this exercise, we will treat the six varieties and two sizes of muesli as if they are one product. In real life, companies like Muesli AG use complex spreadsheets and budgeting software to budget for each item that they sell.

a. The Sales department exerts pressure on the Production department to meet all sales demands if possible, so Production works to keep 20% of the next months requirements for finished goods, (boxes of muesli), on hand at the end of the month.

b. Managers like to be prepared to meet product demands without overstocking the warehouse or risking spoilage. Purchasing works to keep 10% of the next months requirements for raw materials on hand at the end of the month.

c. Recall that Muesli AG sells to grocery stores, convenience stores, and hypermarkets. Therefore, all sales are on credit. On average, Muesli AG collects 70% of credit sales in the month that they are billed and 30% the following month. Because the stores all want to buy the muesli, it is rare that a store would pay later than 30 days from the invoice date or fail to pay.

d. Accounts Payable pays for 80% of the purchases of raw materials in the month of purchase. They pay for the remaining 20% in the following month.

e. All other purchases are paid for at the time of purchase.

f. Manufacturing Overhead is 317,500 per month of which 250,000 is depreciation

g. Direct Labor is a fixed expense of 80,000 per month.

h. Fixed Selling and Administrative costs are 133,000 per month.

i. Variable Selling and Administrative costs are 0.20 per box of muesli sold.

j. Each box of muesli in this example requires 500 g of raw materials. The raw materials cost 10.90 per kilogram.

k. Muesli AG keeps a minimum of 500,000 in its bank account to comply with covenants of its line of credit with the bank.

Instructions: In most organizations, the first step in creating a master budget is to create a Sales forecast because sales drives many other parts of the budget; for example, sales drives production and production drives raw materials purchasing. The sales forecast has already been developed for you by the Sales team. It is shown below and in the Excel budgeting worksheet.

Sales Forecast for Muesli AG

Quantities Revenues

April 360,000 3,060,000

May 288,000 2,448,000

June 267,000 2,269,500

Use the sales forecast and formulas and links to cells in the worksheet provided in order to create the remaining budget schedules. We recommend that you show subtractions as negative numbers in the worksheets. The following questions are intended to guide you in the budgeting process. (Please show Formulas)

1. Complete the sales forecast by calculating the quarterly totals of projected unit sales and revenues.

2. Complete the monthly production plan and the total for the quarter. o You will need to calculate the beginning inventory in April by using what you know about stocking requirements. Hint: you might refer to case detail a.

3. Complete the Raw Materials purchase requirements and the totals for the quarter. o You will need to calculate the beginning inventory in April by using what you know about stocking requirements. Hint: you might refer to case detail b. o Dont forget to convert to the correct unit of measure. Hint: The conversion rate is shown in cased detail j.

4. Now calculate the other purchases for the three months and for the quarter and complete the Other Purchases schedule. Details about these expenses are contained in the case details f, g, h, and i.

5. Calculate the balances in Accounts Receivable for each month and at the end of the quarter. The beginning balance is given to you, but you will need to link to values you have already calculated and adjust for the customers payment habits as outlined in the case detail c.

6. Calculate the balances in Accounts Payable for each month at the end of the quarter. . The beginning balance is given to you, but you will need to link to values you have already calculated and adjust for Muesli AGs payment policies as outlined in case details d and e.

7. You now have enough information to complete the Projected Cash Flows schedule and to determine the ending cash balances.

Insert formulas and cell references into the "gold" cells below. (put assumptions from case here) March April May June July Quarter Total (April, May, June) Sales Projections: [1] Quantities Revenues 320,000 boxes of muesli 360,000 288,000 267,000 268,200 3,000,000 2,448,000 2,269,500 915,000 7,777,500.00 Production Plan: [2] Required for Sales + Desired Ending Finished Goods Inventory - Beginning Finished Goods Inventory Required Production 20% 57,600 53,400 53,640 P boxes of muesli Raw Materials Purchases: [3] Required Production Kilograms of Raw Materials for Production +Desired Ending Raw Materials Inventory -Beginning Raw Materials Inventory Required Purchases of Raw Materials (Kg) Cost of Raw Materials Purchases 13,962 kg Other Purchases: [4] Fixed Manufacturing Overhead Less Depreciation (not a cash expense) Manufacturing Overhead Cash Purchases Fixed Direct Labor Fixed Selling & Admin Variable Selling & Admin Total Other Purchases 816,000 Balance in Accounts Receivable: [5] Beginning Balance + Sales for the Month Collections from prior Month - Collections from current Month Ending Balance 0 816,000 Balance in Accounts Receivable: [5] Beginning Balance + Sales for the Month Collections from prior Month - Collections from current Month Ending Balance 0 196,200 Balance in Accounts Payable: [6] Beginning Balance + Purchases of Raw Materials - Payments for Last Month's Payables - Payments for This Month's Payables Ending Balance Cash Projections: [7] Cash Inflows Cash Outflows: Payments for Raw Materials Purchases Payments for Other Purchases Total Cash Outflows Net Cash for the Month 620,000 Beginning Cash Balance Ending Cash Balance Minimum Required Cash Balance Over/Under 500,000 500,000 500,000 Liquidity Ratios: [8] Insert formulas and cell references into the "gold" cells below. (put assumptions from case here) March April May June July Quarter Total (April, May, June) Sales Projections: [1] Quantities Revenues 320,000 boxes of muesli 360,000 288,000 267,000 268,200 3,000,000 2,448,000 2,269,500 915,000 7,777,500.00 Production Plan: [2] Required for Sales + Desired Ending Finished Goods Inventory - Beginning Finished Goods Inventory Required Production 20% 57,600 53,400 53,640 P boxes of muesli Raw Materials Purchases: [3] Required Production Kilograms of Raw Materials for Production +Desired Ending Raw Materials Inventory -Beginning Raw Materials Inventory Required Purchases of Raw Materials (Kg) Cost of Raw Materials Purchases 13,962 kg Other Purchases: [4] Fixed Manufacturing Overhead Less Depreciation (not a cash expense) Manufacturing Overhead Cash Purchases Fixed Direct Labor Fixed Selling & Admin Variable Selling & Admin Total Other Purchases 816,000 Balance in Accounts Receivable: [5] Beginning Balance + Sales for the Month Collections from prior Month - Collections from current Month Ending Balance 0 816,000 Balance in Accounts Receivable: [5] Beginning Balance + Sales for the Month Collections from prior Month - Collections from current Month Ending Balance 0 196,200 Balance in Accounts Payable: [6] Beginning Balance + Purchases of Raw Materials - Payments for Last Month's Payables - Payments for This Month's Payables Ending Balance Cash Projections: [7] Cash Inflows Cash Outflows: Payments for Raw Materials Purchases Payments for Other Purchases Total Cash Outflows Net Cash for the Month 620,000 Beginning Cash Balance Ending Cash Balance Minimum Required Cash Balance Over/Under 500,000 500,000 500,000 Liquidity Ratios: [8]