Excel Expert Requested

Hello please review the attached and ONLY respond if you can complete.

CASE PROBLEM 3

Case Problem 3

Data File needed for this Case Problem: Cookin.xlsx

Cookin’ Good Cookin’ Good sells specialized home cooking products. The company

employs individuals to organize “Cookin’ Good Parties” at which the company’s products

are sold. Cleo Benard is responsible for entering sales data from various Cookin’ Good

Parties. She wants to design an Excel workbook to act as a data entry form. She has

already created the workbook, but she needs your help with setting up data validation

rules, creating a table lookup, and writing the macros to enter the data. Complete the

following:

1 . Open the Cookin’ workbook located in the Excel8\Case3 folder included with your

Data Files, and then save the workbook as Cookin’ Good. In the Documentation

sheet, enter your name and the date.

2. Go to the Sales Form worksheet. Create appropriate defined names for each cell in

the range C3:C8. Assign the name ProductInfo to the range E4:G1 5.

3. In the Sales Form worksheet, create the following validation rules:

a. The criteria allows only one of five regions (represented by the numbers 1 , 2,

3, 4, and 5) to be entered in cell C3. Enter an appropriate input message and

error alert.

b. The criteria provides the list of 1 2 products (found in range E4:E1 5) in cell C4.

Enter an appropriate input message and error alert.

c. The criteria allows only whole numbers greater than 0 to be entered as the number of units sold in cell C7. Enter an appropriate input message and error alert.

4. In cells C5 and C6, enter a VLOOKUP function that automatically enters the product name and price into the sales form when the ProductID is entered. (Hint: Cells

should be blank if an error value appears in a cell.)

5. In cell C8, enter a formula that automatically calculates the total sale for the order,

which is determined by the number of units sold multiplied by the price of the product. Use the IFERROR function to display a blank cell if an error value occurs during

the calculation of Total Sale.

6. Prevent users from entering data into any cell in the Sales Form worksheet other than

cells C3, C4, and C7, and then protect the Sales Form worksheet.

7. Test the data entry form by entering the following new record: Region = 1, Product

ID = CW, Units Sold = 8.

8. Save the workbook, and then create a macro named AddData with the shortcut key

Ctrl+d that performs the following tasks:

a. In the Sales Form worksheet, copy the values in the range C3:C8. (Hint: You’ll

paste later in the macro.)

b. Switch from the Sales Form worksheet to the Sales Record worksheet. Click

cell A1 .

c. Turn on Relative References.

d. Use the End+ keys to locate the last used row in the first column. Then, press

the  key to move to the next row in the first column.

e. Turn off Relative References.

f. Paste the copied values from Step A into the blank row. (Hint: Use the Paste button arrow, and then click Paste Transpose button.)

g. Switch to the Sales Table worksheet.

h. Click inside the PivotTable and refresh the contents of the PivotTable to include

the new data.

i. Switch to the Sales Form worksheet and clear the values in cells C3, C4, and C7.

j. Make C3 the active cell.

k. Stop recording the macro.

9. Create a button in the range C1 1 :C1 2 on the Sales Form worksheet and assign the

AddData macro to the button. Change the button label to Transfer Sales Data.

1 0. Test the data entry form and AddData macro by entering the following new records:

Region Product ID Units Sold

3 HR 4

4 OEG 2

1 1 . Create a macro named ViewTable with the Ctrl+t shortcut key that displays the con-

tents of the Sales Table worksheet.

1 2. Create a macro named ViewChart with the Ctrl+c shortcut key that displays the

Sales Chart worksheet.

1 3. Create a macro named ViewForm with the Ctrl+f shortcut key that displays the Sales

Form worksheet. Test each macro using its shortcut keys.

1 4. In the Documentation worksheet, create three macro buttons below row 1 3 to view

the Sales Table worksheet (Step 1 1 ), the Sales Chart worksheet (Step 1 2), and the

Sales Form worksheet (Step 1 3). Assign the appropriate macro to each button, and

change the labels on the buttons to be

more descriptive.

1 5. The Sales Table worksheet displays the total product sales in each region. Change the

PivotTable to show the values in the cells as percentages of the Column Total. (Hint:

On the Options tab, in the Calculations group, click the Show Values As button,

and then click % of Column Total. You can return the original value by clicking No

calculation.)

1 6. Create two macros. The first displays the PivotTable as percentages of column

