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. Created by and for the Charles W. Lamden School of Accountancy, Fowler College of Business, San Diego State University 2020 by Nancy Jones and C. Janie Chang 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 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 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. 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. Created by and for the Charles W. Lamden School of Accountancy, Fowler College of Business, San Diego State University 2020 by Nancy Jones and C. Janie Chang o 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 is a 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 copies of 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. o The simplest of liquidity ratios is current assets divided by current liabilities. Using cash, accounts receivable, and accounts payable, calculate Muesli AGs 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?