ACCT 210 Managerial Accounting Chapter 9Budget Excel Spreadsheet

Managerial AccountingChapter 9Budget Excel SpreadsheetComplete and Return Course Mail AttachmentBudgeted sales are expected to be:AprilMayJuneJulyAugust40,000 Units30,000 Units20,000 Units35,000 Units30,000 UnitsSelling Price$20 Per unitPrepare the Budgets for second quarter!!1) Sales Budget with expected Cash CollectionsAll sales are on account. The company collects 60% in month of sale and 35%in the following month. The remaining 5% is uncollectable.March 31 AR is 40,000 and all is collectableMonth of sale CollectionsFollowing month collections60%35%Sales BudgetApril40,000Budgeted sales in unitsMay30,000June20,000Quarter90,000Times selling price per unitBudgeted sells in dollarsCheck #$1,800,000Schedule of Cash CollectionsAprilARMarch 31 BalanceApril Sales$MayJuneQuarter40,000May SalesJune SalesTotal Cash CollectionsCheck #$1,610,0002) Production BudgetProduction budget does not have a total column, instead does an extra period.The company desires to have inventory on hand at the end of each month10% of the following month’s budgeted sales in units. This was meet on March 31.(Hint: April 1 beginning Inventory would be 10% of April’s budgeted sales in units)Desired ending inventorySales in unitsAdd desired ending Inv.Total needsLess Beginning Inv.Production in units10%*April40,000May30,000June20,000July35,000Aug.30,000Check #$34,5003) Materials Purchases Budget and Expected Cash Paymentstwo pounds of material are needed for each unit of product. The company desiresto have on hand at the end of each month equal to 20% of follow month’s productionneeds. This was meet at the end of March.Material cost .60 per pound. 60% is paid for in current month, other 40% is paid in thenext month. March 31 balance owed is 15,000.Desired inventoryMaterial costPaid in current monthPaid in next month20%0.60 per pound60%40%$AprilMay2 pounds per unitJuneQuarterJulyPRODUCTION in unitstimes materials per unit in lbs.Production need in lbs.Add desired ending inv. (lbs.)Total needs (lbs.)Less beg. Inv. (lbs.)Materials to be Purch. (lbs.)Times cost per poundTotal Cost of PurchasesSchedule of Cash Payments:APMarch 31April PurchasesCheck #$106,320April$MayJuneQuarter15,000May PurchasesJune PurchasesTotal Cash Payments forMaterialsCheck #$109,7524) Direct Labor BudgetEach unit requires .08 hours of direct Labor. The Company pays over time.Employees make $15 per hour for first 40 hours, time and a half for anything over 40 hours.If each employee works 40 hours, it would be 2,000 hours total.Labor hours per unitNumber of hours at 40 hours per weekHourly rate0.082000$15AprilMayJuneQuarterPRODUCTION in unitsDirect labor hours per unitLabor hours requiredHours at Regular rateTimes regular rateRegular labor costsHours at overtimeTimes overtime rateOvertime Labor costsTotal DL costs(add rows 130 and 133)(first 2,000 hours or less)(Any over 2,000 hours)Check #$118,2005) Manufacturing Overhead BudgetVariable MOH is $2 per unit produced. Fixed MOH is $30,000 per month.The Fixed MOH includes $10,000 of depreciation that is not a cash outflow.Variable MOHFixed MOHDepreciation$2 per unit$30,000 per month$10,000 per monthAprilMayJuneQuarterPRODUCTION in unitsVariable MOH per unitVariable MOH CostsPlus Fixed MOH per monthTotal MOH CostsLess non-cash costsCash paid for MOHCheck #$239,0006) Selling and Administrative BudgetVariable Selling and Administrative expenses are $.60 per unit SOLD. Fixed Selling andAdministrative expenses is $50,000 per month. The Fixed expenses include $20,000of depreciation that is a non cash outflow.Variable S & AFixed S & ADepreciation$0.60 per unit$50,000 per month$20,000 per monthAprilSALES in unitsVariable S & A per unitVariable S & A expensePlus Fixed S & A expenseTotal S & A expenseLess noncash expenseCash paid for S & AMayJuneQuarterCheck #$144,0007) Cash BudgetOther cash expenses and information for the month includeA) An open line of credit allows our company to borrow up to $75,000 per quarterB) We must have a minimum cash balance each month of $40,000C) All borrowing is done at beginning of month, repayments at end of monthD) Interest is paid at 10% per year on all amounts borrowedE) Cash dividends to be paid in April are (cell E187):F) Cash equipment purchases in May (cell E188) and in June (cell F188)G) Cash balance at beginning of April is $60,000AprilMayJune$0.1$300,160439,240 $200,000QuarterCash Balance, beginningAdd cash receipts (budget 1)Total Cash AvailableLess Disbursements:Material Purchases (Budget 3)Direct labor (Budget 4)MOH (Budget 5)Selling and Adm. (budget 6)Dividends (Letter E)Equipment purchases (letter F)Total DisbursementsExcess (deficiency) of cashFinancing (if needed)BorrowingRepaymentInterest **Total FinancingCash Balance, EndingCheck #$119,423