Can someone help, I can't seem to get past the Financing part, above is what I have done so far but the rest keeps giving me trouble. Thanks

The goal of this assignment is to have students use excel to prepare a master budget, proforma income statement, compute balances in select accounts and to perform sensitivity analysis Sunny Days manufactures patio furniture. April Day, the company treasure has provided the following information and is requesting you prepare a master budget for Sunny Days for the first six months of 2020. Balances in select accounts on January 1, 2020: Cash $40,000 Accounts receivable $140,000 Inventory $105,000 Accounts payable $65.000 Other information: Sales for January are budgeted to be $300,000 a. Percentage increase in sales for each month will be 5% b. Cost of goods sold 50% c. Sales are 60% cash d. Credit sales are collected: 50% in the month of sale 40% in the month following sale 10% in the second month following sale e. The net accounts receivable on January 1st is comprised of $125,000 from December 2017 sales, and $15,000 from November sales. f. Ending inventory for each month should equal 70% of the following month's budgeted cost of goods sold. g. Sixty percent of a month's inventory purchases are paid in the month of purchase. 40% percent in the first month following sale h. Depreciation is $15,000 for the first half of the year. . The following monthly expenses are paid in the month when incurred. Commission percentage on sales equal to 30% Rent of $ 5,000 per month Depreciation $ 2,500 per month Other operating expenses excluding depreciation is equal to 30% of sales j. The company building will require repairs costing $10,000. The work is expected to be paid in February. The repair will not increase the value of the building. k. The company prefers to increase minimum cash balance equal to $80,000 in January If cash balance is below this amount funds need to be borrowed to meet this requirement. All borrowing occurs at the beginning of a month. Interest on the loan is 6% per year or 5% per month Only when cash balance is above minimum balance is there to be any payment of outstanding loans with interest is to be paid first. Interest is computed on all outstanding amounts for a month and is added to financing amount when not paid in a month. The outstanding financing amount includes beginning balance for the month plus any new borrowing for the month and any unpaid interest. Required: Using excel prepare the following for the first six months of the year. (Note sensitivity analysis will need to be conducted and submission is the excel workbook prepared): 1. Schedule of expected cash collections for the first half of the year. 10% 2. Merchandise purchases budget 10% 3. Scheduled of expected cash disbursements 10%. 4. Cash budget 25% (computation of excess/deficiency in cash, loan balance, interest payment, loan borrowing /payment.) 5. Determine the balance in net accounts receivable for June 30,2020.5% 6. Determine the balance in accounts payable for June 30,2020.5% 7. Determine the balance in inventory for June 30 5% 8. Prepare income statement for the period January to June, 2020 10% Sales Increase by 5% per month Total Sales Each Month Schedule of Expected Cash Collections for the first half of the year Jan Feb March April May June Total July Aug Sept 300,000 315,000 330,750 347,288 364,652 382,884 402,029 422,130 15,000 15,750 16,538 1 7,364 18,233 19,144 20,101 21,107 315,000 330,750 347,288 364,652 382,884 402,029 2,142,603 422,130 443,237 443,237 22,1621 465,398 Cash Sales (60%) 189,000 198,450 208,373 218,791 229,731 241,217 1,285,562 253,278 265,942 279,239 Credit Sales Month of Sales (50%) Month Following Sales (40%) Second Month Following Sales (10) Total Cash Collections Jan Feb March April May June Total July Aug Sept 157,500 165,375 173,644 182,326 191,442 201,014 1,071,301 126,000 132,300 138,915 145,861 153,154 696,230 160,811 31,500 33,075 34,729 36,465 135,769 3 8,288 40,203| 157,500 291,375 337,444 354,316 372,032 390,633 1,903,300 Budgeted cost of goods sold Add desired ending inventory Total needs Less beginning inventory Required inventory purchases Merchandise Purchases Budget: Jan Feb March April May June Total 150,000 157,500 165,375 173,644 182,326 191,442 110,250 115,763 121,551 127,628 134,010 140,710 260,250 273,263 286,926 301,272 316,336 332,152 105,000 110,250 115,763 121,551 127,628 134,010 155,250 163,013 171,163 179,721 188,707 198,143| July Aug Sept 201,014 211,065 221,618 147,746 155,133 348,760 366,198 221,618 140,710 147,746 155,133 208,050 218,452 Accounts payable, beginning balance Paid in the month of Purchase In the First Month Following Sales Total cash disbursements The Schedule of Expected Cash Disbursements: Jan Feb March April May June Total July Aug Sept 65,000 65,000 93,150 97,808 102,698 107,833 113,224 118,8861 633,598 124,830 131,071 62,100 65,205 68,465 7 1,889 75,483 343,1421 79,257 83,220 87,381 158,150 159,908 167,903 176,298 185,113 194,369 1,041,740 204,087 214,291 87,381 Commissions Rent Other expenses Total disbursements Schedule of Expected Cash Disbursements-Operating Expenses Jan Feb March April May June Total July Aug Sept 90,000 94,500 99,225 104,186 109,396 114,865 612,172 120,609 126,639 132,971 5,000 5,000 5,000 5,000 5,000 5,000 30,000 5,000 5,000 5,000 90,000 94,500 99,225 104,186 109,396 114,865 612,172 120,609 126,639 132,971 185,000 194,000 203,450 213,373 223,791 234,731 1,254,344 246,217 258,278 270,942 July Aug Sept Cash Budget Jan Feb March April May June Total 40,000 157,500 291,375 337,444 354,316 372,032 390,633 197,500 158,150 185,000 167,903 203,450 176,298 213,373 185,113 194,369 223,791 234,731 Cash Balance, Beginning Add Cash Collections Total Cash Available Less Disbursement For Inventory For Operating Expenses Building Repair Total Disbursements Excess (deficiency) of cash Financing: Borrowings Repayments Interest Total financing 159,908 194,000 10,000 363,908 371,353 389,671 408,904 429,099 343,150 -145,650 Cash balance, ending The goal of this assignment is to have students use excel to prepare a master budget, proforma income statement, compute balances in select accounts and to perform sensitivity analysis Sunny Days manufactures patio furniture. April Day, the company treasure has provided the following information and is requesting you prepare a master budget for Sunny Days for the first six months of 2020. Balances in select accounts on January 1, 2020: Cash $40,000 Accounts receivable $140,000 Inventory $105,000 Accounts payable $65.000 Other information: Sales for January are budgeted to be $300,000 a. Percentage increase in sales for each month will be 5% b. Cost of goods sold 50% c. Sales are 60% cash d. Credit sales are collected: 50% in the month of sale 40% in the month following sale 10% in the second month following sale e. The net accounts receivable on January 1st is comprised of $125,000 from December 2017 sales, and $15,000 from November sales. f. Ending inventory for each month should equal 70% of the following month's budgeted cost of goods sold. g. Sixty percent of a month's inventory purchases are paid in the month of purchase. 40% percent in the first month following sale h. Depreciation is $15,000 for the first half of the year. . The following monthly expenses are paid in the month when incurred. Commission percentage on sales equal to 30% Rent of $ 5,000 per month Depreciation $ 2,500 per month Other operating expenses excluding depreciation is equal to 30% of sales j. The company building will require repairs costing $10,000. The work is expected to be paid in February. The repair will not increase the value of the building. k. The company prefers to increase minimum cash balance equal to $80,000 in January If cash balance is below this amount funds need to be borrowed to meet this requirement. All borrowing occurs at the beginning of a month. Interest on the loan is 6% per year or 5% per month Only when cash balance is above minimum balance is there to be any payment of outstanding loans with interest is to be paid first. Interest is computed on all outstanding amounts for a month and is added to financing amount when not paid in a month. The outstanding financing amount includes beginning balance for the month plus any new borrowing for the month and any unpaid interest. Required: Using excel prepare the following for the first six months of the year. (Note sensitivity analysis will need to be conducted and submission is the excel workbook prepared): 1. Schedule of expected cash collections for the first half of the year. 10% 2. Merchandise purchases budget 10% 3. Scheduled of expected cash disbursements 10%. 4. Cash budget 25% (computation of excess/deficiency in cash, loan balance, interest payment, loan borrowing /payment.) 5. Determine the balance in net accounts receivable for June 30,2020.5% 6. Determine the balance in accounts payable for June 30,2020.5% 7. Determine the balance in inventory for June 30 5% 8. Prepare income statement for the period January to June, 2020 10% Sales Increase by 5% per month Total Sales Each Month Schedule of Expected Cash Collections for the first half of the year Jan Feb March April May June Total July Aug Sept 300,000 315,000 330,750 347,288 364,652 382,884 402,029 422,130 15,000 15,750 16,538 1 7,364 18,233 19,144 20,101 21,107 315,000 330,750 347,288 364,652 382,884 402,029 2,142,603 422,130 443,237 443,237 22,1621 465,398 Cash Sales (60%) 189,000 198,450 208,373 218,791 229,731 241,217 1,285,562 253,278 265,942 279,239 Credit Sales Month of Sales (50%) Month Following Sales (40%) Second Month Following Sales (10) Total Cash Collections Jan Feb March April May June Total July Aug Sept 157,500 165,375 173,644 182,326 191,442 201,014 1,071,301 126,000 132,300 138,915 145,861 153,154 696,230 160,811 31,500 33,075 34,729 36,465 135,769 3 8,288 40,203| 157,500 291,375 337,444 354,316 372,032 390,633 1,903,300 Budgeted cost of goods sold Add desired ending inventory Total needs Less beginning inventory Required inventory purchases Merchandise Purchases Budget: Jan Feb March April May June Total 150,000 157,500 165,375 173,644 182,326 191,442 110,250 115,763 121,551 127,628 134,010 140,710 260,250 273,263 286,926 301,272 316,336 332,152 105,000 110,250 115,763 121,551 127,628 134,010 155,250 163,013 171,163 179,721 188,707 198,143| July Aug Sept 201,014 211,065 221,618 147,746 155,133 348,760 366,198 221,618 140,710 147,746 155,133 208,050 218,452 Accounts payable, beginning balance Paid in the month of Purchase In the First Month Following Sales Total cash disbursements The Schedule of Expected Cash Disbursements: Jan Feb March April May June Total July Aug Sept 65,000 65,000 93,150 97,808 102,698 107,833 113,224 118,8861 633,598 124,830 131,071 62,100 65,205 68,465 7 1,889 75,483 343,1421 79,257 83,220 87,381 158,150 159,908 167,903 176,298 185,113 194,369 1,041,740 204,087 214,291 87,381 Commissions Rent Other expenses Total disbursements Schedule of Expected Cash Disbursements-Operating Expenses Jan Feb March April May June Total July Aug Sept 90,000 94,500 99,225 104,186 109,396 114,865 612,172 120,609 126,639 132,971 5,000 5,000 5,000 5,000 5,000 5,000 30,000 5,000 5,000 5,000 90,000 94,500 99,225 104,186 109,396 114,865 612,172 120,609 126,639 132,971 185,000 194,000 203,450 213,373 223,791 234,731 1,254,344 246,217 258,278 270,942 July Aug Sept Cash Budget Jan Feb March April May June Total 40,000 157,500 291,375 337,444 354,316 372,032 390,633 197,500 158,150 185,000 167,903 203,450 176,298 213,373 185,113 194,369 223,791 234,731 Cash Balance, Beginning Add Cash Collections Total Cash Available Less Disbursement For Inventory For Operating Expenses Building Repair Total Disbursements Excess (deficiency) of cash Financing: Borrowings Repayments Interest Total financing 159,908 194,000 10,000 363,908 371,353 389,671 408,904 429,099 343,150 -145,650 Cash balance, ending