Accounting Budget Project
Waqas Ahmed
budget_example_sensitivityf2015.xlsxSensitivity
| -0.1 | -0.05 | 0.05 | 0.1 | |||
| $ 11.00 | $ 10.50 | $ 10.00 | $ 9.50 | $ 9.00 | ||
| Apr | 18,000 | 19,000 | 20,000 | 21,000 | 22,000 | |
| May | 45,000 | 47,500 | 50,000 | 52,500 | 55,000 | |
| June | 27,000 | 28,500 | 30,000 | 31,500 | 33,000 | |
| July | 22,500 | 23,750 | 25,000 | 26,250 | 27,500 | |
| Aug | 13,500 | 14,250 | 15,000 | 15,750 | 16,500 |
Budget Assumptions
| Budget example from Garrison & Noreen 9th ed Transparencies for Instructors | |||||||
| Budget | Input space | Absorption Unit Product Costs | Q | Cost | Total | ||
| Sales | Price/unit | $ 10.00 | Direct Materials | 5 | $ 0.40 | $ 2.00 | |
| Direct Labor | 0.05 | $ 10.00 | $ 0.50 | ||||
| April | 20,000 | MOH/DLH | 0.05 | $ 57.04 | $ 2.85 | ||
| May | 50,000 | $ 5.35 | |||||
| June | 30,000 | ||||||
| July | 25,000 | Budgeted EI Finished Goods | Q | Cost | Total | ||
| August | 15,000 | ||||||
| EI Units X Cost per | 5,000 | $ 5.35 | $ 26,750 | ||||
| Collection Assumptions | |||||||
| Month of sale | 70.0% | S&A Expense Assumptions | |||||
| Month following | 25.0% | ||||||
| Uncollectible | 5.0% | Variable S&A per unit | $ 0.50 | ||||
| Beginning Balance | $ 30,000 | ||||||
| Fixed S&A | $ 70,000 | ||||||
| Production Assumptions | Depreciation (Non Cash) | $ 10,000 | |||||
| Ending Inventory % | 20% | ||||||
| Cash Budget Assumptions | |||||||
| B Inventory FG | 4,000 | ||||||
| Line of Credit | $ 75,000 | ||||||
| Direct Materials Assumptions | Interest Rate | 16.00% | |||||
| Material per unit/lbs | 5 | Desired cash balance | $ 30,000 | ||||
| Ending Inventory Desired | 10% | Beginning Cash Balance | $ 40,000 | ||||
| Beginning Inventory/lbs | 13,000 | ||||||
| Costs/lb | $ 0.40 | Cash outflows | |||||
| April | May | June | |||||
| Cash Disbursement Assumuption | Equipment | $ 143,700 | $ 48,300 | ||||
| Month of Purchase | 50% | Dividends | $ 49,000 | ||||
| Month Following | 50% | ||||||
| Discounts | 0% | ||||||
| AP Balance Ending | $ 12,000 | ||||||
| Direct Labor Assumptions | |||||||
| Labor per unit/hr | 0.05 | ||||||
| Hour labor cost | $ 10.00 | ||||||
| Guarenteed Labor Hours | 1,500 | ||||||
| Overhead Assumptions | |||||||
| PDOH Rate Variable/DLH | $ 20 | ||||||
| FMOH | $ 50,000 | ||||||
| Depreciation (noncash FMOH) | $ 20,000 |
Sales & Cash Collections
| Sale Budget | |||||
| Month | April | May | June | Quarter | |
| Budgeted Sales | 20,000 | 50,000 | 30,000 | 100,000 | |
| Price | x | $ 10.00 | $ 10.00 | $ 10.00 | $ 10.00 |
| Total | $ 200,000 | $ 500,000 | $ 300,000 | $ 1,000,000 | |
| Schedule of Cash Collections | |||||
| Collections | April | May | June | Quarter | |
| AR BB | $ 30,000 | $ 30,000 | |||
| Sales | - | ||||
| April | - | ||||
| 70.0% | $ 140,000 | 140,000 | |||
| 25.0% | $ 50,000 | 50,000 | |||
| May | - | ||||
| 70.0% | $ 350,000 | 350,000 | |||
| 25.0% | $ 125,000 | 125,000 | |||
| June | - | ||||
| 70.0% | $ 210,000 | 210,000 | |||
| Total CC | $ 170,000 | $ 400,000 | $ 335,000 | $ 905,000 |
Production Budget
| Production Budget | ||||||
| April | May | June | Quarter | July | ||
| Sales Budgeted | 50,000 | 30,000 | 80,000 | 25,000 | ||
| Desired Ending Inv | ||||||
| 20% | + | 10,000 | 6,000 | 5,000 | 5,000 | 3,000 |
| Total needs | 10,000 | 56,000 | 35,000 | 85,000 | 28,000 | |
| Less BI | - | 4,000 | 10,000 | 6,000 | 4,000 | 5,000 |
| Required Production | 6,000 | 46,000 | 29,000 | 81,000 | 23,000 |
Direct Materials
| Direct Materials Budget | |||||
| April | May | June | Quarter | ||
| Required Production Units | 6,000 | 46,000 | 29,000 | 81,000 | |
| Raw Material per | |||||
| Units/lbs | x | 5 | 5 | 5 | 5 |
| Production needs/lbs | 30,000 | 230,000 | 145,000 | 405,000 | |
| Desired ending Inv | |||||
| Lbs | + | 23,000 | 14,500 | 11,500 | 11,500 |
| Total needs | 53,000 | 244,500 | 156,500 | 416,500 | |
| Less BI lbs | - | 13,000 | 23,000 | 14,500 | 13,000 |
| Raw Materials to | |||||
| Purchase | 40,000 | 221,500 | 142,000 | 403,500 | |
| Cost at $0.40/lb | $ 16,000.00 | $ 88,600.00 | $ 56,800.00 | $ 161,400.00 |
Cash Disbursements Materials
| Cash Disbursements Materials | |||||
| April | May | June | Quarter | ||
| AP Beginning | $ 12,000 | $ 12,000 | |||
| April | - | ||||
| 50.0% | $ 8,000.00 | 8,000 | |||
| 50.0% | $ 8,000.00 | 8,000 | |||
| May | - | ||||
| 50.0% | $ 44,300.00 | 44,300 | |||
| 50.0% | $ 44,300.00 | 44,300 | |||
| June | - | ||||
| 50.0% | $ 28,400.00 | 28,400 | |||
| Total Cash out Materials | $ 20,000 | $ 52,300 | $ 72,700 | $ 145,000 |
Direct Labor Budget
| Direct Labor Budget | |||||
| April | May | June | Quarter | ||
| Unit to be produced | 6,000 | 46,000 | 29,000 | 81,000 | |
| Direct Labor Hours | |||||
| per unit | x | 0.05 | 0.05 | 0.05 | 0.05 |
| Total Hours needed | 300 | 2,300 | 1,450 | 4,050 | |
| Guaranteed LHs | 1,500 | 1,500 | 1,500 | 0 | |
| Labor Hours paid | 1,500 | 2,300 | 1,500 | 5,300 | |
| DL Cost per hour | x | $ 10.00 | $ 10.00 | $ 10.00 | $ 10.00 |
| Total DL Cost | $ 15,000 | $ 23,000 | $ 15,000 | $ 53,000 |
MOH
| Manufacturing Overhead | |||||
| April | May | June | Quarter | ||
| Budgeted DLH | 300 | 2,300 | 1,450 | 4,050 | |
| VMOH Rate | x | $ 20 | $ 20 | $ 20 | $ 20 |
| VMOH | $ 6,000 | $ 46,000 | $ 29,000 | $ 81,000 | |
| FMOH | + | 50,000 | 50,000 | 50,000 | 150,000 |
| Total MOH | 56,000 | 96,000 | 79,000 | 231,000 | |
| Less Depreciation | - | 20,000 | 20,000 | 20,000 | 60,000 |
| Cash Disbursements for MOH | $ 36,000 | $ 76,000 | $ 59,000 | $ 171,000 |
S & A Expense
| S & A Expense | |||||
| April | May | June | Quarter | ||
| Budgeted Sales/ Units | 20,000 | 50,000 | 30,000 | 100,000 | |
| V S&A per unit | x | $ 0.50 | $ 0.50 | $ 0.50 | $ 0.50 |
| V S&A Expense | $ 10,000 | $ 25,000 | $ 15,000 | $ 50,000 | |
| Fixed S&A Expense | + | 70,000 | 70,000 | 70,000 | 210,000 |
| Total S&A | 80,000 | 95,000 | 85,000 | 260,000 | |
| Depreciation | - | 10,000 | 10,000 | 10,000 | 30,000 |
| Cash Out S&A | $ 70,000 | $ 85,000 | $ 75,000 | $ 230,000 |
Cash Budget
| Cash Budget | |||||
| April | May | June | Quarter | ||
| Cash Balance Beginning | $ 40,000 | $ 30,000 | $ 50,000 | $ 40,000 | |
| Add | |||||
| Cash Collections | $ 170,000 | $ 400,000 | $ 335,000 | $ 905,000 | |
| Total Available Cash | $ 210,000 | $ 430,000 | $ 385,000 | $ 945,000 | |
| Less Disbursements | |||||
| DM | $ 20,000 | $ 52,300 | $ 72,700 | $ 145,000 | |
| DL | $ 15,000 | $ 23,000 | $ 15,000 | $ 53,000 | |
| MOH | $ 36,000 | $ 76,000 | $ 59,000 | $ 171,000 | |
| S&A | $ 70,000 | $ 85,000 | $ 75,000 | $ 230,000 | |
| Equipment Purchases | $ - | $ 143,700 | $ 48,300 | $ 192,000 | |
| Dividends | $ 49,000 | $ - | $ - | $ 49,000 | |
| Total Disbursements | $ 190,000 | $ 380,000 | $ 270,000 | $ 840,000 | |
| Excess(deficiency) of | |||||
| Cash available | $ 20,000 | $ 50,000 | $ 115,000 | $ 105,000 | |
| Financing | |||||
| Borrowings | $ 10,000 | $ 10,000 | |||
| Repayment | $ (10,000) | $ (10,000) | |||
| Interest | $ (400) | $ (400) | |||
| Total Financing | $ 10,000 | $ - | $ (10,400) | $ (400) | |
| Cash Balance, ending | $ 30,000 | $ 50,000 | $ 104,600 | $ 104,600 |
Income Statement
| Budgeted Income Statement | ||||||
| Royal Company | ||||||
| Budgeted Income Statement | ||||||
| For the Quarter Ending June 30 | ||||||
| Net sales | $ 1,000,000 | $ 1,000,000 | ||||
| Less CoGS | 535,000 | 499,000 | ||||
| Gross Margin | 465,000 | 501,000 | ||||
| Less S&A expense | 260,000 | 260,000 | ||||
| Net Operating Income | 205,000 | 241,000 | ||||
| Less Interest Expense | 400 | 2,000 | ||||
| Net Income | $ 204,600 | $ 239,000 | ||||
| Computation of Net Sales: | ||||||
| Sales | $ 1,000,000 | $ 1,000,000 | ||||
| Less Uncollectible amount(5%) | $ 50,000 | $ 50,000 | ||||
| Net Sales | $ 950,000 | $ 950,000 | ||||
| Computation of CoGS | ||||||
| Budgeted Sales (units) | 100,000 | 100,000 | ||||
| Unit Product Cost | $ 5.35 | $ 5.00 | ||||
| CoGS | $ 535,000 | $ 500,000 |
Beginning Balance Sheet March
| Beginning balance sheet | ||||
| Royal Company | ||||
| Balance Sheet | ||||
| March 31, 2002 | ||||
| Current Assets | ||||
| Cash | $ 40,000 | |||
| Accounts Receivable | $ 30,000 | |||
| Raw Materials Inventory | $ 5,200.00 | |||
| Finished Goods Inventory | $ 21,407.41 | $ 96,607 | ||
| Plant & Equipment | ||||
| Land | 50,000 | |||
| Buildings and Equipment | 175,000 | 225,000 | ||
| Total Assets | $ 321,607 | |||
| Liabilities: | ||||
| Accounts payable | $ 12,000 | |||
| Stockholder's equity | ||||
| Common stock | $ 200,000 | |||
| Retained earnings | 146,150 | 346,150 | ||
| Total liabilities & SE | $ 358,150 |
Budgeted Balance Sheet June
| Budgeted Balance Sheet | ||||
| Royal Company | ||||
| Budgeted Balance Sheet | ||||
| June 30, 2002 | ||||
| Current Assets | ||||
| Cash | $ 104,600 | |||
| Accounts Receivable | 75,000 | |||
| Raw Materials Inventory | 4,600 | |||
| Finished Goods Inventory | 26,750 | $ 210,950 | ||
| Plant & Equipment | ||||
| Land | 50,000 | |||
| Buildings and Equipment | 367,000 | |||
| 417,000 | ||||
| Total Assets | $ 627,950 | |||
| Liabilities: | ||||
| Accounts payable | $ 28,400 | |||
| Stockholder's equity | ||||
| Common stock | $ 200,000 | |||
| Retained earnings | 301,750 | 501,750 | ||
| Total liabilities & SE | $ 530,150 |
