Support Files From Previous Parts

profileSimmons234
cassandra_simmons_final_assignment_part_2_excel.xlsx
Home>Business & Finance homework help>Accounting homework help>Support Files From Previous Parts

Sheet1

Quarterly sales projections:
Months A-F G-L M-S T-Z Monthly Total
January 3750 2750 2000 1750 10250
February 3750 3250 3500 3750 14250
March 3750 4000 4500 3500 15750
April 3750 5000 5000 6000 19750
Sales Budget/Cash Collections Budget Proforma Variable Income Statement
Ending March 31, 2014 Ending March 31, 2014
January February March Total Sales $1,593,900
Sales: Variable costs:
Unit sales in dozens 10,250 14,250 15,750 40,250 Direct materials $1,359,600
Selling price per dozen $39.60 $39.60 $39.60 $39.60 Manufacturing overhead $100,625
Total sales $405,900 $564,300 $623,700 $1,593,900 Operating expenses $120,750
Total variable costs $1,580,975
Total cash sales (40%) $162,360 $225,720 $249,480 Contribution margin $12,925
Total credit sales (60%) $243,540 $338,580 $374,220 Fixed costs:
Manufacturing overhead $30,000
Cash collections: Operating expenses $42,000
Current month cash sales $162,360 $225,720 $249,480 637,560 Interest expense 5,266.33
Collection of credit sales $243,540 338,580 582,120 Total fixed costs 77,266
Total cash collections $162,360 $469,260 $588,060 $1,219,680 Operating income (loss) ($64,341)
Income taxes (16,085)
Quarter end receivables $374,220 Net income (loss) ($48,256)
Direct Material Purchases Budget/Cash Disbursements Budget Proforma Absorption Income Statement
Ending March 31, 2014 Ending March 31, 2014
January February March Total Sales $1,593,900
Production volume 10,250 14,250 15,750 40,250 Cost of goods sold:
Add: Planned ending inventory 1,425 1,575 1,975 1,975 Direct materials $1,359,600
Total volume 11,675 15,825 17,725 42,225 Manufacturing overhead $130,625
Less: Beginning inventory 1,025 1,425 1,575 1,025 Total cost of goods sold $1,490,225
Raw materials to be purchased 10,650 14,400 16,150 41,200 Gross margin $103,675
Cost per Dozen $33.00 $33.00 $33.00 $33.00 Operating expenses 120,750
Total cost of raw materials $351,450 $475,200 $532,950 $1,359,600 Interest expense 5,266
Operating income (loss) ($22,341)
Budgeted cash disbursements: Income taxes (5,585)
25% of current month's purchases $87,862.50 $118,800.00 $133,237.50 $339,900 Net income (loss) ($16,756)
75% of prior month's purchases $263,587.50 $356,400.00 $619,988
$87,862.50 $382,387.50 $489,637.50 $959,887.50
Ending accounts payable $399,712.50
Manufacturing Overhead Budget Proforma Balance Sheet
Ending March 31, 2014 Ending March 31, 2014
January February March Total Current assets:
Cash $ 9,209.67
Unit production in dozens 10,250 14,250 15,750 $40,250 Accounts receivable - 0
Variable manufacturing overhead per dozen $2.50 $2.50 $2.50 $2.50 Raw materials inventory 106,678.16
Budgeted manufacturing overhead $25,625 $35,625 $39,375 $100,625 Total current assets 115,887.83
Budgeted fixed overhead 10,000 10,000 10,000 $30,000
Total budget $35,625 $45,625 $49,375 $130,625 Fixed assets 90,000.00
Less accumulated depreciation - 0
Net fixed assets 90,000.00
Operating Expense Budget Total assets 205,887.83
Ending March 31, 2011
Current liabilities:
January February March Total Accounts payable - 0
Accrued interest payable 5,266.33
Unit production in dozens 10,250 14,250 15,750 40,250 Bank loan and line of credit 198,877.50
Variable operating expenses per dozen $3.00 $3.00 $3.00 $3.00 Total current liabilities 204,143.83
Budgeted variable expense $30,750 $42,750 $47,250 $120,750
Budgeted fixed operating expenses 14,000 14,000 14,000 $42,000 Owner's equity:
Total budget $44,750 $56,750 $61,250 $162,750 Capital contribution 50,000.00
Retained earnings ($48,256)
Total equity 1,744.01
Total liabilities and equity 205,887.83
Cash Budget
Ending March 31, 2014
January February March Total
Receipts:
Cash, beginning balance $ - 0 $ 10,000.00 $ 10,497.50 20,497.50
Collections from customers 162,360.00 469,260.00 588,060.00 1,219,680.00
Total cash available 162,360.00 479,260.00 598,557.50 1,240,177.50
Disbursements:
Investment 50,000.00 - 0 - 0 50,000.00
Direct materials 87,862.50 382,387.50 489,637.50 959,887.50
Manufacturing overhead 35,625.00 45,625.00 49,375.00 130,625.00
Operating expenses 44,750.00 56,750.00 61,250.00 162,750.00
Asset Acquisition 90,000.00 - 0 - 0 90,000.00
Income taxes - 0 - 0 16,085.33 16,085.33
Total disbursements 308,237.50 484,762.50 616,347.83 1,359,347.83
Excess (deficiency) of cash (145,877.50) (5,502.50) (17,790.33) (119,170.33)
Bank loan 50,000.00 - 0 - 0 50,000.00
Credit Line 105,877.50 16,000.00 27,000.00 148,877.50
Repayments - 0 . - 0 - 0
Interest payments - 0 - 0 - 0 - 0
Cash, ending balance 10,000.00 10,497.50 9,209.67 9,209.67
Interest expense (1% per month) 1,558.78 1,718.78 1,988.78 5,266.33
Accrued interest at quarter end 5,266.33

Sheet2

Sheet3