Need all parts answered ASAP!

16 A B D E F G I J L L M N Input Analysis 100 # of Events 1 2 3 4 5 6 7 Food 60% 10% $10,000.00 0.20% Attendance Merchandise 70% 5% $20,000.00 0.03% Programs Product Type Cost of Goods Sold (COGS) Commission Fixed Costs Employees (% of Expected Profit) Salary (per employee) Arena Fee Food Model Inputs Programs 40% 20% $5,000.00 0.05% $50.00 $100,000.00 Merchandise Statistic Average Standard Deviation 95% Confidence interval Programs Food Merchandise Event Profitability Analysis Product Type Correlation with Attendance Attendance 18000 Gross Profit Programs Food Merchandise Programs Food Merchandise Product Type Sales Cost of Goods Sold (COGS) Gross Profit Product Type Sales Forecast Upper Limit for Sales Lower Limit for Sales Operating Expenses Programs Food Merchandise What-if Analysis Product Type Salary Expenses Commissions Fixed Costs Total Operating Expenses Attendance Forecast 18000 15000 21000 8 9 10 11 12 13 14 15 16 16 17 18 19 13 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 Profit before Arena Fee Arena Fee $50,000.00 $75,000.00 $100,000.00 $125,000.00 $150,000.00 $175,000.00 $200,000.00 Number of Employees Profit Summary Total Questions Question 1: The sales for which product type are most highly correlated with attendance? Item Sales Gross Profit Total Operating Expenses Profit before Arena Fee Arena Fee Net Profit Question 2: How likely is it that food sales would exceed $220,000 if attendance is 18000? Question 3: Which product generates the largest gross profit? Question 4: Which product will require the fewest employees? Question 5: Would the event be profitable with a 150,000 arena fee if 15000 people were in attendance? Event Profit Past Events Pivot Table Questions Bakery + Event Profitability You are the owner of a small business that manages the program, food, and merchandise sales at the Excel Center, where the local professional basketball team plays. In addition to basketball games, the Excel Center hosts dozens of event a year. Your company handles all sales made inside the arena. There are three major products that you sell: programs, food, and merchandise. You have decided to build a spreadsheet model to help you analyze and project the profitability of future events. The 'Event Profit' worksheet contains this model. You have randomly selected sales and attendance data from a number of past events to help build your model. This data can be found on the on the 'Past Events' worksheet. You will complete statistics on this model to obtain appropriate values for the inputs for the model. Complete the tasks to finish the model. Whenever possible, reference cells rather than 'hard-coding' values in the cells of your spreadsheet. Task # Points Task Description 1 In the "Input Analysis" section of the spreadsheet model, calculate the average attendance and sales for each type of product from the past events listed on the "Past Events" worksheet. 2 In the "Input Analysis" section of the spreadsheet model, calculate the sample standard deviation for attendance and sales for each type of product from the past events listed on the "Past Events" worksheet (note for Excel 2007 users: Excel 2007 does not support a specific function to calculate sample standard deviations. Use the STDEV function instead). 3 7.5 In the "Input Analysis" section of the spreadsheet model, calculate the 95% confidence interval for the sales for each type of product (notice that you will not calculate a confidence interval for attendance). Use the number of events (calculated in cell 13) as part of your calculations (Note to Excel 2007 users: the CONFIDENCE.NORM function is not supported in Excel 2007. Use the CONFIDENCE function instead). 7.5 In the "Input Analysis" section of the spreadsheet model, calculate the correlations between the sales of each type of product and event attendance. Use appropriate ranges from the "Past Event" worksheet for your calculations. 5 1 The sales for which product type are most highly correlated with attendance? (Select the correct answer from the drop-down list in cell L32.) 6 16 In the "Input Analysis" section of the spreadsheet model, calculate a sales forecast for each type of product if expected attendance at the future event is 18000 people. Reference cell 113 (the attendance forecast) for your calculations. 7 In the "Input Analysis" section of the spreadsheet model, calculate the upper limit for your sales forecast for each type of product if expected attendance at the future event is 18000 people. Reference the confidence interval you created for your calculation. 8 In the "Input Analysis" section of the spreadsheet model, calculate the lower limit for your sales forecast for each type of product if expected attendance at the future event is 18000 people. Reference the confidence interval you created for your calculation. Hou lilcoheni dodano: London 1000ico liati 9 1 How likely is it that food sales would exceed $220,000 if attendance is 18000? (Selected the correct answer from the drop-down list in cell L35.) 10 6 In the "Event Profitability Analysis" section of the model, enter the projected sales for each product type for this event by referencing your sales forecast from the "Input Analysis" section of the model. 11 Calculate the cost of goods sold (COGS) for each product. The COGS is calculated as the sales for each product type times the COGS percentage from the "Model Inputs" section of the model for that product type. 12 6 Calculate the Gross Profit for each product. The gross profit is calculated as the sales minus the COGS for each product. 13 1 Which product generates the largest gross profit? (Select the correct answer from the drop-down list in cell L38.) 14 6 Calculate the Commissions paid for each product type. The commissions paid are calculated as the expected sales for each product type times the commission % from the "Model Inputs" section of the model. 15 6 Enter the fixed costs for the model. Reference the appropriate amount for each product type in the model inputs. 16 Calculate the total operating expenses for each product type. This is the sum of Salary Expenses (which you have not yet calculated), Commissions, and the Fixed Costs for each product type. 17 Calculate the profit for each product type. This is the Gross Profit minus the Total Operating Expenses for each type of product (including Salary Expenses - which you have not yet calculated). 18 6 Calculate the number of employees needed to work the event for each type of product. This is calculated as the Profit Before Arena Fee for each type of product times the Employees (% of Expected Profit) for each product type from the model inputs. Since you can't hire a fraction of an employee, be sure to round your calculations up to the next whole employee. 19 1 Which product will require the fewest employees? (Select the correct answer from the drop-down list in cell L40.) 20 6 Calculate the Salary Expenses for each product type. This is calculated as the number of employees needed for each product type times the Salary (per employee) in the model inputs (employees make the same salary despite the product type). Note that this will create a circular reference in your spreadsheet. You will need to change the options in Excel to appropriately account for the circular reference. 2 Complete the Sales portion of the Profit Summary section of the worksheet by calculating the total Sales for all products from the Gross Profit section of the model. 21 22 2 Complete the Gross Profit portion of the Profit Summary section of the worksheet by total Gross Profit for all products from the Gross Profit section of the model. 23 2 Complete the Total Operating Expenses portion of the Profit Summary section of the worksheet by calculating the Total Operating Expenses for all products from the Operating Expenses section of the model. 23 2 Complete the Total Operating Expenses portion of the Profit Summary section of the worksheet by calculating the Total Operating Expenses for all products from the Operating Expenses section of the model. 24 2 Complete the Profit before Arena Fee portion of the Profit Summary section of the worksheet by calculating the Total Profit before Arena Fee for all products from the Operating Expenses section of the model. 25 2 Complete the Arena Fee portion of the Profit Summary section of the model by referencing the Arena fee from the Model Inputs. 26 2 Calculate the Net Profit portion of the Profit Summary section of the model by subtracting the Arena Fee from the Profit before Arena Fee. 27 20 Complete the data table in the "What-if Analysis" section of the worksheet to compare the Net Profit for various values of the attendance forecast and the Arena Fee. 28 1 Would the event be profitable with a $150,000 arena fee if 15,000 people were in attendance? (Select the correct answer from the drop-down list in cell L42.) Total: 150 16 A B D E F G I J L L M N Input Analysis 100 # of Events 1 2 3 4 5 6 7 Food 60% 10% $10,000.00 0.20% Attendance Merchandise 70% 5% $20,000.00 0.03% Programs Product Type Cost of Goods Sold (COGS) Commission Fixed Costs Employees (% of Expected Profit) Salary (per employee) Arena Fee Food Model Inputs Programs 40% 20% $5,000.00 0.05% $50.00 $100,000.00 Merchandise Statistic Average Standard Deviation 95% Confidence interval Programs Food Merchandise Event Profitability Analysis Product Type Correlation with Attendance Attendance 18000 Gross Profit Programs Food Merchandise Programs Food Merchandise Product Type Sales Cost of Goods Sold (COGS) Gross Profit Product Type Sales Forecast Upper Limit for Sales Lower Limit for Sales Operating Expenses Programs Food Merchandise What-if Analysis Product Type Salary Expenses Commissions Fixed Costs Total Operating Expenses Attendance Forecast 18000 15000 21000 8 9 10 11 12 13 14 15 16 16 17 18 19 13 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 Profit before Arena Fee Arena Fee $50,000.00 $75,000.00 $100,000.00 $125,000.00 $150,000.00 $175,000.00 $200,000.00 Number of Employees Profit Summary Total Questions Question 1: The sales for which product type are most highly correlated with attendance? Item Sales Gross Profit Total Operating Expenses Profit before Arena Fee Arena Fee Net Profit Question 2: How likely is it that food sales would exceed $220,000 if attendance is 18000? Question 3: Which product generates the largest gross profit? Question 4: Which product will require the fewest employees? Question 5: Would the event be profitable with a 150,000 arena fee if 15000 people were in attendance? Event Profit Past Events Pivot Table Questions Bakery + Event Profitability You are the owner of a small business that manages the program, food, and merchandise sales at the Excel Center, where the local professional basketball team plays. In addition to basketball games, the Excel Center hosts dozens of event a year. Your company handles all sales made inside the arena. There are three major products that you sell: programs, food, and merchandise. You have decided to build a spreadsheet model to help you analyze and project the profitability of future events. The 'Event Profit' worksheet contains this model. You have randomly selected sales and attendance data from a number of past events to help build your model. This data can be found on the on the 'Past Events' worksheet. You will complete statistics on this model to obtain appropriate values for the inputs for the model. Complete the tasks to finish the model. Whenever possible, reference cells rather than 'hard-coding' values in the cells of your spreadsheet. Task # Points Task Description 1 In the "Input Analysis" section of the spreadsheet model, calculate the average attendance and sales for each type of product from the past events listed on the "Past Events" worksheet. 2 In the "Input Analysis" section of the spreadsheet model, calculate the sample standard deviation for attendance and sales for each type of product from the past events listed on the "Past Events" worksheet (note for Excel 2007 users: Excel 2007 does not support a specific function to calculate sample standard deviations. Use the STDEV function instead). 3 7.5 In the "Input Analysis" section of the spreadsheet model, calculate the 95% confidence interval for the sales for each type of product (notice that you will not calculate a confidence interval for attendance). Use the number of events (calculated in cell 13) as part of your calculations (Note to Excel 2007 users: the CONFIDENCE.NORM function is not supported in Excel 2007. Use the CONFIDENCE function instead). 7.5 In the "Input Analysis" section of the spreadsheet model, calculate the correlations between the sales of each type of product and event attendance. Use appropriate ranges from the "Past Event" worksheet for your calculations. 5 1 The sales for which product type are most highly correlated with attendance? (Select the correct answer from the drop-down list in cell L32.) 6 16 In the "Input Analysis" section of the spreadsheet model, calculate a sales forecast for each type of product if expected attendance at the future event is 18000 people. Reference cell 113 (the attendance forecast) for your calculations. 7 In the "Input Analysis" section of the spreadsheet model, calculate the upper limit for your sales forecast for each type of product if expected attendance at the future event is 18000 people. Reference the confidence interval you created for your calculation. 8 In the "Input Analysis" section of the spreadsheet model, calculate the lower limit for your sales forecast for each type of product if expected attendance at the future event is 18000 people. Reference the confidence interval you created for your calculation. Hou lilcoheni dodano: London 1000ico liati 9 1 How likely is it that food sales would exceed $220,000 if attendance is 18000? (Selected the correct answer from the drop-down list in cell L35.) 10 6 In the "Event Profitability Analysis" section of the model, enter the projected sales for each product type for this event by referencing your sales forecast from the "Input Analysis" section of the model. 11 Calculate the cost of goods sold (COGS) for each product. The COGS is calculated as the sales for each product type times the COGS percentage from the "Model Inputs" section of the model for that product type. 12 6 Calculate the Gross Profit for each product. The gross profit is calculated as the sales minus the COGS for each product. 13 1 Which product generates the largest gross profit? (Select the correct answer from the drop-down list in cell L38.) 14 6 Calculate the Commissions paid for each product type. The commissions paid are calculated as the expected sales for each product type times the commission % from the "Model Inputs" section of the model. 15 6 Enter the fixed costs for the model. Reference the appropriate amount for each product type in the model inputs. 16 Calculate the total operating expenses for each product type. This is the sum of Salary Expenses (which you have not yet calculated), Commissions, and the Fixed Costs for each product type. 17 Calculate the profit for each product type. This is the Gross Profit minus the Total Operating Expenses for each type of product (including Salary Expenses - which you have not yet calculated). 18 6 Calculate the number of employees needed to work the event for each type of product. This is calculated as the Profit Before Arena Fee for each type of product times the Employees (% of Expected Profit) for each product type from the model inputs. Since you can't hire a fraction of an employee, be sure to round your calculations up to the next whole employee. 19 1 Which product will require the fewest employees? (Select the correct answer from the drop-down list in cell L40.) 20 6 Calculate the Salary Expenses for each product type. This is calculated as the number of employees needed for each product type times the Salary (per employee) in the model inputs (employees make the same salary despite the product type). Note that this will create a circular reference in your spreadsheet. You will need to change the options in Excel to appropriately account for the circular reference. 2 Complete the Sales portion of the Profit Summary section of the worksheet by calculating the total Sales for all products from the Gross Profit section of the model. 21 22 2 Complete the Gross Profit portion of the Profit Summary section of the worksheet by total Gross Profit for all products from the Gross Profit section of the model. 23 2 Complete the Total Operating Expenses portion of the Profit Summary section of the worksheet by calculating the Total Operating Expenses for all products from the Operating Expenses section of the model. 23 2 Complete the Total Operating Expenses portion of the Profit Summary section of the worksheet by calculating the Total Operating Expenses for all products from the Operating Expenses section of the model. 24 2 Complete the Profit before Arena Fee portion of the Profit Summary section of the worksheet by calculating the Total Profit before Arena Fee for all products from the Operating Expenses section of the model. 25 2 Complete the Arena Fee portion of the Profit Summary section of the model by referencing the Arena fee from the Model Inputs. 26 2 Calculate the Net Profit portion of the Profit Summary section of the model by subtracting the Arena Fee from the Profit before Arena Fee. 27 20 Complete the data table in the "What-if Analysis" section of the worksheet to compare the Net Profit for various values of the attendance forecast and the Arena Fee. 28 1 Would the event be profitable with a $150,000 arena fee if 15,000 people were in attendance? (Select the correct answer from the drop-down list in cell L42.) Total: 150