80.2K

Verified Solution

Link Copied!

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,000288,000267,000$ Revenues $3,060,0002,448,0002,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.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.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

Answer & Explanation Solved by verified expert

See Answer