The questions are Part A and part B. Rather than just a screenshot if its possible you can walk me through it or explain how I would do each part in excel.
Chapter 9: Budgeting: Textbook: Managerial Accounting by Garrison, Libby and Webb, McGrawhill 2021. 12 th Edition For the Budget Case study problem from your textbook (see Problem 9-24: Completing a Master Budget) listed below which I illustrated in detail in the class, you are required to recreate the operating budgets for the months of April, May, June and in total and the income statement for the three months using Microsoft Excel. The following data relate to the operations of Shilow Company, a wholesale distributor of consumer goods. a. The gross margin is 25% of sales b. Actual and budgeted sales data are as follows: c. Each month ending inventory should equal 80% of the following month's budgeted cost of goods sold. d. Monthly expenses are follows: Commissions 12% of sales; rent $2,500; other expenses (excluding depreciation), 6% of sales; Depreciation is $900 per month. Required: Prepare the following budgets as a Microsoft Excel financial planning model to determine the operating income for the three months. Do not prepare the cash budget or the budgeted balance sheet. IMPORTANT: The data cells in the Excel spreadsheet should be linked so that senior management are able to carry out what-if or sensitivity analysis. (Part A) Prepare the following Operating Budgets: 1. Monthly Sales Budget (April, May, June) and in Total 2. Monthly Merchandise Purchase Budget (April, May, June) and in Total 3. Monthly Selling and Administration Budget (April, May, June) and in Total 4. Budgeted Income Statement in Total (Part B) Sensitivity Analysis: (What-if-Analysis) Change the gross margin percentage to 40% of sales? What happens to the Operating Income? Comment. You can provide your comments on the spreadsheet it self. Hint: (1) For Part B, you do not need to re-do all the budgets. Just changing the number in the Excel Cell where you have the gross margin percentage should be the proper way to design the spread sheet. (2) Keep all the data in one area on the spreadsheet, then link the cells from there. Chapter 9: Budgeting: Textbook: Managerial Accounting by Garrison, Libby and Webb, McGrawhill 2021. 12 th Edition For the Budget Case study problem from your textbook (see Problem 9-24: Completing a Master Budget) listed below which I illustrated in detail in the class, you are required to recreate the operating budgets for the months of April, May, June and in total and the income statement for the three months using Microsoft Excel. The following data relate to the operations of Shilow Company, a wholesale distributor of consumer goods. a. The gross margin is 25% of sales b. Actual and budgeted sales data are as follows: c. Each month ending inventory should equal 80% of the following month's budgeted cost of goods sold. d. Monthly expenses are follows: Commissions 12% of sales; rent $2,500; other expenses (excluding depreciation), 6% of sales; Depreciation is $900 per month. Required: Prepare the following budgets as a Microsoft Excel financial planning model to determine the operating income for the three months. Do not prepare the cash budget or the budgeted balance sheet. IMPORTANT: The data cells in the Excel spreadsheet should be linked so that senior management are able to carry out what-if or sensitivity analysis. (Part A) Prepare the following Operating Budgets: 1. Monthly Sales Budget (April, May, June) and in Total 2. Monthly Merchandise Purchase Budget (April, May, June) and in Total 3. Monthly Selling and Administration Budget (April, May, June) and in Total 4. Budgeted Income Statement in Total (Part B) Sensitivity Analysis: (What-if-Analysis) Change the gross margin percentage to 40% of sales? What happens to the Operating Income? Comment. You can provide your comments on the spreadsheet it self. Hint: (1) For Part B, you do not need to re-do all the budgets. Just changing the number in the Excel Cell where you have the gross margin percentage should be the proper way to design the spread sheet. (2) Keep all the data in one area on the spreadsheet, then link the cells from there