totals (Step 1 5). Name this macro ShowAsPercent. The second macro displays

the PivotTable as numbers (values shown as No Calculation). Name this macro

ShowAsNumber.

1 7. Edit the ShowAsPercent macro to display the results with a percent style by adding

the following three commands immediately before the End Sub statement:

Range( “ C5: H17 ” ) . Select

Selection. Style = “ Percent”

Range( “ A1” ) . Select

1 8. Create two macro buttons below the PivotTable in the Sales Table worksheet to run

each macro. Assign a macro to each button, and then change the button labels to be

more descriptive.

1 9. Save the workbook as CG with Macros, and then close it. Submit the finished work

2

>Documentation

Good

Cookin

Created By:

Date Created:

Purpose:

To record sales information for Cookin Good products.

Sales Form

Product ID

.9

Product Name

Product Price

9.95

19.95

29.95

9.95

9.95

159.95

Enter New Sales Information Below

Product Information

Region

Product ID

Product Name

Product Price

HPP

Homeware Pizza Pan

1

9

5

SPP

Stone Pizza Pan

2

9.95

HCD

Homeware Casserole Dish

19.95

Units Sold

HCP

Homeware Cookie Pan

Total Sale

CW

China Wok

HEW

Homeware Electric Wok

29.95

WCM

Wilson Coffee Machine

HBM

Homeware Bread Machine

4

HBMD

Homeware Bread Machine -Deluxe

8

HR

Homeware Rotisserie

119.95

OGG

Olson Gas Grill

159.95

OEG

Olson Electric Grill

Sales Record

Region Product ID Product Name Product Price Units Sold Total Sale

HEW Homeware Electric Wok 29.95 3

1 SPP Olson Electric Grill 159.95 1 159.95
1 SPP Stone Pizza Pan 29.95 5

5

4 HCD Homeware Casserole Dish 19.95 3

1 HBMD Homeware Bread Machine -Deluxe

5 HEW Homeware Electric Wok 29.95

1 HEW Homeware Electric Wok 29.95 9

5 HBMD Homeware Bread Machine -Deluxe 89.95 6

4 HEW Homeware Electric Wok 29.95 3 89.85
4 OGG Olson Gas Grill 159.95 2

1 HCD Homeware Casserole Dish 19.95 3 59.85
4 HEW Homeware Electric Wok 29.95 6 179.70
4 CW China Wok 19.95 5

4 SPP Stone Pizza Pan 29.95 2

4 HBM Homeware Bread Machine

4

2 OEG Olson Electric Grill 159.95 3

2 SPP Stone Pizza Pan 29.95 6 179.70
4 HBM Homeware Bread Machine 49.95 10

5 CW China Wok 19.95 8

3 OGG Olson Gas Grill 159.95 3 479.85
5 HPP Homeware Pizza Pan 19.95 10

2 HEW Homeware Electric Wok 29.95 8

3 SPP Stone Pizza Pan 29.95 4

4 HBMD Homeware Bread Machine -Deluxe 89.95 5

5 OGG Olson Gas Grill 159.95 8

3 HBMD Homeware Bread Machine -Deluxe 89.95 6 539.70
2 HEW Homeware Electric Wok 29.95 9 269.55
2 OGG Olson Gas Grill 159.95 7

5 OEG Olson Electric Grill 159.95 1 159.95
4 HCP Homeware Cookie Pan 9.95 4

3 WCM Wilson Coffee Machine 29.95 1 29.95
4 OGG Olson Gas Grill 159.95 4

5 OGG Olson Gas Grill 159.95 10

4 WCM Wilson Coffee Machine 29.95 1 29.95
2 HBMD Homeware Bread Machine -Deluxe 89.95 9

1 CW China Wok 19.95 1 19.95
4 WCM Wilson Coffee Machine 29.95 9 269.55
4 HCP Homeware Cookie Pan 9.95 2 19.90
2 HCD Homeware Casserole Dish 19.95 9

3 SPP Stone Pizza Pan 29.95 2 59.90
1 CW China Wok 19.95 2

4 HCD Homeware Casserole Dish 19.95 9 179.55
1 HBMD Homeware Bread Machine -Deluxe 89.95 1 89.95
2 CW China Wok 19.95 9 179.55
4 HBMD Homeware Bread Machine -Deluxe 89.95 8

1 HR Homeware Rotisserie 119.95 7

4 HBM Homeware Bread Machine 49.95 9

