Assignment 3: Using Excel as a DSS 10 pointsDeadline: Nov. 22, Fall 2011This assignment uses the various features in Excel, you may use either 2003 and 2007 version of Excel, 2007 is the preferred choice and you can check out a laptop computer from the library to work on the assignment. Download the file A3_NN_Last_First.xls at http://faculty.csuci.edu/minder.chen/mis310/A3_NN_Last_First2010Spring.xls Enter all your answers in the Excel spreadsheet and submit the spreadsheet file via the Digital Drop Box in blackboard. Points may be deducted for late submission. Rename your spreadsheet file as A3_NN_Last_First.xls where NN is 01, or02. Last is your last name and First is your first name. For me, it should be named as A3_01_Chen_Minder.xls The first problem set describes a retail store scenario. The total revenue comes from the sales of a mixture of 3 different products sold in the store - called Products A, B and C. We need to create a forecast for 2009 based on the number of units sold for these products in 2008. Yellow boxed cells are where you should enter your answers. The blue color areas are the grading areas. Once I finish the grading of the assignment, the Excel file will be dropped back to your digital drop box and your final grade will be shown in Cell J3 at the “Original Data” worksheet. Please enter your name section number, and email in the cells provided in the “Original Data” worksheet. Your name: Your name here! Section: 1Email address: [email protected] and requirements for worksheet “ Original Data ” (3 Points)All your formula used in your calculation should use meaningful cell names such as =+TotalRevenue – TotalCOGS , please don’t use cell reference such as =+E13-E19 unless it is necessary (such as formula for calculating each products revenues and COGS where you need to copy and paste formula using relative addresses). Yes, you need to name these cells yourself first. 1. The total number of units sold in 2008 for these three products are listed as the following: 2008Unit SoldProduct A 3000Product B 4500Product C 2300Please fill out all the yellow boxed cells such as the following according to the requirements below: 2. The estimated number of unit to be sold for all three products in 2009 over 2008 number should be calculated based on the percentage of increase as specified in Cell D3 which is currently set at 10%. Name the D3 cell as increasePCT. 3. All the calculation required are based on the 2009 forecast target of units to be sold for Product A, B, and C.24. Calculate the revenues and costs of goods sold (COGS) for each products based on 2009 data. 5. TotalRevenue is the sum of the revenues for all three products for 2009. 6. TotalCOGS is the sum of the COGS for all three products for 2009. 7. Salary is 15% of TotalRevenue (the sum of the revenues for all three products for 2009)8. GrossProfit = TotalRevenue – TotalCOGS9. Advertising is 2% of TotalRevenue 10. Miscellaneous expenses are 1% of TotalRevenue 11. OperatingExpense = Salary + Advertising + Miscellaneous expenses12. EarningBeforeTax = GrossProfit - OperatingExpense 13. Calculate the Taxes based on tax rate of 30% against the EarningBeforeTax. 14. Net Profit = EarningBeforeTax- Taxes Assumptions and requirements for worksheet “ Business Graph ” (2 Points) In the “Business Graph” worksheet, depict a 3-D Pie Chart showing the Percentage of each product's contribution to the Total Profitability (Based on Gross Profit, Not revenue). You need to calculate the individual profits generated by selling product A, B, and C based on the data in the “original data” worksheet first and then places them in the area with Yellow background color. The Business Graph shall look similar to thefollowing one (The percentages have been altered to hide their true values, your answer may have different percentages) with aTitle, percentages for each products showing in the pie chart, and the legend. (2 points) 3Assumptions and requirements for worksheet “ Goal Seek ” (2 points)Use “Goal Seeking” worksheet for the following Goal Seeking What-if Analysis. If you want the gross profit to be $150000 when the Target Units Sold for products B and C are given (4500 and 2300 units each) and they cannot be changed. Use Goal Seeking feature to find out how many units of Product A you need to sell to reach the $150,000 Total Gross Profit goal. Put your answer in J12 in worksheet “ Goal Seek ”. Use Goal Seeking feature to find out how many units of Product A you need to sell to reach the $200,000 Total Gross Profit goal. Put your answer in J13 in worksheet “ Goal Seek ”.. 4Assumptions and requirements for the scenario analysis of pricing mixes. (2 points)1. Open the Worksheet “Scenario-Question”. In Part I, create a scenario using the data and formula given in the gray area in Scenario Question worksheet. 2. Keep the Scenario setting after you have created it successfully and you have to use the scenario manager to createthe required scenario summary (i.e., a separate worksheet) for you. 3. You will create 4 different scenarios by changing the product pricing mix in order to determine their impacts to Total Gross Profit and Total Revenue. The first scenario is the original Pricing Mix with no price change. The Second Scenario is to raise the price of Product A by $5.00The Third Scenario is to raise the price of Product B by $5.00The Fourth Scenario is to raise the price of Product C by $5.00The four pricing mixes are listed in the following: Product Name Pricing Mix 1 Pricing Mix 2 Pricing Mix 3 Pricing Mix 4Product A$50.00 $55.00 $50.00 $50.00Product B$45.00 $45.00 $50.00 $45.00Product C$32.00 $32.00 $32.00 $37.00 Working with the data given in the gray area in Scenario Question worksheet. Create a Scenario summary report as a separate worksheet such as the one below (The actual result cells values were altered to hide their true values, your answers should have different values). (2 points)5- Make sure the Results Cells include TotalGrossProfit and Totalrevenue- Use meaningful labels for the Changing Cells and Results Cells (for example using the label "Product_A_Price" instead of $F$10). The Best Way to do this is to name all the Changing Cells and Results Cells. - Circle the product mix scenario that results in the highest TotalGrossProfit and also insert an Oval Callout