I\'m struggling to finish this review assignment. I understand it\'s a lot and i
ID: 2588780 • Letter: I
Question
I'm struggling to finish this review assignment. I understand it's a lot and if it's easier to do it on an excel spreadsheet that is fine
Excel Group Project DUE: November 18, 2015 GROUP MEMBERS: PROBLEM Optima Company is a high-technology organization that produces a mass-storage system. The design of Optima's system is unique and represents a breakthrough in the industry. The units Optima produces combine positive features of both compact and hard disks. The company is completing its fifth year of operations and is preparing to build its master budget for the coming year (2014). The budget will detail each quarter's activity and the activity for the year in total. The master budget will be based on the following information a. Fourth-quarter sales for 2013 are 55,000 units. b. Unit sales by quarter (for 2014) are projected as follows 2014 Projected Sales Units First-quarter Second-quarter Third-quarter Fourth-quarter 65,000 70,000 75,000 90,000 $400 fall are credit sales Selling price per unit Collections: 85% within the sales quarter 15% in the following quarter c. FG Inventory: There is no beginning inventory of finished goods. Optima is planning the following ending finished goods inventories for each quarter FG Inventory-Planned Ending Units Balance First-quarter Second-quarter Third-quarter Fourth-quarter 15,000 20,000 d. Each mass-storage units uses: Prime Costlnput used Cost/Input Direct Labor (hours Direct material $10 $50 $240 e. DM Inventory: There are 65,700 units of direct materials in the beginning inventory as of January 1,2014. At end of the quarter, Optima plans to have 30% of the direct materials needed for the next quarter's unit sales. Optima will end the year with the same amount of direct materials found in this year's beginning Balance Jan 1, 2014 nding Bal each quarter Bal of next quarters unit sales same as this year's beginning inventoExplanation / Answer
Optima Company Quarterly cash budget for the year ended December 31, 2014 1st Qtr 2nd Qtr 3rd Qtr 4th Qtr Annual Beginning cash balance 250000 1038000 2876000 5748000 250000 Collections from customers 25400000 27700000 29700000 35100000 117900000 Total cash available for disbursements 25650000 28738000 32576000 40848000 118150000 Cash disbursements: For Direct material 16572000 18252000 18528000 18948000 72300000 For direct labor 3900000 3600000 4000000 4000000 15500000 For factory overhead 2990000 2810000 3050000 3050000 11900000 For S & A overhead 850000 900000 950000 1100000 3800000 For dividend 300000 300000 300000 300000 1200000 For equipment 2000000 2000000 Total cash disbursement 24612000 25862000 26828000 29398000 106700000 Cash surplus / (deficit) 1038000 2876000 5748000 11450000 11450000 Ending cash balance 1038000 2876000 5748000 11450000 11450000 Optima Company Income statement for the year ending December31, 2014 Sales revenue 120000000 Less: Cost of goods sold ** 99870000 Gross Profit 20130000 Less: Selling & administrative expenses 4000000 Income before taxes 16130000 Cost of goods sold: Direct material / unit of FG 240.00 Direct labor cost / unit of FG 50.00 Factory OH cost / unit of FG * 42.90 Total product cost / unit of FG 332.90 Sales units of FG 300000 Cost of goods sold ** 99870000 Optima Company Balance sheet as at December 31, 2014 Assets Cash 11450000 Accounts receivable 5400000 Direct material inventory 5256000 Finished goods inventory 3330000 Plant and equipment, net 33900000 Total Assets 59336000 Liabilities and equity Accounts payable 9348000 Capital stock 27000000 Retained earnings Beginning Balance 8058000 Net income for the year 16130000 Dividends paid -1200000 Ending balance 22988000 Total Liabilities and equity 59336000 Finished goods iventory has been rounded off by 1,000 since there is a rounding of factor in Factory OH cost per unit. Finished goods inventory Units of finished goods 10000 Product cost per unit 332.90 Value of finished goods 3329000 Plant and equipment Beginning balance 33500000 Add: Purchases 2000000 35500000 Less: Depreciation Factory OH 1400000 S & A OH 200000 1600000 Ending balance 33900000 Schedule 1 : Sales Budget 12/31/13 2014 4th Qtr 1st Qtr 2nd Qtr 3rd Qtr 4th Qtr Annual Units 55000 65000 70000 75000 90000 300000 Price 400 400 400 400 400 400 Sales 22000000 26000000 28000000 30000000 36000000 120000000 Schedule 1A : Collection Budget 12/31/13 2014 4th Qtr 1st Qtr 2nd Qtr 3rd Qtr 4th Qtr Annual Sales 22000000 26000000 28000000 30000000 36000000 120000000 Collections: Previous Qtr (15%) 3300000 3900000 4200000 4500000 15900000 Same Qtr (85%) 22100000 23800000 25500000 30600000 102000000 Total collections 25400000 27700000 29700000 35100000 117900000 Accounts receivable 3900000 4200000 4500000 5400000 Schedule 2: Production Budget 2014 1st Qtr 2nd Qtr 3rd Qtr 4th Qtr Annual Sales units 65000 70000 75000 90000 300000 Ending inventory 13000 15000 20000 10000 10000 FG - Total needs 78000 85000 95000 100000 310000 Beginning inventory 0 13000 15000 20000 0 Production 78000 72000 80000 80000 310000 Schedule 3: Direct materials purchase budget 2014 1st Qtr 2nd Qtr 3rd Qtr 4th Qtr Annual Production units 78000 72000 80000 80000 310000 Material units per each FG 3 3 3 3 3 Production - Total needs 234000 216000 240000 240000 930000 Ending Inventory 64800 72000 72000 65700 65700 Total needs 298800 288000 312000 305700 995700 Less: Beginning inventory 65700 64800 72000 72000 65700 Purchases units 233100 223200 240000 233700 930000 Cost per material unit 80 80 80 80 80 Direct material purchase cost 18648000 17856000 19200000 18696000 74400000 Schedule 3A: Cash disbursement for direct material purchases 2014 1st Qtr 2nd Qtr 3rd Qtr 4th Qtr Annual Direct material purchase cost 18648000 17856000 19200000 18696000 74400000 Same qtr payment (50%) 9324000 8928000 9600000 9348000 37200000 Prevoius qtr disbursement (50%) 7248000 9324000 8928000 9600000 35100000 Total cash disbursement for DM 16572000 18252000 18528000 18948000 72300000 Accounts payable 9324000 8928000 9600000 9348000 Schedule 4: Direct labor budget 2014 1st Qtr 2nd Qtr 3rd Qtr 4th Qtr Annual Production units 78000 72000 80000 80000 310000 Hours / FG unit 5 5 5 5 5 Hours budgeted (needed) 390000 360000 400000 400000 1550000 Cost per hour 10 10 10 10 10 Total cost 3900000 3600000 4000000 4000000 15500000 Schedule 5: Factory Overheads budget 2014 1st Qtr 2nd Qtr 3rd Qtr 4th Qtr Annual Budgeted hours 390000 360000 400000 400000 1550000 Variable overhead rate 6 6 6 6 6 Budgeted Variable OH 2340000 2160000 2400000 2400000 9300000 Budgeted Fixed OH Cash 650000 650000 650000 650000 2600000 Non cash - Depreciation 350000 350000 350000 350000 1400000 Total OH 3340000 3160000 3400000 3400000 13300000 Total factory OH cost / FG * 42.90 Cash disbursement for factory OH 2990000 2810000 3050000 3050000 11900000 Schedule 6 : Selling and administrative expenses budget 2014 1st Qtr 2nd Qtr 3rd Qtr 4th Qtr Annual Sales units 65000 70000 75000 90000 300000 Variable S&A OH rate /unit 10 10 10 10 10 Budgeted variable S&A OH 650000 700000 750000 900000 3000000 Budgeted fixed S&A OH Cash 200000 200000 200000 200000 800000 Non cash - Depreciation 50000 50000 50000 50000 200000 Total selling OH 900000 950000 1000000 1150000 4000000 Cash disbursement for S&A OH 850000 900000 950000 1100000 3800000