Description
Direct Marketing
Inputs
Number of Ads
Click Rate
10000
5.00%
Parameters
Design Fee
Cost Per Ad
Total Clicks
Profit Per Click
Gross Profit
Net Profit
$
$
$
2,000.00
2.25
500
12.50
Direct Marketing
Inputs
Number of Ads
Click Rate
Gross Profit Net Profit
10000
5.00%
Net Profit
2.00%
2.50%
3.00%
$7,666
$9,582
$11,498
$4,286
$5,857
$7,429
2.00%
2.50%
3.00%
Parameters
3.50%
$13,415
$9,000
3.50%
Design Fee $ 2,000.00
Cost Per Ad $
2.25
Total Clicks
500
Profit Per Click
$
12.50
Gross Profit $ 6,250.00
Net Profit $ 3,125.00
4.00%
4.50%
5.00%
5.50%
6.00%
6.50%
$15,331
$17,247
$19,164
$21,080
$22,997
$24,913
$10,571
$12,143
$13,714
$15,286
$16,857
$18,429
4.00%
4.50%
5.00%
5.50%
6.00%
6.50%
10000
15000
20000
25000
30000
35000
40000
$50
$563
$1,075
$1,075
$1,844
$2,613
$2,100
$3,125
$4,150
$3,125
$4,406
$5,688
$4,150
$5,688
$7,225
$5,175
$6,969
$8,763
$6,200
$8,250
$10,300
$1,588
$3,381
$5,175
$6,969
$8,763
$10,556
$12,350
$2,100
$2,613
$3,125
$3,638
$4,150
$4,663
$4,150
$4,919
$5,688
$6,456
$7,225
$7,994
$6,200
$7,225
$8,250
$9,275
$10,300
$11,325
$8,250
$9,531
$10,813
$12,094
$13,375
$14,656
$10,300
$11,838
$13,375
$14,913
$16,450
$17,988
$12,350
$14,144
$15,938
$17,731
$19,525
$21,319
$14,400
$16,450
$18,500
$20,550
$22,600
$24,650
Grader – Instructions
Excel 2019 Project
Exp19_Excel_Ch06_Cap_High_West_Fashions
Project Description:
You are the digital marketing director for High West Fashions, a regional clothing company that specializes in custom t-shirts.
Your company has decided to launch an online advertising campaign that gives customers the ability to purchase heavily
discounted products. You have the task of determining the optimal amount of advertising to purchase in order to maximize
profit and most effectively utilize resources.
Steps to Perform:
Step
Points
Possible
Instructions
1
Start Excel. Download and open the file named
Exp19_Excel_Ch06_Cap_DirectMarketing.xlsx. Grader has automatically added your last
name to the beginning of the filename.
0
2
On the Direct Marketing worksheet, create appropriate range names for Design_Fee (cell B8),
Cost_Per_Ad (cell B9), Total_Clicks (cell B10), Profit_Per_Click (B11), and Gross_Profit
(cell B12).
5
3
Edit the existing name range Design_Fee to Design_Fee2021 to reflect the current year.
4
4
Use the newly created range names to create a formula to calculate Gross Profit (cell B12)
and Net Profit (cell B13).
6
5
Create a new worksheet named Range Names, paste the newly created range name
information in cell A1, and resize the columns as needed for proper display.
5
Mac users, use the Insert menu to insert a new worksheet and paste the range names.
6
On the Direct Marketing worksheet, use Goal Seek to determine the optimal click rate in order
to earn a $5,000 net profit.
6
7
Starting in cell E4. Complete the series of substitution values ranging from 2% to 6.5% at
increments of .50% vertically down column E.
5
8
Enter references to the Gross Profit and Net Profit in the correct location for a one-variable
data table.
3
9
Complete the one-variable data table, and then format the results with Accounting Number
Format with two decimal places.
6
10
Apply custom number formats to display Gross Profit in cell F3 and Net Profit in cell G3.
4
11
Copy the response rate substitution values from the one-variable data table, and then paste
the values starting in cell I4.
4
12
Type 10000 in cell J3. Complete the series of substitution values from 10000 to 40000 at 5000
increments.
3
13
Enter the reference to net profit formula in the correct location for a two-variable data table.
4
14
Complete the two-variable data table and format the results with Accounting Number Format
with two decimal places.
7
15
Apply a custom number format to make the formula reference appear as Net Profit.
3
Created On: 10/21/2021
1
Exp19_Excel_Ch06_Cap – High West Fashions 1.7
Grader – Instructions
Step
Excel 2019 Project
Points
Possible
Instructions
16
Make the Direct Marketing 2 worksheet active. Create a scenario named Best Case, using
Number of Ads and Click Rate. Enter these values for the scenario: 40000, and 6.5%.
4
17
Create a second scenario named Worst Case, using the same changing cells. Enter these
values for the scenario: 10000, and 1%.
4
18
Create a third scenario named Most Likely, using the same changing cells. Enter these
values for the scenario: 10000, and 6.83%.
4
19
Generate a scenario summary report using Gross Profit and Net Income.
4
20
Return to the Direct Marketing 2 worksheet. Load the Solver add-in if it is not already loaded.
Launch Solver and set the objective to calculate a net profit of $20,000.
4
21
Use Number of Ads and Click Rate (B4:B5) as changing variable cells.
4
22
Set a constraint to ensure Number of Ads purchased is less than or equal to 40,000.
2
23
Set a constraint to ensure Click Rate is less than or equal to 7%. (Mac users should enter the
value in decimal form. Example .07)
2
24
Solve the problem. Generate the Answer Report.
3
25
Create a footer on all worksheets with your name on the left side, the sheet name code in the
center, and the file name code on the right side.
4
26
Save and close Exp19_Excel_Ch06_CAP_DirectMarketing.xlsx. Exit Excel. Submit the file as
directed.
0
Total Points
Created On: 10/21/2021
2
100
Exp19_Excel_Ch06_Cap – High West Fashions 1.7
Delta Paint
Input
Units Sold
Unit Selling Price
Employee Hourly Wage
Maximum Capabilty per week
$
$
Limitations
Raw Materials in Units
Required raw materials per unit
Required Labor Hours Per Gallon
Labor Hours available
Expenses
Raw Materials Consumed
Labor Hours Consumed
Total Production Cost
100
30.00
15.00
200
15000
3
0.25
2,000
$
300
25
375.00
$
3,000
Income
Gross Profit
Net Profit
One-Variable Data Table: Production
Two-Variable Data Table: Production and Manufacturing Time
Grader – Instructions
Excel 2019 Project
Exp19_Excel_Ch06_CapAssessment_Delta_Paint
Project Description:
You are the production manager for Delta Paint, a regional manufacturing company that specializes in customized paints. Your
company sells paint by the gallon, and you have the task of forecasting the best production blends to maximize profit and most
effectively utilize resources.
Steps to Perform:
Step
Points
Possible
Instructions
1
Download and open the file named Exp19_Excel_Ch06_Cap_DeltaPaint.xlsx. Grader has
automatically added your last name to the beginning of the filename.
0
2
Create appropriate range names for Total Production Cost (cell B18) and Gross Profit (cell
B21) by selection, using the values in the left column.
3
3
Edit the existing name range Employee_Hourly_Wage to Hourly_Wages2021.
Note, Mac users, in the Define Name dialog box, add the new named range, and delete the
original one.
3
4
Use the newly created range names to create a formula to calculate Net Profit (in cell B22).
Net Profit = Gross Profit – Total Production Cost.
4
5
Create a new worksheet labeled Range Names, paste the newly created range name
information in cell A1, and resize the columns as needed for proper display. Ensure that the
data is sorted by range name, column A.
5
6
On the Forecast sheet, start in cell E3. Complete the series of substitution values ranging from
10 to 200 at increments of 10 gallons vertically down column E.
2
7
Enter references to the Total_Production_Cost, Gross_Profit, and Net Profit cells in the
correct locations (F2, G2, and H2 respectively) for a one-variable data table. Use range names
where indicated.
3
8
Complete the one-variable data table in the range E2:H22 using cell B4 as the column input
cell, and then format the results with Accounting Number Format with two decimal places.
5
9
Apply custom number formats to make the formula references appear as descriptive column
headings. In F2, Total Costs; in G2, Gross Profit, in H2, Net Profit. Bold and center the
headings and substitution values.
3
10
Copy the number of gallons produced substitution values from the one-variable data table, and
then paste the values starting in cell E26.
4
11
Type $15 in cell F25. Complete the series of substitution values from $15 to $40 at $5
increments.
4
12
Enter the reference to the net profit formula in the correct location for a two-variable data table.
4
13
Complete the two-variable data table in the range E25:K45. Use cell B6 as the Row input cell
and B4 as the Column input cell. Format the results with Accounting Number Format with two
decimal places.
10
14
Apply a custom number format to make the formula reference appear as a descriptive column
heading Wages. Bold and center the headings and substitution values where necessary.
3
Created On: 11/08/2021
1
Exp19_Excel_CH06_CAPAssessment – Delta Paint 1.3
Grader – Instructions
Step
Excel 2019 Project
Points
Possible
Instructions
15
Create a scenario named Best Case, using Units Sold, Unit Selling Price, and Employee
Hourly Wage (use cell references). Enter these values for the scenario: 200, 30, and 15.
4
16
Create a second scenario named Worst Case, using the same changing cells. Enter these
values for the scenario: 100, 25, and 20.
4
17
Create a third scenario named Most Likely, using the same changing cells. Enter these
values for the scenario: 150, 25, and 15.
4
18
Generate a scenario summary report using the cell references for Total Production Cost and
Net Profit.
5
19
Load the Solver add-in if it is not already loaded. Set the objective to calculate the highest Net
Profit possible.
5
20
Use the units sold as changing variable cells.
4
21
Use the Limitations section of the spreadsheet model to set a constraint for raw materials (The
raw materials consumed must be less than or equal to the raw materials available). Use cell
references to set constraints.
4
22
Set a constraint for labor hours. Use cell references to set constraints.
4
23
Set a constraint for maximum production capability. Units sold (B4) must be less than or equal
to maximum capability per week (B7). Use cell references to set constraints.
4
24
Solve the problem. Generate the Answer Report and Keep Solver Solution.
5
25
Create a footer on all four worksheets with your name on the left side, the sheet name code in
the center, and the file name code on the right side.
4
26
Save and close Exp19_Excel_Ch06_Cap_DeltaPaint.xlsx. Exit Excel. Submit the file as
directed.
0
Total Points
Created On: 11/08/2021
2
100
Exp19_Excel_CH06_CAPAssessment – Delta Paint 1.3
Purchase answer to see full
attachment
