I have been able to do some of this without much trouble but I am really stuck on #2, #5, #7 and #9. can anyone show me what to do?

- 0 Comments AutoSave H H = 2019SummerFinal Project due 810 - Excel katie_sandage@yahoo.com File Home Insert Page Layout Formulas Data Review View Help Search Share A1 V Part 1 J K L M 35 At the end of each month, inventory is to be on hand(ending inventory) equal to 40% of following month's sales needs, stated at cost. Therefore, inventory on hand on March 31st is April's sales* 8*4. 39 38 Inventory purchases are paid 49% in month of purchase 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 10% of sales dollar 44 Commissions 4% of sales dollar 45 Advertising $900 per month 46 Utilities $200 per month 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 $900 for month 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 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 qu 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. 1 R Rased on the quarterly cash bino ou nenarod do vou have any recommendations OverviewFinalProject Page Budgetsolution Part 2 Part3 Part 4 10:54 PM Type here to search 2019SummerfinalProject due 810 - Excel katie_sandage@yahoo.com 0 - 0 Comments Insert Page Layout Formulas Data Review View Help Search Share AutoSave File Home A1 A A 45 Advertising 46 Utilities X & Part 1 $900 per month $200 per month 18 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 so Depreciation $900 for month 52 Required: You must use cell references on the Budget Solution 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 Budget Solution Worksheet. I have adapted the budget model to meet the needs 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 qu 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? 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 OverviewFinal Project Part1 Budget Solution Part 2 Part3 Part 4 Ready 10:54 PM # O Type here to search 8/7/2019 AutoSave Got H ile Home Insert katie_sandage@yahoo.com - 2019SummerFinalProject due 810 - Excel Help Search 0 Come Page Layout Formulas Data Review View Share # 1 Sled Company Sales Budget for the quarter ending June 30, 20XX May Sales cash sale credit sale 62% 38% Apnl $ $ $ 36,000 $ 22,320 $ 13,680 $ June 37,000 $ 22,940 $ 14,060 $ Quarter 39,000 $ 112,000 24,180 $ 69,440 14,820 $ 42,560 #2 Sled Company Purchase budget For the quarter ended June 30, 20XX April June Quarter $ 36 000 $ 37 000 $ 39,000 S 112,000 $29,520 $30,340 31,980 91,840 May 9000 82% Budgeted Sales Current Cost of goods sold add. Desired Ending inventory Total needs Less: Beginning Inventory Total purchase cost Totaesire Sled Company Selling Expense Budget For the quarter ended June 30, 20XX April May June Quarter $ 36,000 37,000 39.000 $ 112,000 Part 2 Part3 Part 4 Budgeted Sales OverviewFinal Project Part1 BudgetSolution - Dox 10:55 PM O Type here to search 8/7/2019 2019SummerFinalProject due 810 - Excel katie_sandage@yahoo.com AutoSave Of File Home C66 H Insert x = Page Layout x Formulas Data Review View Help Search Sled Company Selling Expense Budget For the quarter ended June 30, 20XX April May June Quarter $ 35,000 37,000 39,000 $ 112,000 10% 4% $ $ $ 3,600 $ 1,440 5,040 $ 3,700 1,480 5,180 3,900 1.560 5,460 11,200 4.480 15.680 $ $ $ 27 Budgeted Sales 28 Variable selling expenses: 29 Salaries and wages 30 Commissions 31 Total variable cash expenses 32 Fixed Selling expenses: 33 Advertising 34 Utilities 35 Total fixed expenses 36 Total selling expenses 900 200 $1,100 6,140 900 200 $1,100 6,280 900 200 $1,100 6,560 $ 2,700 600 $3,300 18,980 $ S . 3> 38 #4 Sled Company General and Administrative Expense Budget For the quarter ended June 30, 20XX April June Quarter 44 General and Administrative Expenses: 45 Rent 46 Depreciation 47 Total general and administrative expenses $2,000 900 $2.900 $2,000 900 $2.900 $2,000 900 $2,900 $6,000 2,700 $8,700 49 #5 OverviewFinalProject Part1 BudgetSolution Part 2 Part 3 Part 4 Ready O Type here to search AutoSave H 2019SummerfinalProject due 810 - Excel katie_ 1 File Home Insert Page Layout Formulas Data Review View Help Search C66 x vf DELFI G 49 #5 Sled Company Cash Budget For the quarter ended June 30, 20XX April May June Quarter ($1,563) ($3,633) - 54 Beginning cash 55 Add Cash Collections of Sales: 56 Cash sales collected in month sale 57 Credit sales Collected in following month 58 Total Cash receipts from collection of sales 59 Cash Available for operations 60 Less Cash Disbursements: 61 Cash payments for purchases paid in month 62 Cash payments made in month after purchase 63 Total Cash payments for merchandise purchased 64 Cash Variable Selling Expenses 65 Cash General and administrative expenses 66 Total cash Disbursements 67 Ending cash 68 2.000 2,000 2,000 6,000 70 6. Based on the quarterly cash budget you prepared, do you have any recommendations 71 on cash management. Discuss the type of business and the cash flow problems a company in this industry might have. 75 #7 OverviewFinalProject Part1 BudgetSolution Part 2 Part3 Part 4 Ready O Type here to search AutoSave of H 2019SummerFinalProject due 910 - Excel File Home katie_sandage Insert Page Layout Formulas Data Review View Help Search C66 x r & Sled Company Budgeted Income Statement For the quarter ended June 30, 20XX 80 Sales 81 Cost of goods sold 82 Gross profit 83 Other Expenses 84 Selling Expenses 85 General and administrative expenses 86 Income(Loss) from operations 87 88 8. What do you think about the survivability of this business? 92 9. What if the company finds out the monthly rent will increase to $2,500, what budgets are effected? Why? 93 What is the New Operating income(Loss) for the quarter? New Operating Income 96 If you have linked everything correctly, you should only have to change the monthly rent 97 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. OverviewFinalProject Part1 BudgetSolution Part 2 Part3 Part 4 Ready - 0 Comments AutoSave H H = 2019SummerFinal Project due 810 - Excel katie_sandage@yahoo.com File Home Insert Page Layout Formulas Data Review View Help Search Share A1 V Part 1 J K L M 35 At the end of each month, inventory is to be on hand(ending inventory) equal to 40% of following month's sales needs, stated at cost. Therefore, inventory on hand on March 31st is April's sales* 8*4. 39 38 Inventory purchases are paid 49% in month of purchase 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 10% of sales dollar 44 Commissions 4% of sales dollar 45 Advertising $900 per month 46 Utilities $200 per month 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 $900 for month 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 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 qu 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. 1 R Rased on the quarterly cash bino ou nenarod do vou have any recommendations OverviewFinalProject Page Budgetsolution Part 2 Part3 Part 4 10:54 PM Type here to search 2019SummerfinalProject due 810 - Excel katie_sandage@yahoo.com 0 - 0 Comments Insert Page Layout Formulas Data Review View Help Search Share AutoSave File Home A1 A A 45 Advertising 46 Utilities X & Part 1 $900 per month $200 per month 18 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 so Depreciation $900 for month 52 Required: You must use cell references on the Budget Solution 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 Budget Solution Worksheet. I have adapted the budget model to meet the needs 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 qu 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? 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 OverviewFinal Project Part1 Budget Solution Part 2 Part3 Part 4 Ready 10:54 PM # O Type here to search 8/7/2019 AutoSave Got H ile Home Insert katie_sandage@yahoo.com - 2019SummerFinalProject due 810 - Excel Help Search 0 Come Page Layout Formulas Data Review View Share # 1 Sled Company Sales Budget for the quarter ending June 30, 20XX May Sales cash sale credit sale 62% 38% Apnl $ $ $ 36,000 $ 22,320 $ 13,680 $ June 37,000 $ 22,940 $ 14,060 $ Quarter 39,000 $ 112,000 24,180 $ 69,440 14,820 $ 42,560 #2 Sled Company Purchase budget For the quarter ended June 30, 20XX April June Quarter $ 36 000 $ 37 000 $ 39,000 S 112,000 $29,520 $30,340 31,980 91,840 May 9000 82% Budgeted Sales Current Cost of goods sold add. Desired Ending inventory Total needs Less: Beginning Inventory Total purchase cost Totaesire Sled Company Selling Expense Budget For the quarter ended June 30, 20XX April May June Quarter $ 36,000 37,000 39.000 $ 112,000 Part 2 Part3 Part 4 Budgeted Sales OverviewFinal Project Part1 BudgetSolution - Dox 10:55 PM O Type here to search 8/7/2019 2019SummerFinalProject due 810 - Excel katie_sandage@yahoo.com AutoSave Of File Home C66 H Insert x = Page Layout x Formulas Data Review View Help Search Sled Company Selling Expense Budget For the quarter ended June 30, 20XX April May June Quarter $ 35,000 37,000 39,000 $ 112,000 10% 4% $ $ $ 3,600 $ 1,440 5,040 $ 3,700 1,480 5,180 3,900 1.560 5,460 11,200 4.480 15.680 $ $ $ 27 Budgeted Sales 28 Variable selling expenses: 29 Salaries and wages 30 Commissions 31 Total variable cash expenses 32 Fixed Selling expenses: 33 Advertising 34 Utilities 35 Total fixed expenses 36 Total selling expenses 900 200 $1,100 6,140 900 200 $1,100 6,280 900 200 $1,100 6,560 $ 2,700 600 $3,300 18,980 $ S . 3> 38 #4 Sled Company General and Administrative Expense Budget For the quarter ended June 30, 20XX April June Quarter 44 General and Administrative Expenses: 45 Rent 46 Depreciation 47 Total general and administrative expenses $2,000 900 $2.900 $2,000 900 $2.900 $2,000 900 $2,900 $6,000 2,700 $8,700 49 #5 OverviewFinalProject Part1 BudgetSolution Part 2 Part 3 Part 4 Ready O Type here to search AutoSave H 2019SummerfinalProject due 810 - Excel katie_ 1 File Home Insert Page Layout Formulas Data Review View Help Search C66 x vf DELFI G 49 #5 Sled Company Cash Budget For the quarter ended June 30, 20XX April May June Quarter ($1,563) ($3,633) - 54 Beginning cash 55 Add Cash Collections of Sales: 56 Cash sales collected in month sale 57 Credit sales Collected in following month 58 Total Cash receipts from collection of sales 59 Cash Available for operations 60 Less Cash Disbursements: 61 Cash payments for purchases paid in month 62 Cash payments made in month after purchase 63 Total Cash payments for merchandise purchased 64 Cash Variable Selling Expenses 65 Cash General and administrative expenses 66 Total cash Disbursements 67 Ending cash 68 2.000 2,000 2,000 6,000 70 6. Based on the quarterly cash budget you prepared, do you have any recommendations 71 on cash management. Discuss the type of business and the cash flow problems a company in this industry might have. 75 #7 OverviewFinalProject Part1 BudgetSolution Part 2 Part3 Part 4 Ready O Type here to search AutoSave of H 2019SummerFinalProject due 910 - Excel File Home katie_sandage Insert Page Layout Formulas Data Review View Help Search C66 x r & Sled Company Budgeted Income Statement For the quarter ended June 30, 20XX 80 Sales 81 Cost of goods sold 82 Gross profit 83 Other Expenses 84 Selling Expenses 85 General and administrative expenses 86 Income(Loss) from operations 87 88 8. What do you think about the survivability of this business? 92 9. What if the company finds out the monthly rent will increase to $2,500, what budgets are effected? Why? 93 What is the New Operating income(Loss) for the quarter? New Operating Income 96 If you have linked everything correctly, you should only have to change the monthly rent 97 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. OverviewFinalProject Part1 BudgetSolution Part 2 Part3 Part 4 Ready