Project ( due on Monday April 13th in the morning ) I need A+ grade please
magic_1
lannister_brewery_budgets_spring_2015.xlsxSales
| Assumptions: | |||||
| Lannister Brewery expects to sell 100,000 cases of beer during the year, with sales peaking in the third quarter. | |||||
| Lannister Brewery | |||||
| Sales Budget | |||||
| For the Year Ended December 31, 2013 | |||||
| Quarter | |||||
| 1 | 2 | 3 | 4 | Year | |
| Budgeted sales in cases | 10,000 | 30,000 | 40,000 | 20,000 | 100,000 |
| Selling price per case | $20 | $20 | $20 | $20 | $20 |
| Total sales | $200,000 | $600,000 | $800,000 | $400,000 | $2,000,000 |
Production
| Assumptions: | |||||
| Management desires an ending finished goods inventory equal to 20% of next quarter's sales. | |||||
| Sales for the first quarter of 2014 are estimated to be 15,000. | |||||
| Lannister Brewery | |||||
| Production Budget | |||||
| For the Year Ended December 31, 2013 | |||||
| (in cases) | |||||
| Quarter | |||||
| 1 | 2 | 3 | 4 | Year | |
| Budgeted sales | 10,000 | 30,000 | 40,000 | 20,000 | 100,000 |
| Add: Desired ending finished goods | 6,000 | 8,000 | 4,000 | 3,000 | 3,000 |
| Total needs (required cases) | 16,000 | 38,000 | 44,000 | 23,000 | 103,000 |
| Less: Beginning finished goods | 2,000 | 6,000 | 8,000 | 4,000 | 2,000 |
| Required production | 14,000 | 32,000 | 36,000 | 19,000 | 101,000 |
DM
| Assumptions: | |||||
| Management desires an ending inventory of raw materials equal to 10% of the next quarter's production requirements. | |||||
| Production for the first quarter of 2014 is estimated to be 225,000. | |||||
| The manufacture of each case requires 15 pounds of raw materials. | |||||
| The cost per pound of raw materials is $0.20. | |||||
| Lannister Brewery | |||||
| Direct Materials Budget | |||||
| For the Year Ended December 31, 2013 | |||||
| Quarter | |||||
| 1 | 2 | 3 | 4 | Year | |
| Required production (in cases) | 14,000 | 32,000 | 36,000 | 19,000 | 101,000 |
| Direct materials per case (pounds) | 15 | 15 | 15 | 15 | 15 |
| Total pounds needed for production | 210,000 | 480,000 | 540,000 | 285,000 | 1,515,000 |
| Add: Desired ending direct materials (pounds) | 48,000 | 54,000 | 28,500 | 22,500 | 22,500 |
| Total materials required | 258,000 | 534,000 | 568,500 | 307,500 | 1,537,500 |
| Less: Beginning direct materials (pounds) | 21,000 | 48,000 | 54,000 | 28,500 | 21,000 |
| Direct materials purchases | 237,000 | 486,000 | 514,500 | 279,000 | 1,516,500 |
| Cost per pound | $0.20 | $0.20 | $0.20 | $0.20 | $0.20 |
| Total cost of direct materials purchases | $ 47,400 | $ 97,200 | $ 102,900 | $ 55,800 | $ 303,300 |
DL
| Assumptions: | |||||
| Each case requires 0.40 direct labor-hours. | |||||
| Anticipated hourly wage rate is $15. | |||||
| Lannister Brewery | |||||
| Direct Labor Budget | |||||
| For the Year Ended December 31, 2013 | |||||
| Quarter | |||||
| 1 | 2 | 3 | 4 | Year | |
| Required production (in cases) | 14,000 | 32,000 | 36,000 | 19,000 | 101,000 |
| Direct labor-hours per case | 0.4 | 0.4 | 0.4 | 0.4 | 0.4 |
| Total required direct labor-hours | 5,600 | 12,800 | 14,400 | 7,600 | 40,400 |
| Direct labor cost per hour | $ 15.00 | $ 15.00 | $ 15.00 | $ 15.00 | $ 15.00 |
| Total direct labor cost | $ 84,000 | $ 192,000 | $ 216,000 | $ 114,000 | $ 606,000 |
MOH
| Assumptions: | |||||
| MOH is applied to production on the basis of direct labor-hours. | |||||
| Lannister Brewery expects variable costs to fluctuate with production volume on the basis of the following rates: IDM $1, IDL $2.4, utilities $0.4, and maintenance $0.2. | |||||
| Some maintenance is fixed. | |||||
| Amounts reported for fixed costs are assumed. | |||||
| Lannister Brewery | |||||
| Manufacturing Overhead Budget | |||||
| For the Year Ended December 31, 2013 | |||||
| Quarter | |||||
| 1 | 2 | 3 | 4 | Year | |
| Variable costs | |||||
| Indirect materials ($1.00/hour) | $ 5,600 | $ 12,800 | $ 14,400 | $ 7,600 | $ 40,400 |
| Indirect labor ($2.40/hour) | $ 13,440 | $ 30,720 | $ 34,560 | $ 18,240 | $ 96,960 |
| Utilities ($0.40/hour) | $ 2,240 | $ 5,120 | $ 5,760 | $ 3,040 | $ 16,160 |
| Maintenance ($0.20/hour) | $ 1,120 | $ 2,560 | $ 2,880 | $ 1,520 | $ 8,080 |
| Total variable costs | $ 22,400 | $ 51,200 | $ 57,600 | $ 30,400 | $ 161,600 |
| Fixed costs | |||||
| Supervisory salaries | $ 36,000 | $ 36,000 | $ 36,000 | $ 36,000 | $ 144,000 |
| Depreciation | $ 15,000 | $ 15,000 | $ 15,000 | $ 15,000 | $ 60,000 |
| Property taxes and insurance | $ 4,000 | $ 4,000 | $ 4,000 | $ 4,000 | $ 16,000 |
| Maintenance | $ 5,600 | $ 5,600 | $ 5,600 | $ 5,600 | $ 22,400 |
| Total fixed costs | $ 60,600 | $ 60,600 | $ 60,600 | $ 60,600 | $ 242,400 |
| Total manufacturing overhead | $ 83,000 | $ 111,800 | $ 118,200 | $ 91,000 | $ 404,000 |
| Direct labor hours | 5,600 | 12,800 | 14,400 | 7,600 | 40,400 |
| MOH rate per direct labor hour | $ 10 | ||||
Selling & Adm
| Assumptions: | |||||
| Variable expenses are $1.80 per case. | |||||
| Fixed expenses are assumed. | |||||
| Lannister Brewery | |||||
| Selling and Administrative Expense Budget | |||||
| For the Year Ended December 31, 2013 | |||||
| Quarter | |||||
| 1 | 2 | 3 | 4 | Year | |
| Budgeted sales in cases | 10,000 | 30,000 | 40,000 | 20,000 | 100,000 |
| Variable expenses | |||||
| Sales comissions ($1 per case) | $ 10,000 | $ 30,000 | $ 40,000 | $ 20,000 | $ 100,000 |
| Freight-out ($0.8 per case) | $ 8,000 | $ 24,000 | $ 32,000 | $ 16,000 | $ 80,000 |
| Total variable expenses | $ 18,000 | $ 54,000 | $ 72,000 | $ 36,000 | $ 180,000 |
| Fixed expenses | |||||
| Advertising | $ 20,000 | $ 20,000 | $ 20,000 | $ 20,000 | $ 80,000 |
| Sales salaries | $ 55,000 | $ 55,000 | $ 55,000 | $ 55,000 | $ 220,000 |
| Property taxes and insurance | $ 14,000 | $ 14,000 | $ 14,000 | $ 14,000 | $ 56,000 |
| Depreciation | $ 10,000 | $ 10,000 | $ 10,000 | $ 10,000 | $ 40,000 |
| Total fixed expenses | $ 99,000 | $ 99,000 | $ 99,000 | $ 99,000 | $ 396,000 |
| Total selling and administrative expenses | $ 117,000 | $ 153,000 | $ 171,000 | $ 135,000 | $ 576,000 |
Income Statement
| Assumptions: | |||
| Interest expense is $21,900. | |||
| Income tax rate is 30%. | |||
| Cost of One Case | |||
| Cost Element | Quantity | Unit Cost | Total |
| Direct materials | 15 | $ 0.20 | $ 3.00 |
| Direct labor | 0.4 | $ 15.00 | $ 6.00 |
| Manufacturing overhead | 0.4 | $ 10.00 | $ 4.00 |
| Total unit cost | $ 13.00 | ||
| Lannister Brewery | |||
| Budgeted Income Statement | |||
| For the Year Ended December 31, 2013 | |||
| Sales | $ 2,000,000 | ||
| Cost of goods sold | $ 1,300,000 | ||
| Gross profit | $ 700,000 | ||
| Selling and administrative expenses | $ 576,000 | ||
| Operating Income | $ 124,000 | ||
| Interest expense | $ 21,900 | ||
| Income before income taxes | $ 102,100 | ||
| Income tax expense | $ 30,630 | ||
| Net income | $ 71,470 | ||
Cash Collections
| Assumptions: | ||||||
| 70% of sales are collected in the quarter in which the sale is made and the remaining 30% are collected in the following quarter. | ||||||
| Accounts receivable of $90,000 at the end of prior year are expected to be collected in full in the first quarter of 2013. | ||||||
| Lannister Brewery | ||||||
| Schedule of Expected Collections from Customers | ||||||
| Collections by Quarter | ||||||
| Sales | 1 | 2 | 3 | 4 | Year | |
| Accounts receivable, 12/31/12 | $ 90,000 | $ 90,000 | ||||
| First quarter | $ 200,000 | $ 140,000 | $ 60,000 | $ 200,000 | ||
| Second quarter | $ 600,000 | $ 420,000 | $ 180,000 | $ 600,000 | ||
| Third quarter | $ 800,000 | $ 560,000 | $ 240,000 | $ 800,000 | ||
| Fourth quarter | $ 400,000 | $ 280,000 | $ 280,000 | |||
| Total collections | $ 230,000 | $ 480,000 | $ 740,000 | $ 520,000 | $ 1,970,000 | |
Payments for DM
| Assumptions: | ||||||
| 50% of DM are paid in the quarter purchased and 50% in the following quarter. | ||||||
| Accounts payable of $25,800 at December 31, 2012 are expected to be paid in full in the first quarter of 2013. | ||||||
| Lannister Brewery | ||||||
| Schedule of Expected Payments for Direct Materials | ||||||
| Payments by Quarter | ||||||
| Purchases | 1 | 2 | 3 | 4 | Year | |
| Accounts payable, 12/31/12 | $ 25,800 | $ 25,800 | ||||
| First quarter | $ 47,400 | $ 23,700 | $ 23,700 | $ 47,400 | ||
| Second quarter | $ 97,200 | $ 48,600 | $ 48,600 | $ 97,200 | ||
| Third quarter | $ 102,900 | $ 51,450 | $ 51,450 | $ 102,900 | ||
| Fourth quarter | $ 55,800 | $ 27,900 | $ 27,900 | |||
| Total collections | $ 49,500 | $ 72,300 | $ 100,050 | $ 79,350 | $ 301,200 | |
Cash
| Assumptions: | |||||
| Beginning cash balance is $42,500. | |||||
| Management would like to have a cash balance of at least $30,000 at the beginning of each quarter for contingencies. | |||||
| Management plans to spend $130,000 during the year on equipment purchases: $50,000 in the first quarter; $40,000 in the second quarter; $20,000 in the third quarter; and $20,000 in the fourth quarter. | |||||
| Direct labor is paid 100% in the quarter incurred. | |||||
| MOH and Selling & Administrative expenses: all items except depreciation are paid in the quarter incurred. | |||||
| Company makes equal quarterly payments of its estimated annual income taxes. | |||||
| Company has an agreement with a local bank that allows it to borrow in increments of $10,000 at the beginning of each quarter, up to $250,000 total loan balance. | |||||
| Interest rate on loans is 1% per month not compounded, for simplicity). | |||||
| Company repays loans and interest at the end of the year. | |||||
| Lannister Brewery | |||||
| Cash Budget | |||||
| For the Year Ended December 31, 2013 | |||||
| Quarter | |||||
| 1 | 2 | 3 | 4 | Year | |
| Beginning cash balance | $ 42,500 | $ 36,343 | $ 34,585 | $ 166,678 | $ 42,500 |
| Add: Receipts | |||||
| Collections from customers | $ 230,000 | $ 480,000 | $ 740,000 | $ 520,000 | $ 1,970,000 |
| Total cash available | $ 272,500 | $ 516,343 | $ 774,585 | $ 686,678 | $ 2,012,500 |
| Less: Disbursements | |||||
| Direct materials | $ 49,500 | $ 72,300 | $ 100,050 | $ 79,350 | $ 301,200 |
| Direct labor | $ 84,000 | $ 192,000 | $ 216,000 | $ 114,000 | $ 606,000 |
| Manufacturing overhead* | $ 68,000 | $ 96,800 | $ 103,200 | $ 76,000 | $ 344,000 |
| Selling and administrative** | $ 107,000 | $ 143,000 | $ 161,000 | $ 125,000 | $ 536,000 |
| Equipment purchases | $ 50,000 | $ 40,000 | $ 20,000 | $ 20,000 | $ 130,000 |
| Income tax expense | $ 7,658 | $ 7,658 | $ 7,658 | $ 7,658 | $ 30,630 |
| Total disbursements | $ 366,158 | $ 551,758 | $ 607,908 | $ 422,008 | $ 1,947,830 |
| Excess (deficiency) of cash available over disbursements | $ (93,658) | $ (35,415) | $ 166,678 | $ 264,670 | $ 64,670 |
| Financing | |||||
| Add: Borrowings (at beginning of quarter) | $ 130,000 | $ 70,000 | $ - 0 | $ - 0 | $ 200,000 |
| Less: Repayments (at end of year) | $ - 0 | $ - 0 | $ - 0 | $ (200,000) | $ (200,000) |
| Interest*** | $ - 0 | $ - 0 | $ - 0 | $ (21,900) | $ (21,900) |
| Total financing | $ 130,000 | $ 70,000 | $ - 0 | $ (221,900) | $ (21,900) |
| Ending cash balance | $ 36,343 | $ 34,585 | $ 166,678 | $ 42,770 | $ 42,770 |
| * Subtract $15,000 depreciation, non cash MOH cost | |||||
| ** Subtract $10,000 depreciation. | |||||
| ***Interest | 21900 |
Balance Sheet
| Lannister Brewery | |||
| Budgeted Balance Sheet | |||
| December 31, 2013 | |||
| Assets | |||
| Current assets: | |||
| Cash | $ 42,770 | a) | |
| Accounts receivable | $ 120,000 | b) | |
| Finished goods inventory | $ 39,000 | c) | |
| Raw materials inventory | $ 4,500 | d) | |
| Total current assests | $ 206,270 | ||
| Plant and equipment: | |||
| Buildings and equipment | $ 830,000 | e) | |
| Less: Accumulated depreciation | $ (392,000) | f) | |
| Plant and equipment, net | $ 438,000 | ||
| Total assets | $ 644,270 | ||
| Liabilities and Stockholders' Equity | |||
| Current liabilities: | |||
| Accounts payable | g) | $ 27,900 | |
| Stockholders'equity: | |||
| Common stock | $ 175,000 | h) | |
| Retained earnings | $ 441,370 | i) | |
| Total stockholders' equity | $ 616,370 | ||
| Total liabilities and stockholders' equity | $ 644,270 | ||
| Explanation: | |||
| a) From cash budget. | |||
| b) 30% of fourth quarter sales, from sales budget. | |||
| c) Desired ending inventory (from production budget) * unit cost. | |||
| d) Desired ending invenotry (from Dm budget) * cost per pound. | |||
| e) From previous year balance sheet (assume $700,000) plus $130,000 additional equipment purchased during 2013. | |||
| f) From previous year balance sheet (assume $292,000) plus $100,000 ($60,000 from MOH and $40,000 from S&A) depreciation taken during 2013. | |||
| g) 50% of raw materials purchases, from DM budget. | |||
| h) Assume $175,000. | |||
| i) Assume $441,370. |
