AutoSave OFF 2020 Summer Project 4 Q Home Insert Draw Page Layout Formulas Data Review View Tell me e Share 0 Comments 1 Insert v 5 Delete v v Ideas Format v Sort & Filter Find & Select B G H 1 K L N Arial 12 v = = General H LL Paste BI U A = $ % ) Conditional Format Cell Formatting as Table Styles B17 fx A D E F M 1 Budget Scenario I have a template set up on the Budgetsolution worksheet that you should use to complete the required budgets and requirements stated below. You need to use cell references in the development of your budgets on the BudgetSolution worksheet using information on this worksheet to reference. If you type in any numbers in the solution, I will take off 1 pt. for each typed number, since we use Excel so that we can update budgets or do what if analysis without retyping numbers. 5 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. 7 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 8 have used cell references and formulas throughout. 9 10 Data Scenario: 11 You have just been hired into a management position which requires the application of your budgeting skills. 12 You find out that budgeting has not been a priority of the company and that they have been experiencing cash shortages. 13 You have contacted various areas on the organization and have accumulated the information below to assist you 14 in preparing a comprehensive budget. 15 16 The following is actual information that relates to the operations of a merchandiser named Sled Company, a wholesaler of sleds as of March 31. 17 Cash $1,000 18 Accounts receivable 13,680 19 Inventory 10,757 20 Accounts Payable S15,781 21 22 Actual and Budgeted sales dollar Data-Sales Budget: 23 March (actual) $38,000 24 April $36,000 25 May S37,000 26 June $39,000 27 July $35,000 29 Sales are the following type: 64% Cash sales collected in month of sale 30 36% Credit sales collected in the following month of sale 31 Credit sales are collected in the month following sale. The accounts receivable at March 31 are a result of March credit sales. 32 33 Cost of goods sold equals 83% of sales price 34 Overview Project Budget BudgetSolution + Ready BE 100% AutoSave OFF JES 5 = 2020 Summer Project 4 Q Home Insert Draw Page Layout Formulas Data Review View Tell me e Share 0 Comments Arial 12 AS A Insert v = = General 1 H 5 LL Delete v Paste BI U v = $ % ) Ideas Conditional Format Cell Formatting as Table Styles Format v Sort & Filter Find & Select - J B17 fx A B D E F H L M N 0 35 At the end of each month, inventory is to be on hand(ending inventory) equal to 36 36% of following month's sales needs, stated at cost. 37 38 Inventory purchases are paid 49% in month of purchase 39 51% in month after purchase 40 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 calculation 41 42 Monthly selling expenses are as follows and are paid in the month incurred if it is a cash expense. 43 Salaries and wages 8% of sales dollar 44 Commissions 3% of sales dollar 45 Advertising $900 per month 46 Utilities $200 per month 47 48 Monthly general and administrative expenses are as follows and are paid in the month incurred if it is a cash expense. 49 Rent $2,000 per month 50 Depreciation $500 for month 51 52 Required: You must use cell references on the BudgetSolution worksheet by referencing this worksheet that contains the data. 53 Prepare the following second quarter budgets and answer the questions listed on the template provided on the BudgetSolution Worksheet. Thave adapted the budget model to meet the needs of this company. 54 if I bolded a line item, that is a header and does not need computation on that row. 55 Please note the quarter column is for the quarter so not all lines should be added across in the quarter column. When you have beginning and ending inventory or cash balances this is for the quarter and should be brought over to 56 1. Prepare Sales Budget in dollars by month and quarter, 57 2. Prepare a merchandise purchase budget by month and quarter in dollars 58 3. Prepare a Selling Expense Budget by month and quarter. 59 4. Prepare a General and Administrative budget, by month and quarter. 60 5. Prepare a cash budget showing the months and quarter, 61 6. Based on the quarterly cash budget you prepared, do you have any recommendations 62 on cash management Discuss the type of business and the cash flow problems a company in this industry might have. 63 Type your answer on the budget worksheet, where I have set out the question. 64 7. Prepare a budgeted income statement for the quarter ending June 30, 20XX. 65 You do not need to show monthly columns. I entered Sales for you on the Income statement 66 8. What do you think about the survivability of this business? 67 9. What if the company finds out the monthly rent will increase to $2,500, what budgets are effected? Why? Overview Project Budget BudgetSolution + Ready E CU 100% AutoSave OFF 2020 Summer Project 4 Q Home Insert Draw Page Layout Formulas Data Review View Tell me e Share 0 Comments Arial 12 v Insert v = = General 1 H 5 LL Delete v Paste BI U v A = $ % Y Ideas Conditional Format Cell Formatting as Table Styles Format v Sort & Filter Find & Select K L M N 0 B17 fx A B E F H 68 What is the New Net income(Loss) for the quarter? 69 if you have linked everything correctly, you should only have to change the monthly rent 70 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 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 Overview Project Budget BudgetSolution + Ready 100% AutoSave OFF DES 5 = 2020Summer Project4 Q Home Insert Draw Page Layout Formulas Data Review View Tell me Le Share 0 Comments Arial 12 Insert v = = General 1 H 5 Delete v Paste BI U A = $ % ) Ideas Conditional Format Cell Formatting as Table Styles Format v Sort & Filter Find & Select G16 A C D - E F G H J K L M N 1 #1 2 Sled Company Sales Budget for the quarter ending June 30, 20XX 3 April May June Quarter Sled Company Purchase budget For the quarter ended June 30, 20XX April May June Quarter 4 5 6 Sales 7 8 #2 9 10 11 12 13 Budgeted Sales 14 Current Cost of goods sold 15 Desired Ending inventory 16 Total needs 17 Less: Beginning Inventory 18 Total purchase oost 19 20 #3 21 22 23 24 25 Budgeted Sales 26 Variable selling expenses: 27 Salaries and wages 28 Commissions 29 Total variable cash expenses 30 Fixed Selling expenses: 31 Advertising 32 Utilities 33 Total fixed expenses 2. Talias DVERE Sled Company Selling Expense Budget For the quarter ended June 30,20XX April May June Quarter Overview Project Budget BudgetSolution + Ready 100% AutoSave OFF DES 5 = 2020Summer Project4 Q Home Insert Draw Page Layout Formulas Data Review View Tell me e Share 0 Comments Arial v 12 Insert v = = > General 1 H 4 LL x Delete v Paste BI U A $ %) Ideas Conditional Format Cell Formatting as Table Styles Format v Sort & Filter Find & Select G16 fx A C D E F H T J K L M N Sled Company General and Administrative Expense Budget For the quarter ended June 30,20XX April May June Quarter 34 Total selling expenses 35 36 #4 37 38 39 40 41 42 General and Administrative Expenses: 43 Rent 44 Depreciation 45 Total general and administrative expenses 46 47 #5 48 49 50 51 52 Beginning cash 53 Add Cash Collections of Sales: 54 Cash sales collected in month sale 55 Credit sales Collected in following month 56 Total Cash receipts from collection of sales 57 Cash Available for operations 58 Less Cash Disbursements: 59 Cash payments for purchases paid in month 60 Cash payments made in month after purchase 61 Total Cash payments for merchandise purchased 62 Cash Selling Expenses 63 Cash General and administrative expenses 64 Total cash Disbursements 65 Ending cash 66 Sled Company Cash Budget For the quarter ended June 30, 20XX April May June Quarter Overview Project Budget BudgetSolution + Ready 100% AutoSave OFF DES 5 = 2020Summer Project4 Q Home Insert Draw Page Layout Formulas Data Review View Tell me Le Share 0 Comments Arial v 12 Insert v = = > 1 General H 4 LL x Delete v Paste BI U A $ % ) Ideas Conditional Format Cell Formatting as Table Styles Format v Sort & Filter Find & Select G16 fx A H T J K L M N B C D E F 68 6. Based on the quarterly cash budget you prepared, do you have any recommendations G9 on cash management Discuss the type of business and the cash flow problems a company in this industry might have. 70 71 72 73 #7 74 Sled Company 75 Budgeted Income Statement 76 For the quarter ended June 30, 20XX 77 78 Sales 112,000 79 Cost of goods sold 80 Gross profit 81 Other Expenses 82 Selling Expenses 83 General and administrative expenses 84 Income (Loss) from operations 85 86 8. What do you think about the survivability of this business? 87 88 89 90 9. What if the company finds out the monthly rent will increase to $2,500, what budgets are effected? Why? 91 What is the New Operating Income (Loss) for the quarter? 92 93 94 If you have linked everything correctly, you should only have to change the monthly rent 95 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 96 97 98 99 100 101 Overview Project Budget BudgetSolution + Ready 100%