1 HR Homeware Rotisserie 119.95 3

4 WCM Wilson Coffee Machine 29.95 4 119.80
2 HR Homeware Rotisserie 119.95 1 119.95
5 WCM Wilson Coffee Machine 29.95 7

2 HBM Homeware Bread Machine 49.95 1 49.95
5 HPP Homeware Pizza Pan 19.95 3 59.85
5 HCP Homeware Cookie Pan 9.95 1 9.95
3 OEG Olson Electric Grill 159.95 4 639.80
3 HEW Homeware Electric Wok 29.95 6 179.70
3 OGG Olson Gas Grill 159.95 9

1 HBM Homeware Bread Machine 49.95 10 499.50
3 HR Homeware Rotisserie 119.95 3 359.85

5 HBMD Homeware Bread Machine -Deluxe 89.95 6 539.70

2 HCP Homeware Cookie Pan 9.95 4 39.80
2 HCD Homeware Casserole Dish 19.95 2 39.90

4 HEW Homeware Electric Wok 29.95 3 89.85

5 WCM Wilson Coffee Machine 29.95 6 179.70
1 OEG Olson Electric Grill 159.95 2

3 WCM Wilson Coffee Machine 29.95 7 209.65
5 HR Homeware Rotisserie 119.95 1 119.95
1 OGG Olson Gas Grill 159.95 6

																					3			89.85
149.				7
		59.85
						89.95					10	899.50
								6					179.70
		269.55
		539.70
3	19.90
99.75
	59.90
					49.95	199.80
	479.85
	499.50
159.60
199.50
239.60
	119.80
449.75
1279.60
1119.65
	39.80
	639.80
1599.50
809.55
		179.55
	39.90
719.60
839.65
449.55
	359.85
	209.65
1439.55
319.90
959.70

Sales Table

Region

Product ID 1 2 3 4 5

CW

HBM

HBMD

HCD $ 59.85

HCP

HEW

$ 269.55

HPP

$ 259.35

HR

$ 119.95

OEG

OGG

$ 959.70

SPP

$ 179.70 $ 179.70

WCM

Grand Total

Total Sales
	Grand Total
	$ 59.85	$ 179.55	$ 99.75	$ 159.60	$ 498.75
$ 499.50	$ 49.95	$ 1,148.85	$ 1,698.30
$ 989.45	$ 809.55	$ 539.70	$ 1,169.35	$ 1,079.40	$ 4,587.45
$ 219.45	$ 239.40	$ 518.70
$ 39.80	$ 59.70	$ 9.95	$ 109.45
	$ 269.55	$ 509.15	$ 359.40			$ 179.70	$ 1,587.35
	$ 259.35
$ 1,199.50		$ 119.95	$ 359.85	$ 1,799.25
$ 319.90	$ 479.85	$ 639.80	$ 159.95	$ 1,599.50
	$ 959.70	$ 1,119.65	$ 1,919.40	$ 2,879.10	$ 7,837.55
$ 309.70	$ 59.90	$ 729.00
$ 239.60	$ 419.30	$ 389.35	$ 1,048.25
$ 4,667.00	$ 3,706.60	$ 4,147.60	$ 4,515.35	$ 5,236.35	$ 22,272.90

Sales Chart

1 CW HBM HBMD HCD HCP HEW HPP HR OGG SPP WCM OEG 59.85 499.5 989.44999999999982 59.85 0 269.55 0 1199.5 959.7 309.7 0 319.89999999999986 2 CW HBM HBMD HCD HCP HEW HPP HR OGG SPP WCM OEG 179.55 49.95 809.55 219.45000000000005 39.800000000000004 509.15000000000009 0 119.95 1119.6499999999999 179.7 0 479.85 3 CW HBM HBMD HCD HCP HEW HPP HR OGG SPP WCM OEG 0 0 539.70000000000005 0 0 269.55 0 359.85 1919.4 179.7 239.6 639.79999999999995 4 CW HBM HBMD HCD HCP HEW HPP HR OGG SPP WCM OEG 99.75 1148.8499999999999 1169.3499999999999 239.4 59.7 359.4 0 0 959.7 59.9 419.3 0 5 CW HBM HBMD HCD HCP HEW HPP HR OGG SPP WCM OEG 159.6 0 1079.4000000000001 0 9.9500000000000028 179.7 259.35000000000002 119.95 2879.1 0 389.35 159.94999999999999