2 basic excel projects

Hello,I have atteched the excel files that you should use and the instructions of both of them.Thanks
courtyardmedical_08.xlsx

ex2016_guidedproject_8_3_instructions.pdf

Don't use plagiarized sources. Get Your Custom Essay on
2 basic excel projects
Just from $13/Page
Order Essay

ex2016_independentproject_8_5_instructions.pdf

placerhills_08.xlsx

Unformatted Attachment Preview

Dental Services
Patient ID
Insurance
Service Code
Maximum
Benefit
Billed
OP0001
CompDent
D0120
$125.00
$135.00
OP0002
SIP
D1110
$185.00
$135.00
OP0003
CompDent
D2751
$235.00
$250.00
OP0004
Westville
D2751
$135.00
$100.00
OP0005
CompDent
D1110
$245.00
$185.00
OP0006
SIP
D1110
$275.00
$100.00
OP0007
SIP
D1110
$325.00
$425.00
OP0008
Westville
D0120
$175.00
$125.00
OP0009
BlueDent
D0120
$195.00
$145.00
OP0010
BlueDent
D1110
$200.00
$225.00
OP0011
BlueDent
D1110
$225.00
$235.00
OP0012
SIP
D1110
$200.00
$185.00
OP0013
CompDent
D2751
$475.00
$325.00
OP0014
SIP
D1110
$235.00
$235.00
OP0015
SIP
D1110
$235.00
$245.00
OP0016
BlueDent
D1110
$225.00
$200.00
OP0017
BlueDent
D0120
$185.00
$175.00
OP0018
Westville
D0120
$195.00
$150.00
OP0019
Westville
D1110
$225.00
$125.00
OP0020
CompDent
D2751
$375.00
$500.00
OP0021
CompDent
D2330
$325.00
$400.00
OP0022
Westville
D2751
$450.00
$400.00
OP0023
Westville
D0120
$325.00
$300.00
OP0024
SIP
D0210
$500.00
$550.00
OP0025
SIP
D0120
$250.00
$250.00
OP0026
BlueDent
D2751
$125.00
$115.00
OP0027
CompDent
D0120
$325.00
$300.00
OP0028
Westville
D2330
$275.00
$250.00
OP0029
SIP
D2751
$200.00
$235.00
OP0030
BlueDent
D1110
$185.00
$175.00
OP0031
CompDent
D2330
$225.00
$235.00
Wellness Services
Weekly Workout Plan
Calories/Hr
Activity
# of Times
per Week
Calories Burned
per Week
High-Intensity Interval
525.00
1
525.00
Average Weekly Calories Consumed
Running
500.00
1
500.00
Average Weekly Calories Burned
Zumba
400.00
2
800.00
Water Aerobics
300.00
1
300.00
Strength Training
235.00
1
235.00
1,960.00
6
2,360.00
Total
Goal
3,500.00
15,965.00 Daily Consumed
2,360.00 Daily Burned
2,280.71
337.14
Wellness Services
Weekly Calorie Journal by Meal
Breakfast
Dinner
Dessert
Total
350
200
600
250
800
250
2,450
300
190
750
225
675
150
2,290
325
250
500
150
500
175
1,900
400
275
650
200
850
200
2,575
325
300
545
250
575
150
2,145
425
150
600
225
650
155
2,205
400
200
525
300
775
200
2,400
361
224
596
229
689
183
2,281
Total Calories
500
600
700
800
900
1,000
1,100
1,200
Dinner Calories
Average
Lunch
Afternoon
Snack
Dinner Calories
Date
Morning
Snack
Lunch Calories
500
500
600
700
800
900
1000
1100
1200
600
700
800
900
1000
1100
1200
USING MICROSOFT EXCEL 2016
Guided Project 8-3
Guided Project 8-3
Courtyard Medical Plaza has new worksheets for weight loss workshops. You use Solver with sample data and add
scenarios and data tables to complete a sample set. You also create PivotTables to analyze dental insurance data.
Skills Covered in This Project
•
•
•
•
Step 1:
Download
start file
Create and manage scenarios.
Use Solver in a worksheet to find a solution.
Build a one-variable data table.
Build a two-variable data table.
•
•
•
•
Create and customize a PivotTable.
Insert a slicer in a PivotTable.
Insert a PivotChart.
Generate Descriptive Statistics for a set of data.
1. Open the CourtyardMedical-08 workbook and click the Enable Editing button. The file will be
renamed automatically to include your name.
2. Install Solver and the Analysis ToolPak.
a. Select the Options command [File tab].
b. Click Add-Ins in the left pane.
c. Click Go near the bottom of the window.
d. Select the Solver Add-in box.
e. Select the Analysis ToolPak box.
f. Click OK.
3. Click the Workout Plan worksheet tab and select cell E10. Five activities are included in this plan to
burn calories for weight loss. This cell includes a SUM formula.
4. Add scenarios in a worksheet.
a. Click the What-if Analysis button [Data tab, Forecast group] and select Scenario Manager.
b. Click Add.
c. Type Basic Plan as the name.
d. Click the Changing cells box, select cells
D5:D9, and click OK.
e. Do not edit the Scenario Values and click
OK.
f. Click Add to add another scenario.
g. Type Double as the name, keep the
Changing cells as is, and click OK.
h. Change the values to 2, 2, 4, 2, 2, doubling
each current value, in the Scenario Values
dialog box and click OK (Figure 8-96).
i. Click Close.
5. Use Solver to find a target calorie burn.
a. Click the Solver button [Data tab, Analyze group].
b. Select cell E10, the cell with a SUM formula, for the Set Objective box.
c. Click the Value Of radio button and type 3500 in the entry box.
d. Click the By Changing Variable Cells box and select cells D5:D7. Solver finds how many times
each activity should be performed to burn 3,500 calories subject to the constraints.
6. Add constraints to a Solver problem.
a. Click Add to the right of the Subject to the Constraints box.
b. Select cell D5 for the Cell Reference box.
c. Choose >= as the operator.
Excel 2016 Chapter 8 Exploring Data Analysis and Business Intelligence
Last Updated: 2/7/18 Page 1
USING MICROSOFT EXCEL 2016
Guided Project 8-3
d. Click the Constraint box and type 2. The constraint requires that the exercise be done at least
twice a week.
e. Click Add to add each of the five remaining constraints shown here:
D5 <=4 D6 <=3 D6 >=1
D7 <=4 D7 >=1
f. When all constraints are identified,
click OK in the Add Constraint
dialog box.
g. Choose GRG Nonlinear for the
Select a Solving Method.
h. Confirm that the Make
Unconstrained Variables NonNegative box is selected (Figure 897).
i. Click Solve. A solution displays in
the worksheet, and the Solver
Results dialog box is open.
7. Save Solver results as a scenario.
a. Click Save Scenario in the Solver
Results dialog box.
b. Type 3500 Burn as the scenario
name.
c. Click OK to return to the Solver
Results dialog box.
d. Click the Restore Original Values button.
e. Select Answer in the Reports list.
f. Click OK. The generated report is
inserted, and the original values
are restored in the worksheet.
8. Create a scenario summary report.
a. Click the What-if Analysis button
[Data tab, Forecast group] and
select Scenario Manager.
b. Click the Summary button.
c. Verify that the Scenario summary
button is selected.
d. Click the Result cells box, select
cells D5:D9, type a comma, and
then select cell E10.
e. Click OK in the Scenario Summary
dialog box. The report is
generated in a new worksheet.
Since the results cells are named,
the range names appear in the
report (Figure 8-98).
Excel 2016 Chapter 8 Exploring Data Analysis and Business Intelligence
Last Updated: 2/7/18 Page 2
USING MICROSOFT EXCEL 2016
Guided Project 8-3
9. Create a one-variable data table to calculate total calories if dinner calories are adjusted.
a. Click the Calorie Journal worksheet tab and select cell I5. The SUM formula calculates total
calories consumed per day.
b. Select cell E15. The formula for the data
table must be one column to the right and
one row above the first input value.
c. Type =, click cell I5, and press Enter.
d. Select cells D15:E23 as the data table range.
e. Click the What-If Analysis button [Data tab,
Forecast group] and choose Data Table.
f. Click the Column input cell box and select
cell G5. The input values will be substituted
for this cell in the data table formula.
g. Click OK (Figure 8-99).
10. Create a two-variable data table to calculate
total calories if both lunch and dinner calories
are adjusted.
a. Select cell L15. A two-variable table has one formula, one row above column inputs and one
column left of row values.
b. Type =, click cell I5, and press Enter.
c. Select cells L15:T23.
d. Click the What-If Analysis button [Data tab, Forecast group] and choose Data Table.
e. Select cell E5 for the Row input cell box. Lunch calories are in the row of this data table.
f. Click the Column input cell box and select cell G5. Dinner calories are in the column.
g. Click OK to build the data table (Figure 8-100).
11. Select cell J1 and insert a page break [Page Layout tab, Page Setup group].
12. Create a PivotTable for dental insurance data.
a. Click the Dental Insurance worksheet tab and select cells A4:E35.
b. Click the Recommended PivotTables button [Insert tab, Tables group].
c. Choose Sum of Billed by Service Code and click OK. Label fields are in the Rows area, and
numeric fields are in the Values area.
d. Name the worksheet tab PivotTable 1.
e. Point to Billed in the Choose fields to add to report area and drag the field name to the
Values area to show the field twice in the PivotTable.
Excel 2016 Chapter 8 Exploring Data Analysis and Business Intelligence
Last Updated: 2/7/18 Page 3
USING MICROSOFT EXCEL 2016
Guided Project 8-3
13. Edit value field settings.
a. Click Sum of Billed in cell B3 and click the Field Settings button [PivotTable Tools Analyze, Active
Field group].
b. Type Total Billed as the Custom Name.
c. Click Number Format, choose Currency, set 0 (zero) decimal places, and click OK two times to
close the dialog boxes.
d. Right-click Sum of Billed2 in cell C3 and select Value Field Settings.
e. Type Average Billed as the Custom Name.
f. On the Summarize Values By tab, choose Average as the function.
g. Click Number Format, choose Currency,
set 0 (zero) decimal places, and close
the dialog boxes.
14. Use PivotTable tools to format the report.
a. Click any cell in the PivotTable and
apply Teal, Pivot Style Medium 6, or Pivot
Style Medium 6.
b. Select the Banded Rows and Banded
Columns boxes [PivotTable Tools Design
tab, PivotTable Style Options group].
c. Click the Grand Totals button
[PivotTable Tools Design tab, Layout
group] and choose Off for Rows and
Columns.
15. Select cell A1 and type Courtyard Medical
Dental Services.
16. Type Billings by Service Code in
cell A2.
17. Format both labels as 14 pt. (Figure
8-101).
18. Create a PivotChart.
a. Select a cell in the PivotTable
and click the PivotChart button
[PivotTable Tools Analyze, Tools
group].
b. Choose Column and Clustered
Column as the subtype and
click OK.
c. Drag the chart object so that its
top-left corner is in cell E3.
d. Size the chart object to reach
cell M24.
e. Click one of the Average Billed
columns and click the Change
Chart Type button [PivotChart
Tools Design tab, Type group].
f. Click the Chart Type arrow for
the Average Billed series and
choose Line with Markers (Figure 8-102).
Excel 2016 Chapter 8 Exploring Data Analysis and Business Intelligence
Last Updated: 2/7/18 Page 4
USING MICROSOFT EXCEL 2016
Guided Project 8-3
g. Click OK.
h. Click one of the Total Billed columns and change its Shape Fill [PivotChart Tools Format tab,
Shape Styles group] to Teal, Accent 5, Darker 25%.
19. Insert a slicer.
a. Click any cell in the PivotTable and click the Insert Slicer button [PivotTable Tools Analyze tab,
Filter group].
b. Select the Insurance box and click OK.
c. Position the slicer so that the top-left corner is in cell O3. Size the slicer to reach cell Q15.
d. Format the slicer with Aqua, Slicer Style Dark 5, or Slicer Style Dark 5.
e. Click CompDent in the slicer to filter the PivotTable and PivotChart (Figure 8-103).
Step 2:
Upload &
Save
Step 3:
Grade my
Project
f. Select cell A1.
20. Generate Descriptive Statistics for a rating category.
a. Click the Dental Insurance sheet tab.
b. Click the Data Analysis button [Data tab, Analyze group].
c. Select Descriptive Statistics and click OK.
d. Select cells E4:E35 for the Input Range box.
e. Select the Labels in First Row box.
f. Select the Output Range button.
g. Click the Output Range box and click cell G4.
h. Select the Summary statistics box and click OK.
i. AutoFit column G (Figure 8-104).
21. Save and close the workbook.
22. Upload and save your project file.
23. Submit project for grading.
Excel 2016 Chapter 8 Exploring Data Analysis and Business Intelligence
Last Updated: 2/7/18 Page 5
USING MICROSOFT EXCEL 2016
Excel 2016 Chapter 8 Exploring Data Analysis and Business Intelligence
Guided Project 8-3
Last Updated: 2/7/18 Page 6
USING MICROSOFT EXCEL 2016
Independent Project 8-5
Independent Project 8-5
At Placer Hills Real Estate, the commission and fee split between the listing and selling agents depends on price
tiers (levels). You create a one-variable data table to display commissions and net earnings for various split rates.
You copy the sheet and edit it to show results for another price tier. Additionally, you create scenarios for selling
price and commission, generate summary statistics, and create a forecast sheet.
Skills Covered in This Project
•
•
•
Step 1:
Download
start file
1.
•
•
Build a one variable data table.
Use Solver.
Create and manage scenarios.
Generate descriptive statistics.
Create a forecast sheet.
Open the PlacerHills-08 workbook and click the Enable Editing button. The file will be renamed
automatically to include your name.
2. Review formulas.
a. Select cell C14 on the Tier 2 worksheet. The total commission is calculated by multiplying the
selling price by the listing rate.
b. Select cell C15. The nested IF function checks the selling price (C12) to determine the
commission split percentage (column D).
c. Select cell C16. The nested IF function checks the selling price (C12) to determine the fee
percentage (column E) and multiplies the commission (C15) to calculate the fee in dollars.
d. Select cell C17. The net commission is calculated by subtracting fees from the PHRE amount.
3. Build one-variable data tables.
a. Select cell C20 and create a
reference to cell C15.
b. Select cell D20 and create a
reference to cell C17. Both formulas in
the data table refer to cell C12, the
one variable.
c. Use cell D8 as the column input for the
data table. (Figure 8-110).
d. Decrease the decimal two times for
the results in the data table.
e. Make a copy of the Tier 2 worksheet
and name the copy Tier 1.
f. Edit the label in cell A23 to show Tier 1.
g. Edit the selling price in cell C12 to
$1,000,000.
h. Recalculate the data table with cell
D7 as the column input for Tier 1.
4. Name cell ranges.
a. Click the Price Solver worksheet tab.
b. Click cell C12 and name the range
Selling_Price. You cannot use spaces
in a range name.
c. Name cell C14 as Total_Commission
and cell C17 as PHRE_Commission.
.
D8
>< fx A B E D C 2 - 3 4 I F G I I H I 1 Fat IRE: CoDllll.is..ion S plit Placer Hills Real E_sta e Calculator I I 5 Maximum Pri ce Minimum 0 Price Tier 7 9 1 2 3 10 4 8 P1lce s s _S -------- 1,500,000 __ $ 5 ,500,000 9 , 500, 000 $ s s s Commi~ion Split Fees 1,499,999 ~----- 25~ ) 9.0% 5,499,999 I J51 r-_ 11.0% 9,499,999 45% 12.5% 13,499 ,999 55% 13.5% 11 s 4,500,000 6% 270,000 li li_ li Selling Price Total Commission s 'i ,~ PHRESplit $ 94,500 Fee Calculation 17 Net Com mission s s 84,105 $ Solit 94,500.00 ~~!~~e Commission ~olumn input ce ll: 10,39.5 X ? Data Ta ble Bow input ce ll: 1,l!jl lrtijl I ISOSO DO Ca~ 18 - Possible Rates 19 20 21 21 2l 24 25 Tier 2 2.5% 30% 35% 40% 45% 26 50% 21 55% 60% 28 Net Pav $84,105.00 29 8-110 Data table setup for Tier 2 5. Install the Solver Add-in and the Analysis ToolPak. 6. Use Solver to find target PHRE net commission amounts. a. Build a Solver problem with cell C17 as the objective cell. For the first solution, set the objective to a value of 50000 by changing cell C12. Save the results as a scenario named $50,000. Excel 2016 Chapter 8 Exploring Data Analysis and Business Intelligence Last Updated: 12/29/17 Page 1 Independent Project 8-5 USING MICROSOFT EXCEL 2016 b. Restore the original values and run another Solver problem to find a selling price for a PHRE commission of 75000. Save these results as a scenario named $75,000. c. Restore the original values and run a third Solver problem to find a selling price for a net commission of $100,000. Save these results as a scenario. 7. Manage scenarios. a. Show the $50,000 scenario in the worksheet. b. Create a Scenario summary report for cells C12, C14, and C17. 8. Generate Descriptive Statistics for recent sales. a. Click the Sales Forecast sheet tab. b. Generate Descriptive Statistics for cells E4:E26 and note that the label is in the first row. c. Select the Output Range button, click the entry box, and select cell G4. d. Select the Summary statistics box and include the largest and smallest (Kth values) in the report. e. AutoFit column G (Figure 8-111). 9. Create a forecast sheet for the date and price data on the Sales Forecast sheet using a column chart. End the forecast three months from the last date in the table. Position the chart object with its top-left corner in cell E1. Size the chart object to reach cell P20. Name the generated sheet as Forecast. Step 2: Upload & Save Step 3: Grade my Project 10. Save and close the workbook (Figure 8-112). 11. Upload and save your project file. Sale Price I Mean Standard Error Median 1 370593.2 19986.61 365000 Mode #N/A St andard Deviation 93745.5 Sample Variance 8.79E-t-09 Kurtosis -1.23251 Skewness 0.01939 Range 294100 Minimum 228900 Maximum 523000 Sum Count 8153050 22 Largest(!) 523000 Smallest(!) 228900 8-111 Descriptive statistics for sales 12. Submit project for grading. Excel 2016 Chapter 8 Exploring Data Analysis and Business Intelligence Last Updated: 12/29/17 Page 2 USING MICROSOFT EXCEL 2016 Independent Project 8-5 Commissle • Split C..lelllator Prri<:e-Tler Minimu-m Price lta>imum
Price
CommiHion
Spilt
Commis.sio• Split
Caleulator
staL.___ _ _ _ _
_ _ _ _—-.
Prl{:e n er
Fes
_ _:.._ _-+-”
S_ _ _ _ _+cc_-”
V199,999 -··- ·- · zs,·;u _ _ 9.Ml
–‘—-+””
s _ _- =
u oo.ooo_ s ··-· s .499.m _ .. _ ,_ , s~l!I _ ,1 1.M;
– – ” – – – – +-‘,– -?’,5
,500,00J. S —- 9,499,999 – — – ·- · .!…5-~ _ _ll.5~
9,500,000 S
13,499,999
55~
13-.5’~
Scllin1.Price ·-·-·· –
I
________ 2 ________
1 5,000
J:~~~~~~-Ne1 Comm lsdo n
$
S
1,350
13,650
Pou:lble R.ate-.s
25:>I
S
5,500,000
$
9, d.99_.’99-~
t::5%
12.5%
9,500,000 $
B ,499,’m
5~
–il5%
Sellin,1 Prire ________ t–s
Lktl.ng Camm~lon
____ ~,50~000
__ _ __ ___ __ __ _30:>I
Tier 1
., _ __ __ ., ____35ll
., ___ __ ., ____409I
., _____ ., ____ 45ll
~m~sKXl
S
$
10, 395.
“$
84,105-
Possible Rat!’.5-
Sp{it
94, S.OO
13,650
SO% $
30,00J
$
21,300
55ll S
60ll 5
33,000 S
36,000 $
30,000
32,760
6%
:=::S=:=:= 2~~~=
Fee Ca lc:Ul!a1lon
Net Commi ~,ion
15,000 S
U ,650
S ____ …,1,=B,””OOO
=–if-‘Scc__,1′-=6~
,380
=-i
S __ ., __ 21,000 S 19,110
5 __ ., __ 24,000 S_ .. 21.SO
5 __ ., __ 2?,000 5_ .. 2~70
Jees
S
Ner Pay
15,0CO
Spl tr
—1-c–~l.~Joc,9cc~=.’-r—“‘
==+–..t ~
s________ 1,~ooo
s____ Ji499.L999
35% __ t!-.~
S
t.i:
S pllr
—————–
Commi5-Sion
Price
3
5 ·-·· 1,000,000 .
$
Ma>Grnum
Pri ce
4
Usti:n1.Commis.s.ion __ -·-··-·-·-· 6~
rotal commls!!.lon
S
60,000
P HII.E Sp lh
Mt11imu m
I.Jet Pay
S.
84, 105
60.01s
25li ~–5=1=.;oo=””‘””s
______________ 303’ ,j —–~
B~
l ~ooo
=t~•-~7~2~090
4
Tie r 2
,______, _s”-+-S _ _..::•…:••c:.””‘
~ …:>c_…:•:.:•cc•1ccaec ,
40%
S
108.000 S
96,120
————– 4591 $
121,500 S 108,135
5 ,,,ooo”‘+–‘s~ 1,,2,,o””is”o'”
______________ 503’ ,j _______,13=
55% S
146,500 S
151,165
60* ‘$
162,000 S
144,180
2{‘,-U ~~&,’!DO
lf1Af»1E- S,IA.S,(DQ
l,121(»16 i U 7,(ll0
l,f’J~Ui
3/61»1&
Sl~mo
iHQ.U SZ’5;’!00
J/H/1Dlli sn1,mo
4f3(1DJJi
SJ]!t,(DCI
~,,1,,
Purchase answer to see full
attachment

Order a unique copy of this paper
(550 words)

Approximate price: $22

Basic features
  • Free title page and bibliography
  • Unlimited revisions
  • Plagiarism-free guarantee
  • Money-back guarantee
  • 24/7 support
On-demand options
  • Writer’s samples
  • Part-by-part delivery
  • Overnight delivery
  • Copies of used sources
  • Expert Proofreading
Paper format
  • 275 words per page
  • 12 pt Arial/Times New Roman
  • Double line spacing
  • Any citation style (APA, MLA, Chicago/Turabian, Harvard)

Our guarantees

Delivering a high-quality product at a reasonable price is not enough anymore.
That’s why we have developed 5 beneficial guarantees that will make your experience with our service enjoyable, easy, and safe.

Money-back guarantee

You have to be 100% sure of the quality of your product to give a money-back guarantee. This describes us perfectly. Make sure that this guarantee is totally transparent.

Read more

Zero-plagiarism guarantee

Each paper is composed from scratch, according to your instructions. It is then checked by our plagiarism-detection software. There is no gap where plagiarism could squeeze in.

Read more

Free-revision policy

Thanks to our free revisions, there is no way for you to be unsatisfied. We will work on your paper until you are completely happy with the result.

Read more

Privacy policy

Your email is safe, as we store it according to international data protection rules. Your bank details are secure, as we use only reliable payment systems.

Read more

Fair-cooperation guarantee

By sending us your money, you buy the service we provide. Check out our terms and conditions if you prefer business talks to be laid out in official language.

Read more

Calculate the price of your order

550 words
We'll send you the first draft for approval by September 11, 2018 at 10:52 AM
Total price:
$26
The price is based on these factors:
Academic level
Number of pages
Urgency

Order your essay today and save 15% with the discount code DISCOUNT15