Answered step by step
Verified Expert Solution
Question
1 Approved Answer
Developing your own Financial Planning Model for Sensitivity Analysis- Practicing What you Learnt! Chapter 9: Developing a Master Budget in Excel with linked formulas Textbook:
Developing your own Financial Planning Model for Sensitivity Analysis- Practicing What you Learnt! Chapter 9: Developing a Master Budget in Excel with linked formulas Textbook: Managerial Accounting by Garrison, Libby and Webb, McGrawhill 2021. 12 th Edition. For the master budget problem from your textbook (see Problem 9-24) listed below, you are required to develop a Microsoft Excel based Financial Planning model. You will be only required to do the following budget schedules with linked Excel formulas (i.e. the set of budgets I did as the in-class problem in class). My handwritten notes are on Bright Space if you missed that class. Instructions: - In the Excel spreadsheet keep the problem data (budget assumptions) separate in one section of the spreadsheet. This is to be able to link the budget formulas so that the master budgets do not have to be recreated when the data is manipulated. - Then link the data using Excel formulas to develop the following budgets and schedules for the three months (April, May, June) and in total for Quarter 2 (Q2). You may have to keep an extra column for March and July as I did in class. 1. Schedule 1: Sales budget for (April, May, June) and Total for Q2. Also show how much is cash sales and how much are credit sales 2. Schedule 2: Merchandise Purchase Budget (April, May, June) and Total for Q2 3. Schedule 3: Selling and Administration Budget (April, May, June) and Total for Q2 4. Budgeted Income Statement in total for the quarter (Total for Q2) 5. Sensitivity Analysis 5(a) Change the Gross Margin from 25\% to 30%. Print the Budgeted Income Statement in total for the quarter (Total for Q2) 5(b) Change the gross margin from 25% to 35% and increase sales commission from 12% to 15% and other expenses from 6% to 10%. Print the Budgeted Income Statement in total for the quarter (Total for Q2) Printouts to be submitted for grading 1. For questions 1 through 4 , printout Schedule1, Schedule 2 Schedule 3 and the quarterly income statement. 2. For 5(a) Print the Budgeted Income Statement in total for the quarter (Total for Q2) 3. For 5(b) Print the Budgeted Income Statement in total for the quarter (Total for Q2) PROBLEM 9-24 Completing a Master Budget [LO2] 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. Sales are 60% for cash and 40% on credit. Credit sales are collected in the month following sale. The accounts receivable at March 31 are the result of March credit sales. d. iEach month's ending inventory should equal 80% of the following month's budgeted cost of goods sold. c. One-balf of a month's inventory purchases is paid for in the month of purchase; the other one-half is paid for in the following month. The accounts payable at March 31 are the result of March parchases of inventory. f Monthly expenses are as follows: commissions, 12% of sales; rent, $2,500; ocher expenses (excluding depreciation), 6% of sales. Assume that these expeases are paid monthly. Depreciation is $900 per month and includes depreciation on new assets. (1) Prepare the operating Brolgets (2) Prepare an Incoune statement for the guarter. Developing your own Financial Planning Model for Sensitivity Analysis- Practicing What you Learnt! Chapter 9: Developing a Master Budget in Excel with linked formulas Textbook: Managerial Accounting by Garrison, Libby and Webb, McGrawhill 2021. 12 th Edition. For the master budget problem from your textbook (see Problem 9-24) listed below, you are required to develop a Microsoft Excel based Financial Planning model. You will be only required to do the following budget schedules with linked Excel formulas (i.e. the set of budgets I did as the in-class problem in class). My handwritten notes are on Bright Space if you missed that class. Instructions: - In the Excel spreadsheet keep the problem data (budget assumptions) separate in one section of the spreadsheet. This is to be able to link the budget formulas so that the master budgets do not have to be recreated when the data is manipulated. - Then link the data using Excel formulas to develop the following budgets and schedules for the three months (April, May, June) and in total for Quarter 2 (Q2). You may have to keep an extra column for March and July as I did in class. 1. Schedule 1: Sales budget for (April, May, June) and Total for Q2. Also show how much is cash sales and how much are credit sales 2. Schedule 2: Merchandise Purchase Budget (April, May, June) and Total for Q2 3. Schedule 3: Selling and Administration Budget (April, May, June) and Total for Q2 4. Budgeted Income Statement in total for the quarter (Total for Q2) 5. Sensitivity Analysis 5(a) Change the Gross Margin from 25\% to 30%. Print the Budgeted Income Statement in total for the quarter (Total for Q2) 5(b) Change the gross margin from 25% to 35% and increase sales commission from 12% to 15% and other expenses from 6% to 10%. Print the Budgeted Income Statement in total for the quarter (Total for Q2) Printouts to be submitted for grading 1. For questions 1 through 4 , printout Schedule1, Schedule 2 Schedule 3 and the quarterly income statement. 2. For 5(a) Print the Budgeted Income Statement in total for the quarter (Total for Q2) 3. For 5(b) Print the Budgeted Income Statement in total for the quarter (Total for Q2) PROBLEM 9-24 Completing a Master Budget [LO2] 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. Sales are 60% for cash and 40% on credit. Credit sales are collected in the month following sale. The accounts receivable at March 31 are the result of March credit sales. d. iEach month's ending inventory should equal 80% of the following month's budgeted cost of goods sold. c. One-balf of a month's inventory purchases is paid for in the month of purchase; the other one-half is paid for in the following month. The accounts payable at March 31 are the result of March parchases of inventory. f Monthly expenses are as follows: commissions, 12% of sales; rent, $2,500; ocher expenses (excluding depreciation), 6% of sales. Assume that these expeases are paid monthly. Depreciation is $900 per month and includes depreciation on new assets. (1) Prepare the operating Brolgets (2) Prepare an Incoune statement for the guarter
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started