Tech Project #2

(Read all questions and instructions carefully.)

Set up:

• Download the Assignment_2Data.xlsx file from Blackboard: Tech Project Tab Tech Project #2

• Rename the file: LastNameA2.xlsx (Example: JungA2.xlsx)

  Part 1. Scenario Manager (20 points)

  Jitters Coffee is a small company that sells three major coffee beans: Regular, Decaf, and Hazelnut. You are hired by Jitters Coffee as an assistant to the Sales Vice President and you have been asked to use your analytical skills and expertise with Excel to create quantitative and qualitative assessments of sales data. Your job is to create business scenario analysis to help the VP make business decisions.

  T1.(Function) In the T1 part1 worksheet, type in the formulas to calculate the cost per unit of each coffee bean, sold quantity of each coffee bean, total sales of each coffee bean, total cost of each coffee bean, total profit of each coffee bean, the grand total of advertising cost, the grand total of sold quantity, the grand total sales, the grand total cost, and the grand total profit. All the numbers must be calculated by using the following formulas and remember that always use cell reference instead of directly typing in the numbers for all formulas: (5 pts)

  Cost Per Unit = Retail Price Per Unit (1- Gross Margin Percentage). Sold Quantity=Advertising Cost Conversion Rate. (You must use a proper cell reference in

  calculating sold quantity). Total Sales = Retail Price Per Unit Sold Quantity Total Cost = (Cost Per Unit Sold Quantity) + Advertising Cost Total Profit = Total Sales - Total Cost In the Grand Total row, display the sum of advertising cost, sum of sold quantity, sum of

  total sales, sum of total cost, and sum of total profit. Make sure that you format cells that displays dollar amount in the currency format. Please make sure

  that the conversion rate is 12%.

  T2.(Scenario Manager) The sales department is considering changing coffee bean prices. This will affect the gross margin percentage as well. Create a copy of the T1_part1 worksheet and name it T2.Scenario. In the T2_Scenario worksheet, use Scenario Manager to create a scenario summary report that displays the grand total sales, the grand total cost, and the grand total profit under each of the following scenarios as shown below. Ascenario summary that is not created by Scenario Manager will get a grade of zero. (15 pts)

  Regular Decaf Hazelnut Gross Margin Percentage

Coffee Beans

Retail Price 1

Retail Price 2

Retail Price 3

Retail Price 4

40 50 40 40 30 30 15 30 25 25 25 15 50% 35% 40% 40%

1

BUAD 6800

Make sure that you change the name of the scenario summary worksheet to be T2_Scenario_Summary. The labels for Changing Cells and Results Cells in the scenario summary should be informative. Your result should look like below:

Part 2. Solver (30 points)

Scenario: Harry and Tyler operate Jitters, a drive-through coffee shop in Fairfax. Interested in entering the mail-order coffee business, they print a catalog of their products and carry out a direct mailing campaign to launch the new business.

The cost of printing the catalog is $20,000, plus $0.10 per catalog. The cost of mailing each catalog is $0.15. In addition, the company will include direct reply envelopes in the mailings. This can be used by customers to send in their orders. Each direct mail envelope that is used by a respondent costs Jitters $0.20. The average size of a customer order is expected to be $40, and the companys variable cost per order averages around 80% of the orders value. Assume that the response rate is 7%.

You are an intern with Jitters and have been asked to develop a decision support system (DSS) to model the direct mailing campaign. The Excel model should allow the managers to play with different numbers and understand how the various components of the proposed project will impact its success.

T3.(Function/Format) Create an Excel data model in T3.Solver Data worksheet. (5 pts). Enter cell names and cell values from the problem.

Type in the formulas for the model. Format the cell values correctly. Percentage should be in percentage form, numbers as numbers, and dollars as dollars.

Coffee Beans	Retail Price Per Unit	Cost Per Unit	Advertising Cost	Sold Quantity	Total Sales
Regular	40.00
Decaf	30.00
Hazelnut	25.00
		Grand Total
Gross Margin Percentage
Conversion Rate	12%

Jitters direct mail model
Mailing inputs			Model of responses
Fixed cost of printing			Response rate (trial value)
Variable costs			Number of responses
Printing
Mailing			Model of revenue, costs, and profit
Number mailed			Revenue
			Costs
Order inputs			Fixed
Average order			Variable from mailing
Variable cost (% of order)			Variable from orders
Variable cost of envelopes			Total cost
			Profit