Answered step by step
Verified Expert Solution
Link Copied!

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:

image text in transcribedimage text in transcribedimage text in transcribed 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

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image

Step: 3

blur-text-image

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

Integrated Audit Practice Case

Authors: David S. Kerr, Randal J. Elder, Alvin A. Arena

6th Edition

ISBN: 0912503564, 9780912503561

More Books

Students also viewed these Accounting questions