Please help me to balance out the balance sheet, with explanations where you got the numbers from on the excel sheet. Feel free to change anything but please be specific why you changed it.
E 94 Year 1 2,700 15.75 42,525 7,400 15.75 116,550 15,750 18,900 7,875 34,020 41,895 39,375 34,020 108,045 A B D 1 Step 1 Sales budget q1 q2 q3 2 Budgeted sales in unit 1,000 1,200 2,500 3 Selling price per unit 15.75 15.75 15.75 4 Total budget sales 15,750 18,900 39,375 5 6 Expected cash collection (80% collected in current quarter, 20% in following quarter) 7 Accounts receivable at beginning 0 8 Cash collection for q1 12,600 3,150 9 Cash collection for 92 15,120 3,780 10 Cash collection for q3 31,500 11 Cash collection for 24 12 Total cash collection 12,600 18,270 35,280 13 14 Step 2 Production budget (stock up 5% of next quarter sales) 15 Total budget sales in units 1,000 1,200 2,500 16 Planned ending units of inventory 60 125 135 17 Total production required 1,060 1,325 2,635 18 Beginning inventory of finished goods 100 60 125 19 Required units to produce 1,265 2,510 20 21 Step 3 Materials budget (250 ml per unit, $9 per ml, 2% stock up) 22 Required units to produce 960 1,265 2,510 23 Material per unit 250 250 250 24 Materials needed for production 240,000 316,250 627,500 25 Ending material for next month (2% of 6,325 12,550 13,125 26 Total materials needed 246,325 328,800 640,625 27 Less: beginning 0 6,325 12,550 28 Total materials to purchase 246,325 322,475 628,075 29 2,700 60 2,760 135 2,625 7,400 60 7,460 100 7,360 960 2,625 250 656,250 5,675 661,925 13,125 648,800 7,360 250 1,840,000 5,675 1,845,675 0 1,845,675 E 648,800 0.30 1,845,675 0.30 553,703 194,640 28,263 165,444 193,707 73,898 96,743 188,423 165,444 524,507 A B D 30 Cash payment ($0.30 per ml, 85% current month, 15% next month) 31 Total materials to be purchased 246,325 322,475 628,075 32 Price per ml 0.30 0.30 0.30 33 Total price 73,898 96,743 188,423 34 Cash payments: 35 A/P at beginning 36 Payment on first quarter purchase 62,813 11,085 37 Payment on second quarter purchase 82,231 14,511 38 Payment on third quarter purchase 160,159 39 Payment on fourth quarter purchase 40 Total expected cash payment for mate 62,813 93,316 174,671 41 42 Step 4 Direct labor budget (0.10 per hour (6mins), $11.06/hr (min. wage), min. 552 hrs) 43 Required production units 960 1,265 2,510 44 Direct labor per unit 0.10 45 Total direct labor unit 96 127 46 Direct labor hours to be paid 552 552 47 Hourly rate 11.06 11.06 11.06 48 Cash payment for direct labor 6,105 6,105 6,105 49 50 Step 5 Manufacturing overhead budget ($75,000 fixed, $10 per direct labor hour) 51 Total direct labor needed 96 127 251 52 Overhead rate 10 10 10 53 Total variable overhead 960 1,265 2,510 54 Fixed overhead 75,000 75,000 75,000 55 Total overhead 75,960 76,265 77,510 56 Less: Non cash 0 0 0 57 Cash payment for overhead 75,960 76,265 77,510 58 0.10 0.10 251 2,625 0.10 263 552 11.06 6,105 7,360 0.10 736 2,208 11.06 24,420 552 736 263 10 2,625 75,000 77,625 0 77,625 10 7,360 75,000 307,360 0 307,360 E 2,700 0.8 2,160 65,000 67,160 1,500 65,660 7,400 0.8 5,920 260,000 265,920 6,000 259,920 193,707 41,895 235,602 524,507 108,045 632,552 B D 59 Step 6 Selling and Administrative budget ($0.80 per unit, $65,000 fixed, $1500 non cash) 60 Budgeted sales in units 1,000 1,200 2,500 61 Variable selling and admin per unit 0.8 0.8 0.8 62 Total variable selling and admin 800 960 2,000 63 Fixed selling and admin 65,000 65,000 65,000 64 Total selling and admin 65,800 65,960 67,000 65 Less: non cash 1,500 1,500 1,500 66 Cash payment for selling and admin 64,300 64,460 65,500 67 68 Step 7 Cash Budget and Cash flow 69 Beginning Balance 62,813 93,316 174,671 70 Add: collection 12,600 18,270 35,280 71 Total Cash Available 75,413 111,586 209,951 72 Deduct: 73 Materials 62,813 93,316 174,671 74 Labor 6,105 6,105 6,105 75 Overhead 75,960 76,265 77,510 76 Selling and admin 64,300 64,460 65,500 77 Dividend 78 Purchase equipment 79 Total payments 209,178 240,146 323,786 80 Excess (Deficiency) of cash (133,765) (128,560) (113,835) 81 Financing 82 Capital contribution 350,000 83 Beginning Balance 216,235 87,675 84 Interest payment 85 Total Financing 350,000 216,235 87,675 86 Ending cash balance 216,235 87,675 (26,160) 87 193,707 6,105 77,625 65,660 524,507 24,420 307,360 259,920 343,097 (107,495) 1,116,207 (483,655) 350,000 277,749 (26,160) (26,160) (133,655) 627,749 144,094 B D E 12,600 (62,813) (6,105) (75,960) (64,300) (196,578) 18,270 (93,316) (6,105) (76,265) (64,460) (221,876) 35,280 (174,671) (6,105) (77,510) (65,500) (288,506) 41,895 (193,707) (6,105) (77,625) (65,660) (301,202) 108,045 (524,507) (24,420) (307,360) (259,920) (1,008,162) 0 0 0 0 0 88 Cash flow statements 89 Operating activities 90 Cash received from sales 91 Cash payment for materials 92 Cash payment for labor cost 93 Cash payments for overhead 94 Cash payments for selling and admin 95 Net cash from operating activity 96 97 Investing activities 98 Net cash from investing activities 99 100 Financing activities 101 Initial capital 102 Jasmin Lawas 103 Lubomir Kalniev 104 Net cash from financing activities 105 106 Net change in cash 107 Cash at the beginning of the period 108 Cash ending balance 109 0 0 0 0 0 175,000 175,000 350,000 (658,162) 62,813 (595,349) B D E A 110 Step 8 Budgeted Income Statement 111 Sales Revenue 112 Cost of Goods Sold 113 Gross Profit 114 Selling and Admin expense 115 Operating income 116 Interest expense 117 Net income 118 7,400 4,331 3,069 265,920 (262,851) 0 (262,851)
