PLEASE HELP ME SOLVE THE ATTACHED ACCOUNTING QUESTION.

PLEASE USE EXCEL FORMULAS TO THE MAXIMUM CAPACITY.

50% OF THE GRADE IS BASED ON USE OF FORMULAS.

PLEASE SEE ATTACHED TEMPLATE WITH QUESTION.

Name: Type your name here Project 4 worth 15 pts. Save you file using your first initial, last name, and name of pr Due by August 28 11:59 pm Central time. Upload to project 4 link. This project covers material in chapter 9 and I have extensive budget demonstrations in the chapter 9 fold You need to use cell references in the development of your budgets. You must use this worksheet to reference the data that is being inputted onto the budgets on the b If you type in any numbers in the solution, I will take off 5 pts., since we use Excel so that we can u You should use this worksheet as your data field and only use cell references and formulas in your budge Your grade will be based on accuracy of your solution and correct usage of excel. The budget worksheet h For #9 you should only have to change one number on this worksheet to answer the question. The beauty behind excel is that managers can perform what-if analysis just by changing the data, so you d have used cell references and formulas throughout. Data Scenario: You have just been hired into a management position which requires the application of your budgeting ski You find out that budgeting has not been a priority of the company and that they have been experiencing c You have contacted various areas on the organization and have accumulated the information below to ass in preparing a comprehensive budget. The following is actual information that relates to the operations of a merchandiser named Sled Compan Cash $1,000 Accounts receivable 14,060 Inventory 22,960 Accounts Payable 16,682 Actual and Budgeted sales dollar Data-Sales Budget: March (actual) $38,000 April $41,000 May $42,000 June $40,000 July $43,000 Sales are the following type: 63% Cash sales collected in month of sale 37% Credit sales collected in the following month of sa Credit sales are collected in the month following sale. The accounts receivable at March 31 are a result o Cost of goods sold equals 80% of sales price At the end of each month, inventory is to be on hand(ending inventory) equal to 70% of following month's sales needs, stated at cost. Inventory purchases are paid 48% in month of purchase 52% in month after purchase The accounts payable at March 31 is a result of March purchases of inventory. I have set up a calculation Monthly selling, general administrative expenses are as follows and are paid in the month incurred if it is Salaries and wages 10% of sales dollar Rent $2,000 per month Other cash expenses 3% of sales dollar Advertising $1,000 per month Depreciation $900 for month Required: Prepare the following budgets on the budget worksheet, which contains a template of I have adapted the budget model to meet the needs of this company, and this project is showing monthly a 1. Prepare a Cash Receipts Budget for the quarter ending June 30th, by month and quarter. You do not need a formal sales budget since the sales budget is above in the data. 2. Prepare a merchandise purchase budget by month and quarter. A merchandiser purchases in dollars. I have set up a formatted budget for you. You can see how I have made a few minor changes to Schedul Make sure you think about the numbers you use in the quarter column. I am specifically talking about how 3. Prepare a Selling and Administrative Expense Budget, by month and quarter. My budget is a little bit different than the one in the text, because the expenses are more detailed in my bu 4. Prepare a cash disbursements budget, by month and quarter. See page 368. 5. Prepare a cash budget showing the months and quarter. Use the format I have provided on the budget sheet. 6. Based on the quarterly cash budget you prepared, do you have any recommendations on cash management. Discuss the type of business and the cash flow problems a company in this industr Type your answer on the budget worksheet , where I have set out the question. 7. Prepare a budgeted income statement for the quarter ending June 30, 20XX. You do not need to show monthly columns. I entered Sales for you on the incom 8. What do you think about the survivability of this business? 9. What if the company finds out the monthly rent will increase to $2,500, what budgets are effected? W What is the New Net income(Loss) for the quarter? If you have linked everything correctly, you should only have to change the monthly rent on this sheet to determine your answer to the questions asked. Please change the rent back to the origin Grading Rubric: #1-5 and #7 #6 #8 #9 I will take off partial pts. For each error within budget 2 pts. for each budget 1 pt. 1 pt. 1 pt. Project 4 Objectives: 1. Develop operating budgets 2. Analyze the cash flow of the company. 3. Analyze how changes in cost or revenue impa 4. Provide recommendations to management. st name, and name of project. ons in the chapter 9 folder. o the budgets on the budget worksheet. Excel so that we can update budgets or do what if analysis without retyping numbers. formulas in your budgets. The budget worksheet has formatted budgets for you to complete. he question. anging the data, so you do not need to retype the budgets if you on of your budgeting skills. ave been experiencing cash shortages. information below to assist you ser named Sled Company, a wholesaler of sleds as of March 31. month of sale the following month of sale March 31 are a result of March credit sales. es needs, stated at cost. Therefore, inventory on hand on March 31st is April's sales*.8*.7. have set up a calculation on the budget worksheet to show you this calculation. e month incurred if it is a cash expense. h contains a template of budgets you should use. ect is showing monthly and a quarter budget. and quarter. See page 367 er purchases in dollars. nor changes to Schedule A on page 373, which is a production budget which we are not preparing. fically talking about how you handle beginning and ending inventory, since for the quarter the beginning inventory sh See page 366*** e more detailed in my budget analysis. a company in this industry might have. es for you on the income statement udgets are effected? Why? e rent back to the original amount of 2,000 before you submit. of the company. in cost or revenue impact the budgets. ions to management. t preparing. er the beginning inventory should be the beginning inventory for the quarter. #1 Sled Company Cash collections for the quarter ending June 30, 20XX April $ Sales Revenue(Given) Cash sales collected in month sale Credit sales Collected in following month Total Cash receipts 63% 37% May June Quarter 41,000 $25,830 14,060 $39,890 #2 Sled Company Merchandise purchase budget For the quarter ended June 30, 20XX April May June March Budget Cost of goods sold Desired Ending inventory Total needs Less: Beginning Inventory Total purchase cost Quarter $30,400 22,960 53,360 21,280 $32,080 #3 Sled Company Selling, General and Administrative Expense Budget For the quarter ended June 30,20XX April May June Quarter Sales Variable selling and admin. expense % Total variable cash expenses Fixed Selling and admin. expenses: Rent Advertising Depreciation Total fixed expenses Total selling and administrative expenses #4 Sled Company Cash Disbursements Budget For the quarter ended June 30,20XX April Payments for Purchases (from Schedule 2) Cash payments for purchases paid in month Cash payments made in month after purchase Total Cash payments for merchandise purchased Other cash disbursements May June Quarter 48% 52% Total of other cash disbursements Total cash Disbursements #5 Sled Company Cash Budget For the quarter ended June 30, 20XX April May June Quarter Beginning cash Add: Cash Receipts Cash Available Less Cash Disbursements: Ending cash 6. Based on the quarterly cash budget you prepared, do you have any recommendations on cash management. Discuss the type of business and the cash flow problems a company in this industry might have. #7 Sled Company Budgeted Income Statement For the quarter ended June 30, 20XX Sales Cost of goods sold Gross margin Less selling and administrative Expenses Operating income 123,000 8. What do you think about the survivability of this business? 9. What if the company finds out the monthly rent will increase to $2,500, what budgets are effected? Why? What is the New Net income(Loss) for the quarter? New Net Income If you have linked everything correctly, you should only have to change the monthly rent on this sheet to determine your answer to the questions asked. Please change the rent back to the original amount of 2,000 before you submit. Name: Type your name here Project 4 worth 15 pts. Save you file using your first initial, last name, and name of pr Due by August 28 11:59 pm Central time. Upload to project 4 link. This project covers material in chapter 9 and I have extensive budget demonstrations in the chapter 9 fold You need to use cell references in the development of your budgets. You must use this worksheet to reference the data that is being inputted onto the budgets on the b If you type in any numbers in the solution, I will take off 5 pts., since we use Excel so that we can u You should use this worksheet as your data field and only use cell references and formulas in your budge Your grade will be based on accuracy of your solution and correct usage of excel. The budget worksheet h For #9 you should only have to change one number on this worksheet to answer the question. The beauty behind excel is that managers can perform what-if analysis just by changing the data, so you d have used cell references and formulas throughout. Data Scenario: You have just been hired into a management position which requires the application of your budgeting ski You find out that budgeting has not been a priority of the company and that they have been experiencing c You have contacted various areas on the organization and have accumulated the information below to ass in preparing a comprehensive budget. The following is actual information that relates to the operations of a merchandiser named Sled Compan Cash $1,000 Accounts receivable 14,060 Inventory 22,960 Accounts Payable 16,682 Actual and Budgeted sales dollar Data-Sales Budget: March (actual) $38,000 April $41,000 May $42,000 June $40,000 July $43,000 Sales are the following type: 63% Cash sales collected in month of sale 37% Credit sales collected in the following month of sa Credit sales are collected in the month following sale. The accounts receivable at March 31 are a result o Cost of goods sold equals 80% of sales price At the end of each month, inventory is to be on hand(ending inventory) equal to 70% of following month's sales needs, stated at cost. Inventory purchases are paid 48% in month of purchase 52% in month after purchase The accounts payable at March 31 is a result of March purchases of inventory. I have set up a calculation Monthly selling, general administrative expenses are as follows and are paid in the month incurred if it is Salaries and wages 10% of sales dollar Rent $2,000 per month Other cash expenses 3% of sales dollar Advertising $1,000 per month Depreciation $900 for month Required: Prepare the following budgets on the budget worksheet, which contains a template of I have adapted the budget model to meet the needs of this company, and this project is showing monthly a 1. Prepare a Cash Receipts Budget for the quarter ending June 30th, by month and quarter. You do not need a formal sales budget since the sales budget is above in the data. 2. Prepare a merchandise purchase budget by month and quarter. A merchandiser purchases in dollars. I have set up a formatted budget for you. You can see how I have made a few minor changes to Schedul Make sure you think about the numbers you use in the quarter column. I am specifically talking about how 3. Prepare a Selling and Administrative Expense Budget, by month and quarter. My budget is a little bit different than the one in the text, because the expenses are more detailed in my bu 4. Prepare a cash disbursements budget, by month and quarter. See page 368. 5. Prepare a cash budget showing the months and quarter. Use the format I have provided on the budget sheet. 6. Based on the quarterly cash budget you prepared, do you have any recommendations on cash management. Discuss the type of business and the cash flow problems a company in this industr Type your answer on the budget worksheet , where I have set out the question. 7. Prepare a budgeted income statement for the quarter ending June 30, 20XX. You do not need to show monthly columns. I entered Sales for you on the incom 8. What do you think about the survivability of this business? 9. What if the company finds out the monthly rent will increase to $2,500, what budgets are effected? W What is the New Net income(Loss) for the quarter? If you have linked everything correctly, you should only have to change the monthly rent on this sheet to determine your answer to the questions asked. Please change the rent back to the origin Grading Rubric: #1-5 and #7 #6 #8 #9 I will take off partial pts. For each error within budget 2 pts. for each budget 1 pt. 1 pt. 1 pt. Project 4 Objectives: 1. Develop operating budgets 2. Analyze the cash flow of the company. 3. Analyze how changes in cost or revenue impa 4. Provide recommendations to management. st name, and name of project. ons in the chapter 9 folder. o the budgets on the budget worksheet. Excel so that we can update budgets or do what if analysis without retyping numbers. formulas in your budgets. The budget worksheet has formatted budgets for you to complete. he question. anging the data, so you do not need to retype the budgets if you on of your budgeting skills. ave been experiencing cash shortages. information below to assist you ser named Sled Company, a wholesaler of sleds as of March 31. month of sale the following month of sale March 31 are a result of March credit sales. es needs, stated at cost. Therefore, inventory on hand on March 31st is April's sales*.8*.7. have set up a calculation on the budget worksheet to show you this calculation. e month incurred if it is a cash expense. h contains a template of budgets you should use. ect is showing monthly and a quarter budget. and quarter. See page 367 er purchases in dollars. nor changes to Schedule A on page 373, which is a production budget which we are not preparing. fically talking about how you handle beginning and ending inventory, since for the quarter the beginning inventory sh See page 366*** e more detailed in my budget analysis. a company in this industry might have. es for you on the income statement udgets are effected? Why? e rent back to the original amount of 2,000 before you submit. of the company. in cost or revenue impact the budgets. ions to management. t preparing. er the beginning inventory should be the beginning inventory for the quarter. #1 Sled Company Cash collections for the quarter ending June 30, 20XX April $ Sales Revenue(Given) Cash sales collected in month sale Credit sales Collected in following month Total Cash receipts 63% 37% For cash sales collected in the month of may, june and july For cash collected from credit sales May June Quarter 41,000 $ 42,000 $ 40,000 $ 123,000 $25,830 $26,460 $25,200 $77,490 14,060 15,170 15,540 44,770 $39,890 $41,630 $40,740 $122,260 Working 1 63% of current month sales revenue 37% of the sales collected in the following month of sales #2 Budget Cost of goods sold Desired Ending inventory Total needs Less: Beginning Inventory Total purchase cost For the quarter ended June 30, 20XX Budget Cost of goods sold Desired Ending inventory Beginning inventory Sled Company Merchandise purchase budget For the quarter ended June 30, 20XX March April May June $30,400 $32,800 $33,600 22,960 23,520 22400 53,360 56,320 56,000 21,280 22,960 23,520 $32,080 $33,360 $32,480 Working 2 Quarter 32,000 24080 56,080 22,400 $33,680 128,800 92,960 221,760 90,160 $131,600 80% of the current month sales Sales of subsquent month*8*7 You use the closing inventory of the previous month #3 Sales Variable selling and admin. Expense Total variable cash expenses Fixed Selling and admin. expenses: Rent Advertising Depreciation Total fixed expenses Total selling and administrative expenses Sled Company Selling, General and Administrative Expense Budget For the quarter ended June 30,20XX April May June Quarter $41,000 42,000 40,000 $123,000 4100% 4200% 4000% 12300% $4,100 $4,200 $4,000 $12,300 $2,000 1,000 900 $3,900 $8,000 $2,000 1,000 900 $3,900 $8,100 $2,000 1,000 900 $3,900 $7,900 $6,000 3,000 2,700 $11,700 $24,000 $33,680.00 16,166 $17,888 $34,054 Quarter $99,520 $47,769 $52,292 $100,061 $7,900 $41,954 $24,000 $124,061 $95,580 40,740 $136,320 $41,954 $94,366 Quarter $163,590 $122,260 $285,850 $124,061 $161,789 #4 Sled Company Cash Disbursements Budget For the quarter ended June 30,20XX Payments for Purchases (from Schedule 2) Cash payments for purchases paid in month Cash payments made in month after purchase Total Cash payments for merchandise purchased Other cash disbursements 48% 52% April May June $33,360.00 $32,480.00 16,013 15,590 $16,890 $17,514 $32,903 $33,104 Total of other cash disbursements Total cash Disbursements $8,000 $40,903 $8,100 $41,204 #5 Sled Company Cash Budget For the quarter ended June 30, 20XX April Beginning cash Add: Cash Receipts Cash Available Less Cash Disbursements: Ending cash May $14,060 39,890 $53,950 $40,903 $13,047 June $53,950 41,630 $95,580 $41,204 $54,376 6. Based on the quarterly cash budget you prepared, do you have any recommendations on cash management. Discuss the type of business and the cash flow problems a company in this industry might have. #7 Sled Company Budgeted Income Statement For the quarter ended June 30, 20XX Sales Cost of goods sold Gross margin Less selling and administrative Expenses Operating income 123,000 98,400 24,600 $24,000 $600 8. What do you think about the survivability of this business? The business has low chances of survival.This is due to low operating income. 9. What if the company finds out the monthly rent will increase to $2,500, what budgets are effected? Why? What is the New Net income(Loss) for the quarter? The budget to be affected are the selling,general and administrative and cash disbursement budgets. This is because they both involve an element of rent expense for them to be drawn up. New Net loss $24600 Less:New selling and administrative expe$25500 Hence a net loss of $900. -900