Budget Scenario Please use attached "project"

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 on cash management. You need to use cell references in the development of your budgets on the BudgetSolution work If you type in any numbers in the solution, I will take off 1 pt. for each typed number, since we us You should use this worksheet as your data field and only use cell references and formulas in your bud Your grade will be based on accuracy of your solution and correct usage of excel. The budget workshee The beauty behind excel is that managers can perform what-if analysis just by changing the data, so yo have used cell references and formulas throughout. BudgetSolution worksheet using information on this worksheet to reference. number, since we use Excel so that we can update budgets or do what if analysis without retyping numbe formulas in your budgets. The budget worksheet has formatted budgets for you to complete. anging the data, so you do not need to retype the budgets if you s without retyping numbers. Budget Scenario I have a template set up on the Budgetsolution worksheet that you should use to complete the required bu You need to use cell references in the development of your budgets on the BudgetSolution worksh If you type in any numbers in the solution, I will take off 1 pt. for each typed number, since we use 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 The beauty behind excel is that managers can perform what-if analysis just by changing the data, so 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 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 13,680 Inventory 10,757 Accounts Payable $15,781 Actual and Budgeted sales dollar Data-Sales Budget: March (actual) $38,000 April $36,000 May $37,000 June $39,000 July $35,000 Sales are the following type: 64% Cash sales collected in month of sale 36% 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 83% of sales price At the end of each month, inventory is to be on hand(ending inventory) equal to 36% of following month's sales needs, stated at cost. Inventory purchases are paid 49% in month of purchase 51% 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 expenses are as follows and are paid in the month incurred if it is a cash expense. Salaries and wages Commissions Advertising Utilities 8% of sales dollar 3% of sales dollar $900 per month $200 per month Monthly general and administrative expenses are as follows and are paid in the month incurred if it is a c Rent $2,000 per month Depreciation $500 for month Required: You must use cell references on the BudgetSolution worksheet, by referencing this worksheet Prepare the following second quarter budgets and answer the questions listed on the template provided o If I bolded a line item, that is a header and does not need computation on that row. Please note the quarter column is for the quarter so not all lines should be added across in the quarter co 1. Prepare Sales Budget in dollars by month and quarter. 2. Prepare a merchandise purchase budget by month and quarter in dollars. 3. Prepare a Selling Expense Budget, by month and quarter. 4. Prepare a General and Administrative budget, by month and quarter. 5. Prepare a cash budget showing the months and quarter. 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 inc 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 mplete the required budgets and requirements stated below. dgetSolution worksheet using information on this worksheet to reference. umber, since we use Excel so that we can update budgets or do what if analysis without retyping numbers. ormulas in your budgets. he budget worksheet has formatted budgets for you to complete. ging the data, so you do not need to retype the budgets if you of your budgeting skills. e been experiencing cash shortages. formation below to assist you r named Sled Company, a wholesaler of sleds as of March 31. e following month of sale March 31 are a result of March credit sales. needs, stated at cost. ve set up a calculation on the budget worksheet to show you this calculation. cash expense. nth incurred if it is a cash expense. encing this worksheet that contains the data. e template provided on the BudgetSolution Worksheet. I have adapted the budget model to meet the needs of this c ross in the quarter column. When you have beginning and ending inventory or cash balances this is for the quarter ompany in this industry might have. es for you on the income statement dgets are effected? Why? rent back to the original amount of 2,000 before you submit. ithout retyping numbers. el to meet the needs of this company. lances this is for the quarter and should be brought over to the quarter column. #1 Sled Company Sales Budget for the quarter ending June 30, 20XX April May June Quarter Sales #2 Sled Company Purchase budget For the quarter ended June 30, 20XX April May June Quarter Sled Company Selling Expense Budget For the quarter ended June 30,20XX April May June Quarter Budgeted Sales Current Cost of goods sold Desired Ending inventory Total needs Less: Beginning Inventory Total purchase cost #3 Budgeted Sales Variable selling expenses: Salaries and wages Commissions Total variable cash expenses Fixed Selling expenses: Advertising Utilities Total fixed expenses Total selling expenses #4 Sled Company General and Administrative Expense Budget For the quarter ended June 30,20XX April May June Quarter General and Administrative Expenses: Rent Depreciation Total general and administrative expenses #5 Sled Company Cash Budget For the quarter ended June 30, 20XX April May June Quarter Beginning cash Add Cash Collections of Sales: Cash sales collected in month sale Credit sales Collected in following month Total Cash receipts from collection of sales Cash Available for operations Less Cash Disbursements: Cash payments for purchases paid in month Cash payments made in month after purchase Total Cash payments for merchandise purchased Cash Selling Expenses Cash General and administrative expenses Total 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 profit Other Expenses Selling Expenses General and administrative expenses Income(Loss) from operations 112,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 Operating 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