{[ promptMessage ]}

Bookmark it

{[ promptMessage ]}


252_CVP_spreadsheet_instructions - BA 252 Management...

Info iconThis preview shows pages 1–3. Sign up to view the full content.

View Full Document Right Arrow Icon
BA 252: Management Accounting Spreadsheet Assignment 1: Strategic Planning and CVP analysis What-if and Goal Seeking Using Excel 2007 The Thomas Lollipop Company, as part of its strategic planning process, is working on its budget. Before preparing a cash budget, management wants to create a proforma income statement as part of its profit planning stage. Your part in this stage is to develop a spreadsheet program for the proforma income statement that will analyze different scenarios. To accomplish this, the program will use the built-in functions for “Scenario Manager” and “Goal Seek.” The “base case” scenario (Scenario a) includes the following budgeted amounts: Sales price $3 per lollipop Volume 150 lollipops per month Variable costs $2 per lollipop Fixed costs $125 per month Part A: Not happy with the projected profit, let’s consider a number of different scenarios. For each scenario, determine the new profit, break-even volume and revenues, margin of safety, and change in each of these outcomes from the base case scenario. Save each scenario. Each of the following scenarios uses the base case data with that scenario’s changes (in other words, don’t use a previous scenario’s data with the next scenario). Scenario b: Increase sales price by 50¢. Project this will decrease sales volume by 5%. Scenario c: Increase sales price by $1.00, and maintain volume by a $100 advertising campaign. Scenario d: Increase sales price by $1.00, reduce quality (which will decrease variable costs 25¢), and spend $200 on an advertising campaign to maintain monthly sales volume at 150 lollipops. Scenario e: Double sales volume by spending $150 on an advertising campaign. Scenario f: Automate some of the production process. This will increase fixed costs by $100 per month, but will reduce variable costs by 25¢. Scenario g: Combine Scenarios e and f. Part B: Not happy with the outcomes from the scenarios, management next asked a series of questions. Answer each one using the base case data. 1. How many lollipops have to be sold to make an additional $50 monthly profit? 2. What will the sales price have to be in order to reduce breakeven volume to 100 lollipops? 3. How much will variable costs have to be reduced to increase profit to $40 per month? 4. How much will fixed costs have to be reduced so that margin of safety = 20%? 511137205fee669d0826e91751951a63b69d2eac.doc, updated 3/12/2010
Background image of page 1

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full Document Right Arrow Icon
CVP spreadsheet assignment instructions, page 2 of 8 Instructions for Creating the Spreadsheet Program Each project is to be individually done. This means two or more students cannot use the same spreadsheet file. I encourage you to work together, though. Just make sure each of you has his/her own computer, you each create your own files, and you each save your individual files on separate disks. These instructions are very detailed. To those of you who do not need this much detail, I apologize. But, at least skim over these instructions to make sure you do not miss something (and lose points when I grade your project).
Background image of page 2
Image of page 3
This is the end of the preview. Sign up to access the rest of the document.

{[ snackBarMessage ]}