Please note these may be out of order. Follow the number in steps. 1,2,3

3. Name the first new worksheet Scenario Manager. In this worksheet, use Scenario Manager as shown in Figure 2 to see the impact of 2 different scenarios on net income; one scenario is called Large Venue, the other Small Venue. After you have added the 2 scenarios, click on Summary and the Result cell is G26 (as shown in the Figure). a. Large Venue b. Small Venue Seats 1000 250 Talent $15,000 $3,500 Venue Rental $3,000 $700 Amplification $700 $300 Lighting $500 $100 Security $300 $100 Insurance $250 $50 5 6 7 8 9 D Scenario Summary Current Values: Small venue Large Venue Changing Cells: $B$5 500 250 1000 $B$18 $6,000 $3,500 $15,000 $B$19 $1,250 $700 $3,000 $B$20 $420 $300 $700 SB$21 $250 $100 $500 $B$22 $150 $100 $300 $B$23 $100 $50 $250 Result Cells: $G$26 $6,877 $3,006 $11,538 Notes: Current Values column represents values of changing cells at time Cronario Siimmar Ronart. Created changing calls for each Scenario Summary Analysis Scenario Manager Fixed Cost GoalSeek CondFormat 10 11 12 13 14 15 16 Ready 4. Name the next copied worksheet Fixed Costs. Create a 3-D pie chart that displays the theatre's fixed costs. 5. Name the next copied worksheet GoalSeek. Use Goal seek to show how many tickets must be sold to get an Operating Income of $15,000. The answer is 617.87 tickets. 6. Name the last copied worksheet ConditionalFormat. Use Conditional Formatting to change cell G26 to Light Red a with Dark Red Text when Net income falls below 0 and for it to change to a Green Fill with Dark Green Text when the income is positive. Change the number of tickets to 20 to show the conditional formatting. Excel Case #4 Excel Skills: SUM Function, Conditional Formatting, Goal Seek, Scenario Manager Lala Films wants to host a theatrical show and is undecided as to what kind of venue to book for the show. Their financial data is shown in Figure 1. The cells in light yellow are formulas and the cells in white are numbers which yo can simply type in. 1. Start with Case 4.xlsx, make Worksheet 1 look like Figure 1. Name the worksheet "Analysis". The Input Sect on the left contains numbers (except sums in rows 11, and 24) while the Output Section contains formula based on the following: . Revenue is calculated by multiplying the revenue items (B8 through B10) with the tickets sold B5 (e. G6 is B8 times B5). Variable expenses are the variable expenses times tickets sold Contribution Margin=Total Revenue-Total Variable expenses Operating Income is calculated by deducting the fixed expenses from the contribution margin Income taxes are calculated by multiplying the operating income with the tax rate in cell B6. Net Income is the difference between the operating income and taxes O A B 0 E F C 3 Bud 5 Venue #tickets 500 Revenue 6 Tax Rate 35% Tickets $15,000 7 Revenue per ticket Merchandising $2,500 8 Ticket Price $30 Food & beverage $7,500 9 Merchandising $5 Total Revenue per ticket $25,000 10 Food & beverage $15 Variable Expenses 11 Total Revenue per ticket $50 Ticketing $1,750 12 Variable costs Merchandising $1,000 13 Ticketing $3.50 Food & beverage $3.500 14 Merchandising $2 Total Variable Expenses $6,250 15 Food & beverage $7 Contribution Margin $18,750 16 Total Variable Cost $13 Fixed Expenses 17 Fixed Cost Talent $6,000 18 Talent $6,000 Venue rental $1,250 19 Venue rental $1,250 Amplification $420 20 Amplification $420 Lighting $250 21 Lighting $250 Security $150 22 Security $150 Insurance $100 23 insurance $100 Total Fixed Expenses $8.1701 24 Total Fixed costs $8,170 Operating Income $10,580 25 Income Taxes $3,703.00 26 Net Income $6.872 Figure 1: Analysis tab 2. Once you have completed the initial worksheet, copy your solution to four new worksheets. File Home Insert Page Layout Formulas Data Review View Help O PROTECTED VIEW Be careful_files from the Internet can contain viruses. Unless you need to edit, it's safer to stay in Protected View. Enable Editing F9 f Total Revenue B D E F G H J KL . 1 2 - 3 : S Venue: #tickets 6 Tax Rate 7 Revenue per ticket 8 Ticket Price 9 Merchandising 10 Food & beverage 11 Total Revenue per ticket 12 Variable Costs 13 Ticketing 14 Merchandising 15 Food & beverage 16 Total Variable Cost 17 Fixed Cost 18 Talent 19 Venue rental 20 Amplification 21 Lighting 22 Security 23 Insurance 24 Total Fixed costs Revenue Tickets Merchandising Food & beverage Total Revenue Variable Expenses Ticketing Merchandising Food & beverage Total Variable Expenses Contribution Margin Fixed Expenses Talent Venue rental Amplification Lighting Security Insurance Total Fixed Expenses Operating Income Income Taxes Net Income 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41