Need help with the a budgeting formulation assignment shown on the attached excel spread sheet

Name: Type your name here This project covers material in chapter 9 and I have extensive budget demonstrations in the chapter 9 fold I have a template set up on the solution worksheet that you should use to complete the required budgets t 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,500 Accounts receivable 13,300 Inventory 22,386 Accounts Payable 16,577 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: 65% Cash sales collected in month of sale 35% 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 78% 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 47% in month of purchase 53% 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 12% of sales dollar Rent $2,000 per month Other cash expenses 2% of sales dollar Advertising $1,500 per month Depreciation $800 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,200, 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. ons in the chapter 9 folder. e the required budgets that are stated on the solution worksheet tab. 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. 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 65% 35% May June Quarter 41,000 $26,650 13,300 $39,950 #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 $29,640 22,386 52,026 20,748 $31,278 #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 Total variable cash expenses Rent Advertising Total of other cash disbursements Total cash Disbursements May June Quarter 47% 53% #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,200, 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 This project covers material in chapter 9 and I have extensive budget demonstrations in the chapter 9 folder. I have a template set up on the solution worksheet that you should use to complete the required budgets that are stated on the solution worksheet tab. 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 budget worksheet. If you type in any numbers in the solution, I will take off 5 pts., since we use Excel so that we can update budgets or do what if analysis without re You should use this worksheet as your data field and only use cell references and formulas in your budgets. Your grade will be based on accuracy of your solution and correct usage of excel. The budget worksheet has formatted budgets for you to complete. 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 do not need to retype the budgets if you 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 skills. You find out that budgeting has not been a priority of the company and that they have been experiencing cash shortages. You have contacted various areas on the organization and have accumulated the information below to assist you in preparing a comprehensive budget. The following is actual information that relates to the operations of a merchandiser named Sled Company, a wholesaler of sleds as of March 31. Cash $1,500 Accounts receivable 13,300 Inventory 22,386 Accounts Payable 16,577 Actual and Budgeted sales dollar Data-Sales Budget: March (actual) April May June July $38,000 $41,000 $42,000 $40,000 $43,000 Sales are the following type: 65% Cash sales collected in month of sale 35% Credit sales collected in the following month of sale Credit sales are collected in the month following sale. The accounts receivable at March 31 are a result of March credit sales. Cost of goods sold equals 78% 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. Therefore, inventory on Inventory purchases are paid 47% in month of purchase 53% in month after purchase The accounts payable at March 31 is a result of March purchases of inventory. I have set up a calculation on the budget worksheet to show you this calcula Monthly selling, general administrative expenses are as follows and are paid in the month incurred if it is a cash expense. Salaries and wages 12% of sales dollar Rent $2,000 per month Other cash expenses 2% of sales dollar Advertising $1,500 per month Depreciation $800 for month Required: Prepare the following budgets on the budget worksheet, which contains a template of budgets you should use. I have adapted the budget model to meet the needs of this company, and this project is showing monthly and a quarter budget. 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 Schedule A on page 373, which is a production budget whi Make sure you think about the numbers you use in the quarter column. I am specifically talking about how you handle beginning and ending inventory, sinc 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 budget analysis. 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 industry might have. 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 income statement 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,200, what budgets are effected? Why? 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 original amount of 2,000 before you submit. 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 impact the budgets. 4. Provide recommendations to management. n the solution worksheet tab. or do what if analysis without retyping numbers. udgets for you to complete. etype the budgets if you er of sleds as of March 31. g month of sale ed at cost. Therefore, inventory on hand on March 31st is April's sales*.8*.7. worksheet to show you this calculation. ets you should use. See page 367 3, which is a production budget which we are not preparing. eginning and ending inventory, since for the quarter the beginning inventory should be the beginning inventory for the quarter. See page 366*** you on the income statement 00 before you submit. venue impact the budgets. #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 65% 35% May 41,000 $ $26,650 13,300 $39,950 June 42,000 $ $27,300 14,350 $41,650 40,000 $26,000 14,700 $40,700 Quarter $123,000 $79,950 $42,350 $122,300 #2 March Budget Cost of goods sold Desired Ending inventory Total needs Less: Beginning Inventory Total purchase cost April $29,640 22,386 52,026 20,748 $31,278 Sled Company Merchandise purchase budget For the quarter ended June 30, 20XX May June Quarter $31,980 $32,760 31,200 125,580 0 0 0 22,386 31,980 32,760 31,200 147,966 22,386 22,932 $21,840 87,906 $9,594 $9,828 $9,360 $60,060 #3 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 $4,920 5,040 4,800 $14,760 $820 840 800 $2,460 $5,740 $5,880 $5,600 $17,220 14% 14% 14% 14% $5,740 $5,880 $5,600 $17,220 Sales salaries & wages Other cash expenses total 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 $2,000 1,500 0 $3,500 $9,240 $2,000 1,500 0 $3,500 $9,380 $2,000 1,500 800 $4,300 $9,900 $6,000 $4,500 $800 $11,300 $28,520 #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 Total variable cash expenses Rent Advertising Total of other cash disbursements Total cash Disbursements 47% 53% $22,386 $10,521 11,865 $22,386 May $22,932 $10,778 12,154 $22,932 June $21,840 $10,265 11,575 $21,840 Quarter $67,158 $31,564 $35,594 $67,158 $5,740 $2,000 $1,500 $9,240 $31,626 $5,880 $2,000 $1,500 $9,380 $32,312 $5,600 $2,000 $1,500 $9,100 $30,940 $17,220 $6,000 $4,500 $27,720 $94,878 $26,000 40,700 $66,700 $30,940 $35,760 Quarter $79,950 122,300 $202,250 $94,878 $107,372 #5 April Beginning cash Add: Cash Receipts Cash Available Less Cash Disbursements: Ending cash Sled Company Cash Budget For the quarter ended June 30, 20XX May June $26,650 $27,300 39,950 41,650 $66,600 $68,950 $31,626 $32,312 $34,974 $36,638 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. This is a sole propretorship form of business. As a small capital base to operate efficiently in the market. The main cash flow problem is that the business may have problems in acquiring cash for expansion since in operates at a loss, there is no assurance that the return will be enough to repay the loan. As a result the business may not obtained a loan from a financial institution. Expansion will be limited since tha cash base ina less as seen from the above cash budget. #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 95,940 27,060 $28,520 ($1,460) 8. What do you think about the survivability of this business? The business future is not guaranteed as it operates at a loss as seen fromn the above income statement operating income figure of ($.1,060) 9. What if the company finds out the monthly rent will increase to $2,200, what budgets are effected? Why? What is the New Net income(Loss) for the quarter? The selling, general and administrative expense increases as it increases the total fixed expense. The cash disbursement budget is also affected since increase in rent increases the total cash disbursement under other cash disbursements. The new net income declines further. The loss increases as fromm the budgeted iuncome statement. New Net Income ($2,060) Name: Type your name here This project covers material in chapter 9 and I have extensive budget demonstrations in the chapter 9 folder. I have a template set up on the solution worksheet that you should use to complete the required budgets that are stated on the solution worksheet tab. 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 budget worksheet. If you type in any numbers in the solution, I will take off 5 pts., since we use Excel so that we can update budgets or do what if analysis without re You should use this worksheet as your data field and only use cell references and formulas in your budgets. Your grade will be based on accuracy of your solution and correct usage of excel. The budget worksheet has formatted budgets for you to complete. 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 do not need to retype the budgets if you 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 skills. You find out that budgeting has not been a priority of the company and that they have been experiencing cash shortages. You have contacted various areas on the organization and have accumulated the information below to assist you in preparing a comprehensive budget. The following is actual information that relates to the operations of a merchandiser named Sled Company, a wholesaler of sleds as of March 31. Cash $1,500 Accounts receivable 13,300 Inventory 22,386 Accounts Payable 16,577 Actual and Budgeted sales dollar Data-Sales Budget: March (actual) April May June July $38,000 $41,000 $42,000 $40,000 $43,000 Sales are the following type: 65% Cash sales collected in month of sale 35% Credit sales collected in the following month of sale Credit sales are collected in the month following sale. The accounts receivable at March 31 are a result of March credit sales. Cost of goods sold equals 78% 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. Therefore, inventory on Inventory purchases are paid 47% in month of purchase 53% in month after purchase The accounts payable at March 31 is a result of March purchases of inventory. I have set up a calculation on the budget worksheet to show you this calcula Monthly selling, general administrative expenses are as follows and are paid in the month incurred if it is a cash expense. Salaries and wages 12% of sales dollar Rent $2,000 per month Other cash expenses 2% of sales dollar Advertising $1,500 per month Depreciation $800 for month Required: Prepare the following budgets on the budget worksheet, which contains a template of budgets you should use. I have adapted the budget model to meet the needs of this company, and this project is showing monthly and a quarter budget. 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 Schedule A on page 373, which is a production budget whi Make sure you think about the numbers you use in the quarter column. I am specifically talking about how you handle beginning and ending inventory, sinc 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 budget analysis. 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 industry might have. 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 income statement 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,200, what budgets are effected? Why? 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 original amount of 2,000 before you submit. 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 impact the budgets. 4. Provide recommendations to management. n the solution worksheet tab. or do what if analysis without retyping numbers. udgets for you to complete. etype the budgets if you er of sleds as of March 31. g month of sale ed at cost. Therefore, inventory on hand on March 31st is April's sales*.8*.7. worksheet to show you this calculation. ets you should use. See page 367 3, which is a production budget which we are not preparing. eginning and ending inventory, since for the quarter the beginning inventory should be the beginning inventory for the quarter. See page 366*** you on the income statement 00 before you submit. venue impact the budgets. #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 65% 35% May 41,000 $ $26,650 13,300 $39,950 June 42,000 $ $27,300 14,350 $41,650 40,000 $26,000 14,700 $40,700 Quarter $123,000 $79,950 $42,350 $122,300 #2 March Budget Cost of goods sold Desired Ending inventory Total needs Less: Beginning Inventory Total purchase cost April $29,640 22,386 52,026 20,748 $31,278 Sled Company Merchandise purchase budget For the quarter ended June 30, 20XX May June Quarter $31,980 $32,760 31,200 125,580 0 0 0 22,386 31,980 32,760 31,200 147,966 22,386 22,932 $21,840 87,906 $9,594 $9,828 $9,360 $60,060 #3 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 $4,920 5,040 4,800 $14,760 $820 840 800 $2,460 $5,740 $5,880 $5,600 $17,220 14% 14% 14% 14% $5,740 $5,880 $5,600 $17,220 Sales salaries & wages Other cash expenses total 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 $2,000 1,500 0 $3,500 $9,240 $2,000 1,500 0 $3,500 $9,380 $2,000 1,500 800 $4,300 $9,900 $6,000 $4,500 $800 $11,300 $28,520 #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 Total variable cash expenses Rent Advertising Total of other cash disbursements Total cash Disbursements 47% 53% $22,386 $10,521 11,865 $22,386 May $22,932 $10,778 12,154 $22,932 June $21,840 $10,265 11,575 $21,840 Quarter $67,158 $31,564 $35,594 $67,158 $5,740 $2,000 $1,500 $9,240 $31,626 $5,880 $2,000 $1,500 $9,380 $32,312 $5,600 $2,000 $1,500 $9,100 $30,940 $17,220 $6,000 $4,500 $27,720 $94,878 $26,000 40,700 $66,700 $30,940 $35,760 Quarter $79,950 122,300 $202,250 $94,878 $107,372 #5 April Beginning cash Add: Cash Receipts Cash Available Less Cash Disbursements: Ending cash Sled Company Cash Budget For the quarter ended June 30, 20XX May June $26,650 $27,300 39,950 41,650 $66,600 $68,950 $31,626 $32,312 $34,974 $36,638 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. This is a sole propretorship form of business. As a small capital base to operate efficiently in the market. The main cash flow problem is that the business may have problems in acquiring cash for expansion since in operates at a loss, there is no assurance that the return will be enough to repay the loan. As a result the business may not obtained a loan from a financial institution. Expansion will be limited since tha cash base ina less as seen from the above cash budget. #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 95,940 27,060 $28,520 ($1,460) 8. What do you think about the survivability of this business? The business future is not guaranteed as it operates at a loss as seen fromn the above income statement operating income figure of ($.1,060) 9. What if the company finds out the monthly rent will increase to $2,200, what budgets are effected? Why? What is the New Net income(Loss) for the quarter? The selling, general and administrative expense increases as it increases the total fixed expense. The cash disbursement budget is also affected since increase in rent increases the total cash disbursement under other cash disbursements. The new net income declines further. The loss increases as fromm the budgeted iuncome statement. New Net Income ($2,060)