Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

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

image text in transcribed
image text in transcribed
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%

Step by Step Solution

There are 3 Steps involved in it

Step: 1

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image

Step: 3

blur-text-image

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

Accounting Custom Edition For National American University

Authors: Charles T. Horngren, Walter T. Harrison Jr, M. Suzanne Oliver

9th Edition

1256297585, 978-1256297581

More Books

Students also viewed these Accounting questions

Question

Timeline for final evaluation

Answered: 1 week ago

Question

How will it be used?

Answered: 1 week ago