Case Study 7 - Chapter8

Muesli AG

Introduction

The management at Muesli AG fully understands the importance of budgeting not only to help reach company goals, but also to keep the companyfocused on its plans for achieving thosegoals. 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 budgetsfor 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 reportis used as feedback to inform the next round of planning and budgeting.

Problem Statement: Case Details

Use the following data to make a quarterly budget for MuesliAG. (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 exertspressure on the Production department to meet all sales demands if possible, so Production works to keep 20% of the next month's requirements for finished goods, (boxes of muesli), on hand at the end of the month.

b).

Managers like to be prepared to meet productdemands without overstocking the warehouse or risking spoilage. Purchasing works to keep 10% of the next month's 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, MuesliAG collects 70% ofcredit 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 Payablepays for 80% ofthe 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 Overheadis 317,500 per month of which 250,000is depreciation.

g).

Direct Labor is a fixed expenseof 80,000 per month.

h).

Fixed Sellingand Administrative costs are 133,000per month.

i).

Variable Sellingand Administrative costsare 0.20 per box of muesli sold.

j).

Each box of muesliin this example requires 500 g of raw materials. The raw materials cost

10.90per 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 make a Sales forecast because sales drives many other parts of the budget; for example, sales drives productionand production drives raw materials purchasing. The sales forecast has already been developed for you by the Salesteam. It is shown below and in the Excel budgeting worksheet.

Sales Forecast forMuesli AG
	April	May	June
Quantities	360,000	288,000	267,000
Revenues	3,060,000	2,448,000	2,269,500

Use the sales forecast and formulas and links to cells in the worksheet provided in order to make the remaining budget "schedules". We recommend that you show subtractions as negative numbersin the worksheets. The following "questions" are intended to guide you in the budgeting process.

1. Complete the sales forecastby calculating the quarterly totalsof projected unit sales and revenues.

2. Complete the monthly production plan and the total for the quarter.

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 purchaserequirements and the totals for the quarter.

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.

Don't forgetto convert to the correctunit 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 Purchasesschedule. Details about these expensesare 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 linkto 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 balanceis given to you, but you will need to link to values you have alreadycalculated and adjust for Muesli AG's payment policies as outlined in case details d and e.

7. You now have enoughinformation to completethe Projected Cash Flows scheduleand to determine the ending cash balances. Based on your cash flow projections, will Muesli AG need to borrow any additional funds during the quarter?

8. Muesli AG has some loans and a line of credit with the bank. A loan differs from a line of credit in that a loan provides the borrower with a set amount of money that has to be paid back plus interest over a set time period. The payment amounts on the loan are predetermined when the loan is initiated. A line of credit allows the borrower to borrow money temporarily when cash flowsare tight. There isa limit as to how much money can be borrowedand, in many cases, only the interest needs to be paid each month. There may also be requirements to pay down the line of credit periodically. The bank wants to know that Muesli AG can pay back its loans, so it builds"covenants" into the loan agreements. The loan covenants require the borrower to do certain things, such as providing copiesof financials and limit other borrowing in order to keep the loan. One example of acovenant is that the borrower must maintain a liquidity ratio at or above a certain amount. The simplestof liquidity ratios is currentassets divided by current liabilities. Using cash, accounts receivable, and accounts payable, calculate Muesli AG's projected liquidity ratios. If the bank requires a liquidity ratio of 4 or better and assuming that Muesli will meet its budget, will Muesli AG be in compliance with its loan covenants during each month of this quarter?

9. Based on this budget, what recommendations do you have for Muesli AG? Assuming the budget is accurate,are they doing well? Do they need to make improvements? What other plans should they make?

Questions to be answer are in the yellow box in the Excel sheet with calculation (+_*/%). Please explain calculation and show oh the content of the answer. (How it become to be the numbers) of an answers)

The simplestof liquidity ratios is currentassets divided by current liabilities. Using cash, accounts receivable, and accounts payable, calculate Muesli AG's projected liquidity ratios. If the bank requires a liquidity ratio of 4 or better and assuming that Muesli will meet its budget, will Muesli AG be in compliance with its loan covenants during each month of this quarter? 9. Based on this budget, what recommendations do you have for Muesli AG? Assuming the budget is accurate,are they doing well? Do they need to make improvements? What other plans should they make?

Questions to be answer are in the yellow box in the Excel sheet with calculation (+_*/%).

Insert formulas and cell references into the "gold" cells below. Insert formulas and cell references into the "gold" cells below