Assignment 3: Using Excel as a DSS

10 points
Deadline: See the syllabus for deadline!

This assignment uses the various features in Excel, you may use either 2003/2007 or 2010/2013 version of Excel.

You should submit your finished Excel file via CI Learn (Blackboard) under [Assignment, Quiz, & Exam] area to submit your completed Excel file.Find Excel Assignment. No need to zip your file.

Download the file A3_Last_First.xlsat

Points may be deducted for late submission. Rename your spreadsheet file as A3_LastName_FirstName.xls where Last is your last name and First is your first name. For me, it should be named as A3_Chen_Minder.xls

There are 4 worksheets in the Excel file for you to work on.

  1. Original Data worksheet (3 points)

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 answer in Cell ranging from D14 to D25, the 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 some of 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

2. Business Graphic worksheet (2 points)

Assumptions and requirements for worksheet “Business Graph”

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 on next page (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.

  1. Goal Seeking worksheet (2 points)

Assumptions and requirements for worksheet “Goal Seeking”(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.Copyyour answer to J19 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. Copy your answer to J20in worksheet “Goal Seek”..

  1. Scenario Question (3 points)

Assumptions and requirements for the scenario analysis of pricing mixes.

  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 togenerate automatically the required scenario summary (i.e., a newly generated worksheet usually called Scenario Summary).
  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.0

The four pricing mixes are listed in the following (Hint: there is a pattern here!!! This will help you with Part II of this question):

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 in the Scenario Summary worksheet werealtered to hide their true values, your answers should have different values).

  • Make sure the Results Cells include TotalGrossProfit (cell H14) and Totalrevenue (cell H16); Name H14 as TotalGrossProfit and H16 as 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 properly.
  • 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”. You may need toscroll down a little bit to answer questions in Part II. These two questions are provided in the cells and is also given below (1 point)

a.How can you determine the most profitable product mix scenario manually – just by observing the pattern in the pricing mixes and other related numbers related to each product (without using Excel or a calculator)?

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

Write in your answer in A22 and A 24

You are done. Save your Excel file and submit it online.

1