Case Study

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 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 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% 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 Overheadis 317,500 per month of which 250,000is depreciation.

g).

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

h).

Fixed Selling and 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 drive 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 Salesteam. It is shown below and in the Excel budgeting worksheet.

Sales Forecast for Muesli 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.

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

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.

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.

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.

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.

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.

Younow 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 flows are tight. There isa limit as to how much money can be borrowed and, 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 a covenant 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?

Please use Excel Sheet to solve the questionnaire and show the work.

Questions to the answer are provided in the highlighted box in the Excel sheet without showing formula of calculation. Show formulas as an answer instead of numbers. please, insert formulas each box of highlighted. (+_*/%). Please explain calculation and show formula of the answers and Using chart and graph accordingly based on the original questionnaire without an answer. (How it become to be the numbers) of an answers) just Insert formula in the yellow box

Require a including charts same as picture below that are incomplete: 1. Projected Inventory Levels. 2. Projected Changes in Receivables and Payables. 3. Projected Changes in Reveivables and Payables. ( Please show formulas for the charts, and detail as much as possible). Thank you!

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