AC 320 Project The goal of this assign is the students were to prepare a master budget proforma Matement compute balances in select a n d perform sensitivity analysis Sunny Days manufactures patio furniture. All Day the y were has provided the allowing information and is requesting you prepare a 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 SiOS.000 Accounts payable 565,000 Other information Sales for January are budgeted to be $300,000 a. Percentage increase in sales for each month will be 59 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 c. The net accounts receivable on January 1" 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. e 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. i. 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 The company building will ruine i paid in February. The repair will tin 10,000. The work is expected to be see the value of the building e company prefers to increase minimum cash balance equal to 50,000 in January 1 cash balance is below this amou nds to be borrowed to meet this requirement. All borrowing cours at the beginning of month Interest on the loan is 6% per year or 5% por 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. Cashbudget 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% the above with the following changes and save as a separate spread sheet 9. Redo I to assuming 20% Growth rate for sales is equal to 5 percent Commission percentage is equal 30 percent Cost of goods 40% Other operating expenses excluding depreciation as a percentage of sales is to 35 percent Sales are 50% cash Scoring for Part B 12.5% 2 2.5% 3.2.5% 4.6.25% 5. 1.25% 6.1.25% 7.1.25% 8. 2.5% AC 320 Project The goal of this assign is the students were to prepare a master budget proforma Matement compute balances in select a n d perform sensitivity analysis Sunny Days manufactures patio furniture. All Day the y were has provided the allowing information and is requesting you prepare a 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 SiOS.000 Accounts payable 565,000 Other information Sales for January are budgeted to be $300,000 a. Percentage increase in sales for each month will be 59 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 c. The net accounts receivable on January 1" 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. e 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. i. 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 The company building will ruine i paid in February. The repair will tin 10,000. The work is expected to be see the value of the building e company prefers to increase minimum cash balance equal to 50,000 in January 1 cash balance is below this amou nds to be borrowed to meet this requirement. All borrowing cours at the beginning of month Interest on the loan is 6% per year or 5% por 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. Cashbudget 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% the above with the following changes and save as a separate spread sheet 9. Redo I to assuming 20% Growth rate for sales is equal to 5 percent Commission percentage is equal 30 percent Cost of goods 40% Other operating expenses excluding depreciation as a percentage of sales is to 35 percent Sales are 50% cash Scoring for Part B 12.5% 2 2.5% 3.2.5% 4.6.25% 5. 1.25% 6.1.25% 7.1.25% 8. 2.5%