Case Problem 2 Data File Needed For This Case Problem
Case Problem 2data File Needed For This Case Problem Po
Case Problem 2 data file needed for this case problem: Popcorn.xlsx Seattle Popcorn Seattle Popcorn is a small company located in Tacoma, Washington, that produces gourmet popcorn distributed in the Northwest. Steve Wilkes has developed a workbook that will allow him to perform a profit analysis for the company. Using this workbook, he wants to create formulas to determine the break-even point for the company—the sales volume needed so that revenues will match the anticipated monthly expenses. Three factors determine the break-even point: the sales price of each unit of Seattle Popcorn, the variable manufacturing cost to the company for each unit, and the fixed expenses (salaries, rent, insurance, and so on) that the company must pay each month.
Steve wants to be able to explore a range of possible values for each of these factors, as follows:
• The sales price of each unit of Seattle Popcorn can vary from $5 to $15 (in whole numbers).
• The variable manufacturing cost of each unit can vary from $5 to $15 (in whole numbers).
• The fixed monthly expense for the company can vary from $15,000 to $30,000 (in whole numbers).
Complete the following:
1. Open the Popcorn workbook located in the Excel8\Case2 folder included with your data files, and then save the workbook as Seattle Popcorn. In the Documentation sheet, enter your name and the date.
2. Switch to the Profit Analysis worksheet, and then define names for cells, as follows: cell C15: PricePerUnit, cell C16: CostPerUnit, cell C17: MonthlyExpenses.
3. In the range H3:H43, enter a formula using defined names to calculate the revenue, which is determined by the units sold multiplied by the price per unit.
4. In the range I3:I43, enter a formula using defined names to calculate the expenses, which are determined by the units sold multiplied by the cost per unit plus the fixed monthly expense.
5. In cell C18, enter a formula to calculate the break-even point, which is determined by the fixed monthly expense divided by the difference between the price per unit and the cost per unit. Use the IFERROR function to display a blank cell instead of an error value.
6. In cell C19, enter a formula to calculate the revenue at the break-even point, which is determined by the break-even point multiplied by the sales price per unit. Use the IFERROR function to display a blank cell instead of an error value.
7. Create validation rules for cells C15, C16, and C17 as per the provided specifications, and protect the worksheet so the user can only enter data in these cells.
8. Enter specific values to determine the units Seattle Popcorn must sell each month to break even: sales price $13, manufacturing cost $10, fixed expense $30,000. Add a comment in cell C16 regarding unit cost data.
9. Record a macro named PrintChart with shortcut Ctrl+A, which sets the print area, adjusts page layout, and prints the chart and input/output area. Edit the macro to replace the print command with a print preview. Create a button to run this macro, test it, save the workbook as a macro-enabled file named SP with Macros, and then close the workbook.