Assignment 3: Using Excel as a DSS

10 points
Deadline: April 21, 2015; 5:00 pm

This 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.

You should submit your finished Excel file via CI Learn (Blackboard) under Content  Assignment 3. No need to zip your file.

Download the file A3_NN_Last_First.xlsat

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_Last_First.xls where Last is your last name and First is your first name. For me, it should be named as A3_Chen_Minder.xls

The first problem set describes a retail store scenario. The total revenue comes from the sales of a mixture of 3 differentproducts 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 areasare 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: / 1
Email address: /

Assumptions 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 COGSwhere 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:

2008
Unit Sold
Product A / 3000
Product B / 4500
Product C / 2300

Please fill out all the yellow boxed cells such as the following according to the requirements below:

  1. The estimated number of unit to be sold for all three products in 2009over 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.
  2. All the calculation required are based on the 2009 forecast target of units to be sold for Product A, B, and C.
  3. Calculate the revenues and costs of goods sold (COGS) for each products based on 2009 data.
  4. TotalRevenueis the sum of the revenues for all three products for 2009.
  5. TotalCOGSis the sum of the COGS for all three products for 2009.
  6. Salary is 15% of TotalRevenue(the sum of the revenues for all three products for 2009)
  7. GrossProfit = TotalRevenue – TotalCOGS
  8. Advertising is 4% of TotalRevenue
  9. Miscellaneous expenses are 1% of TotalRevenue
  10. OperatingExpense = Salary + Advertising + Miscellaneous expenses
  11. EarningBeforeTax = GrossProfit - OperatingExpense
  12. Calculate the Taxes based on tax rate of 30% against the EarningBeforeTax.
  13. 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 TotalProfitability (Based on Gross Profit, Not revenue).

You need to calculate the individualprofits generated by selling product A, B, and C based on the data in the “original data” worksheet first and then placesthem in the area with Yellow background color. The Business Graph shall look similar to the following one (The percentages have been altered to hide their true values, your answer may have different percentages) with a Title, percentages for each products showing in the pie chart, and the legend. (2 points)

Assumptions 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 J13in worksheet “Goal Seek”..

Assumptions 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 impactsto 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.00
The Third Scenario is to raise the price of Product B by $5.00
The Fourth Scenario is to raise the price of Product C by $5.00

The four pricing mixes are listed in the following:

Product Name / Pricing Mix 1 / PricingMix 2 / PricingMix 3 / PricingMix 4
Product A / $50.00 / $55.00 / $50.00 / $50.00
Product B / $45.00 / $45.00 / $50.00 / $45.00
Product 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 reportas 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)

  • 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 shape with text inside stating that the circled scenario is “The Most Profitable Scenario”. [Use Insert > Shape as the following screen snapshot]

Open the Worksheet “Scenario-Question” and in Part II. answer the following two questions in the cells provided (1 point)

a.How can you determine the most profitable product mix scenario manually (without using Excel or a calculator)?

b.Discuss one of the major flaws in the underlying hidden assumption used in this scenario analysis.

1