BlueSkiesAirlines

itt You arean analystfor BIueSkiesAirlines,a regionalairlineheadquartered in KansasCity.BlueSkieshas up to 10 departuresa dayfrom the KansasCity Airport. Your assistantdevelopeda templatefor you to per flight. Eachregionalaircraftcanhold up to 70 storedaily flight dataaboutthe numberofpassengers passengers. Youneedto calculate theoccupancy rate,whichisthepercentofeachflightthatisoccupied. In addition,you needdaily statistics,suchastotal number of passengers, averages, leastfull flights,and so lorth. so thatdecisions canbe madefor futureflight departures out of KansasCity.Youalsowant1o weeklystatistics per flight number.This exercise calculate followsthe samesetof skillsas usedin Hands-OnExercises 1 and2 in the chapter.Referto Figure2.33asyou completethis exercise.

€

r

l

l

:

l

|

Air i!n+ s Blr - rSk!*s * lAir(stt Cip*ityi Flight hfo flightt$ Dstlmtian 451€ 3Er" 3:48 Trl 4?5 DF|V 345 CRD

turlday

13d.0 TUL

45

4T8 45:6

65 63 70 78 70

:t ?":; i;t'. ii.'i r': r i:c l.l :a'f,.r-_i

JTO 3:2 J49

SLC Dr!.r' ORD DE\ ORD

Totdl tsef Farrenger: AVefiSel

trt 6C.l

Ssld

lrl*1;rn! Ler5l lrii i)ights htoSi -rI ijirnis Aoi F!ishE Der$!v

35 70 iB

T@dsy

Monday

5l![ S-EEsr :::i:! s illi.tllj 5l tr:.i]: 7C ?i L,trr 7D i; j:,, 61

{SG! tr 35 58 d9

53% lSO%

67

ElsI n-Pcs !t1.1': 55 ti

r:.

S-Eu!!

fi

-n:t x:j

6C

f,:i.?!!

.s.!:l

66

!i

ll:

68

€1 64

.,:ii

66

!,i.:j;

;C

45

ii_i":

4S

.::.a!

5S 58

5:..rii ;-..!.1 :2.r!

66

ni iti 6:

t1:r.i:il

tr

4:.t:r'l

ii

r.,

lo

:ti

l:r

lr: :i

6:

,ti'.:

il

llic

6v ,65.4 66 5J70 10

9ll vi 8ti fot

453 i7.375 :3 48 67 I

429 829 61,2857 sli 6: 69i; 45 6E 96,q 7

r:r: il

94*

:5

;r i:

tr

!; :11

66

1.1l;

69

ijli i,i'

78

ii.r: ci

:::i.11 I ,:i:t +

59

65 €6

lii i:

78

78

567 839 93.* ffis

&lsl

'r' !\

4B

t;::

Studsy g-E@ &&I

friday

dlas

::. r;

6?

:tir t:!

::,'i.

ft

g.Eae

ii:f l,

7c

60

fhur$y 8-Pas Sls[

wBdoesday

&l!I

53 65 43 70 I

il

69

69* 1m9i

5t l0 I

r-rl.rai

67

1t; ;,',.

ffi 68

.r.il:.. .rr iit

69 55

!t ill iil.il;:

:i

iir i! :ilil.rl:

575 90.? d:"88A9 93i 63

55

380 !!9 63,3331 97? 66.5 71.e 100?

55 69 6

90ti grs 79% 99!r

FIG4JRK€"S* BlueSkies Airlines)p

a. Open e02pllligh*andsaveit ase02plflights-LastnameFirstname. b. Clickcell D6, the cellto displaytheoccupancypercentfor Flight 4520on Sunday,and do the folJowing: . Type= and click cell C6. Type/ and click cell C2. . PressF4 to makecellC2 absolute. . Click Enter to the left of the FormulaBar.The occupancyrateof Flight 4520is 85.7o/o. . Double-clickthe cell D6 fill handleto copythe formuladown the column. c. Click cell D7 and noticethat the bottom borderdisappears from cell D l5. When you copya formula,Excelalsocopiesthe originalcell'sformat.The cellcontainingtheoriginalformuladid not havea bottom border,so when you copiedthe formula down the column, Excelformatted it to matchtheoriginalcellwith no border.To reapplytheborder,clickcellDl5, clickthe Border arrow in the Font groupon the Home tab,and thenselectBottom Border. d. Selectthe rangeD6;DI5, copyit to the Ctipboard,and pasteit startingin cell F6.Noticethe formula in cell F6 changesto * E6l$C$2.The first cell referencechangesfrom C6 to E6, maintaining its relativelocation from the pastedformula. $C$2remainsabsoluteso that the per flight is alwaysdivided by the valuestoredin cell C2.The copiedrange number of passengers is still in the Clipboard.Pastethe formula into the remaining 0/oFull columns (columnsH, J,L, N, andP).PressEscto turn off themarqueearoundtheoriginalcopiedrange. e. Cleanup thedataby deleting0.0o/oin is misleading asit impliesthe cells,suchasH7.The0.0olo flight wasempty;however, someflightsdo not operaleon all days.Checkyour worksheetagainst the Daily FlightInfornalionsectionin Figure2.33.

. Fx**l **1* PracticeExercises

w S'

.:'d

$,

Calculatethe total nunber of passenger6,pgr dayby doing the'foll,owing: .., . ' Click cell Cl8. ,,, c ClickAutoSumintheEditinggrr:up. . Selectthe rangeC6:C15,and then pressEnter,

,

per dayby doingthe following: c. Calculatethe averagenumberof passengers

. Click cell Cl9. r Click the AutoSum arrow in the Editing group, and then select,Average. o Selectthe rangeC6:C15,and thenclickEnter to the Ieft of the FormulaBar.

per dayby doingthe following: h. Calculatethe mediannumberof passengers . Click cell C20. e Click Insert Functiori to the left of the Formula Bar,type median in the Searchfor a function box, and then click Go. . Click MEDIAN in the Selecta function box, and thenclickOK. o Selectthe rangeC6:Cl5 to enterit in the Numberl box,and then clickOK. L.

Calculatethe leastnumberof passengers on a dailyflight by doingthe following: r Click cell C2l. o Click the AutoSumarrow in the Editinggroup,and then selectMin. o Selectthe rangeC6:C15, andthenpressEnter.

on a dailyflight by doingthe following: ). Calculatethe mostpassengers

. Click cellCl2. . Click the AutoSumarrow in the Editinggroup,and then selectMax. o Selectthe rangeC6:C15,and then pressEnter.

Calculatethe numberof flightsfor Sundayby doingthe following: . Click cellC23. o Click the AutoSum arrow in the Editing group,and then selectCount Numbers, r Selectthe rangeC6:C15,and then pressEnter. in cells Dl9:D22- Format the valueswith l. Calculatethe average,median,least,and fuIl percentages PercentSt/e with zerodecimalplaces,Do,not copythe formulasfrom colurnnC to column D, as it doesnot that will changelhe borders.Youwon't inserta SUM function in cellD18 because column.SelectcellsCl8:D23, copythe range, makesensetolotal the occupancyratepercentage and thenpastein thesecells:E18,Gl8, Il8, Kl8, Ml8, and Ol8. PressEscafterpasting. m. Createa footer with your name on the left side,the datecodein the center,and the file name code on the right side. n. Saveand closethe workbook,and submitbasedon your instructor'sdirections.

You work in the Human Resource$Departrnent at Central Nevada College.You are preparing a ratings,whereratingsbetweenI and 1.9 spreadsheet modelto calculate bonusesbasedon performance do not receivebonuses,ratingsbetween2 and 2.9 earn$100bonuses,ratingsbehveen3 and 3.9 earn $250bonuses,ratingsbetween4 and4.9 earn $500bonuses,and ratingsof 5 or higherearn $ 1,000 bonuges,In addition, you nebd to caleulqteannufil raisesbased on years employ€d.Employeesr'r'ho raise;emplopeswho havenot workedat leastfive years haveworkedfiveor more yearsearn a3.25o/o partially doesnot yet contain rangenames.This exercise completed worksheet earna 2o/oraise.The I , 2, 3, and4 in the chapter.Referto Figure followsthe samesetof skillsasusedin Hands-OnExercises 2.34asyo* completethis exercise.

CHATPT€R g I Forinu,lasiand Firnctions

iffi, i

#.", ' '

:.):.1

a. Open eAhp2sal*r.yandsaveit ase02p2salary-LastnameFitstname. b. Click cell 84; click the Formulas tab, click Date &Tirne in the Function Library,selectTODAY, and ihen aict OK to enter today'sdate'inthe cell. c Enter a formula to calculatethe number of yearsemployedby doing the following:

" : a : . : : : . : : , . . : . . . : : . : . : . . . .. :' .: . : . . . .

: :kltrl";j'"J; ;;;;;".;;;,il, ,

;.; ;.hense,ec,

YEARFR,AC. , . Click ccll.A,ll to enterthe cell referencein the Slarldate box. PressThb,and then click cell 84 to enterths cell referencein the End-date box,

absoluteor mixedfor cell 84 so that it alwaysrefersto the cell ccintainingthe TODAY ,,

::. ,.

"

. . ' : r . ' 1 " , . . : li : i .

thenumberof years, rti. ygnnpneCfuncrion io.-*f. =($B$a-Al1)/365 to calculate providesbetteraccurarysinceitaccountsforleapyearqqndthedivisor365doesnot ' ,:, .

:l:li:li"i:"r;",;'::"r,':'l':"',11'l'I l.:ll::ll,iIl'.i..:.l'..'..:'..'ll:

.

/

ourw!

ri

Double-clickthe cell Cl1 fill handle to copythe YE,+RIRACfunction down the Years Employedcolumn.Your resultswill differ basedon the.datecontainedin cell 84.

d. Enter the breakpointand bonus datafor the lookup tableby doing the following . Click cell A2l, type 1, and then pressCtrl+Enter. r Click the Hornetab, click FiIl in the Editing group,and then selectSerfu. Click Columns in the Seriesm section,leavethe Stepvalueu*1t afp. 5 in the Stopvalue box, and then click OK. ''' . ClickcellB2l..Enter0, 100,250,50O,and'tOOOdownthecolumn.Thecellshave'been"', previouslyi;;r"a p;;dy for.matted#* a*"""#fNo*u.' r"i-"iuirr r.i" a..ii""rpr'.*. o SelectrangeA2l:B25, click in the Name Box, type Bonus,and then pressEnter to name the ra.ngg.

. ' PrabticeExercises. Excel €OlO

...im

;,r . r",,'s 3f, q

. il";3' v

t

Clickcell Cl l, gpe >= and then clickcell nS. pfr, F4 to enterCl I >=$B$5to comparethe yearsernployeaio ttt. absolutereferenceofthe five-yearthresholdin the Logical-test box. PressThb,clickcellBl l, type* andthenclickcell86. PressF4 to enter811*$8$6to calculate raisefor employeesr+'howorkedfive yearsor more in the Value-if-true box. a 3.25o/o PressTab,clickcell Bl l, type * and then clickcell 87. PressF4 to enter81 1*$B$7to calculatea 2o/oraisefor employeeswho workedlessthan five yearsin the Vilue-if-false box. Click OK. . O."Uf.-.fick the cell Fll fill handle to copythe formula down the Raisecolumn. to Click cell Gl1. Type=B 1I.+ElI +Fl I to addthe currentsalary,the bonus,and the ra.ise the new salary.PressCtrl+Enter to keepcell Gl1 the activecell,and then double-click calculate the cell Gl I fill handle to copythe formula dor'vnthe column. h. Createa footer with your nameon the left side,the datecodein the center,and the file riamecode on the right side. Saveand closethe workbook,and submitbasedon your instructor'sdirections.

You are an agentwith Koyle RealEstatein Bowling Green,Ohio. Customersoften ask you what their monthly mortgag€paymentswill be,Youdecideto devetopan Exceltemplateyou can useto enterthe housecost,current APR.,yearlyproperty tax, and estimatedyearlyproperty insurance.Borrowerswho want the lowestinterestratesmakea 20olodown payment.Youwill assignrangenamesand usethem 2, 3, and 4 in in the formulas.This exerciselollowsthe samesetof skillsasusedin Hands-OnExercises the chapter,Referto Figure2.35asyou completethis exercise.

g

ffR ' i2 -co* 4 Ocwn

=clientl$Bsg =cllentlss$8 dlrentisBsls

3 ai$own8equired dlEnt!$89{ =clientt$Bsl2 6.jlnsu.ance -? iLoan *,:Mln5urance jMPmtPr ,_9. l0MPrapT.x :1. PmbFerYesr

=clientl$B$16 =Client!$B$19

&.lPropeiyTax

{lienti$As!1

=clientl$BsrT =Clientl$B$18 =clienllsBss

I i"os. 5 17McnthlyPayment{P&l) $

FIGURE 2.35 Client Mortgage Calculator)

D.

DtseovEn

$'.

Open e12p3clientandsaveit ase02p3client-LastnameFirstname. Createa rangenamefor the down paymentrequirementby doing the following: Click cell 84, click in the Name Box, type DownRequired,and then pressEnter. The number of paymentsper yearvaluehasalreadybeenassignedthe rangenamePmtsPerYear. Namethe input valuesby doing the following: . SelectrangeA8:B12. . Click the Formulastab, and then click Createfrom Selectionin the Defined Namesgroup. o Make sureLeftcolumnis selected, and then clickOK. o Click eachinput valuecellin rangeB8:Bl2 and look at the newlycreatednamesin the Name Box.

CHAPTER 2 . Formulasand Functions

As managerof the SunriseCredit Union, you are responsiblefor managingthe weeklypayroll.Your assistantdevelopeda partial worksheet,but you needto enterthe formulasto calculatethe regularpay, overtimepay,grosspay,taxablepay,withholding tax, FICA, and net pay.In addition,you want to total pay columnsand calculatesometrasicstatistics.As you constructformulas,makesureyou useabsolute and relativecell referencescorrectlyin formulasand avoidcircular references. a. Open the eA2m[payrollworkbookand saveit ase02mlpayroll-LastnameFirstname. b. Studythe worksheetstructure,and then readthe businessrulesin the Notessection. c. UseIF functions to calculatethe regularpay and overtimepaybasedon a regular40-hour workweek.Payovertimeonly for overtimehours.Calculatethe grosspaybasedon the regularand overtimepay.Abram'sregularpayis $398.With eightovertimehours,Abram'sovertimepayis $I 19.40. d. Createa formula to calculatethe taxablepay.With two dependents,Abram'staxablepay is $4r7.40. e. IJsethe appropriatefunction to identifr and calculatethe federalwithholding tax. With a taxable payof $417.40, Abram'stax rateis25o/o, andthe withholdingtaxis $104.35. f. CalculateFICA basedon grosspay and the FICA rate,and then calculatethe net pay. g. Calculatethe total regularpay overtimepay,grosspay,taxablepay,withholding tax, FICA, and nerpay. columns. h. Copyall formulasdowntheirrespective i. Apply Accounting Number Format to the rangeC5:C16.Apply Accounting Number Format to the first row of monetarydataand to the total row.Apply CommaStyleto the monetaryvalues monetaryvalues, andthenusetheFormat lor theotheremployees. Underlinethelastemployee's Cellsdialogbox to apply Double Accounting Underline for the totals. j. Insert appropriatefunctions to calculatethe average, highest,and lowestvaluesin the Summary

'.'jffi:f D,-eovEHs-&-'I#ffi*;:diff ;y.r,f :n;T*:::"'l:s"*ffi Lli:l?#5};it function. You can closethe practiceworksheetcontainingthe Help datawithout savingit. You want to determinethe number (frequency)of employeeswho workedlessthan 20 hours,between 20 and29 hours,between30 and 40 hours,and over40 hours.CellsJ28:J3llist the ranges.You needto translatethis rangeinto correctvaluesfor the Bin column in cellsI28:I30 and then enter the FREQUENCYfunction in cellsK28rK3l. The function should identify one employeewho worked between0 and 19hours and six employeeswho worked more than 40 hours. l. Apply other pagesetupformats asneeded. m. Insert a fo.oterwith your nameon the left side,the datecodein the center,and the file name code on the rightside. n. Saveand closethe workbook, and submit basedon your instructor'sdirections.

FirstBankof As a loan officer at First Bank of Missouri, you track house loans. You started a spreadsheetthat contains client names, the selling price of houses, and the term of the loans. You are ready to calculate the interest rate, which is based on the term. In addition, you need to calculate the required down pay-

fitill:ff:;iJ::i:ffi 3*TJ,:.Tf# ;ff:J*"ffiffii?, T.TJffiTll"f;ffi'*'t 6, *,?6s'"t.

; ffil l;ffi#ff::#iffiJ

&s:9 &*L

il m'astnameFirstname

c. Assign appropriate range names to the number of payments per year value and to the lookup table.

*3"{JqFT#ffi A . Formulasand Functions

4]-

d. Userangenameswhen possiblein formulas,and avoidcreatingcircularreferences. e.' Usean appropriatefunction to displaythe interestrate for the first customer. f. Usean appropriatelookup function to calculatethe amount of the dovm paymentfor the first customer.The down paymentis basedon the term and the sellingprice.The first customer's amountis $68,975. g. Calculatethe amount to be financedfor the first customer. h. Calculatethe monthly paymentfor the first customerusingrangenamesand cellreferences. The first customer's monthlypaymentis $t,142.65. i. Copy the formulasdown their respectivecolumns.Format interestrateswith PercentStylewith two decimalplaces.Format monetaryvalueswith Accounting Number Format with two decimal places. j. Calculatethe number of loansand other summarystatistics.Format the statisticsasneeded, k. Createa section,completewith column headings,for the rangenames.Placethis areabelowthe lookup table. l. Insert a footer with your nameon the lefl side,the sheetname codein the center,and the file name codeon the right side. m. Saveand closethe workbook, and submit basedon your instructor'sdirections.

, You area teachingassistantfor Dr. DeniseGerber,who teachesan introductory C# programmingclass at your college.One ofyour routine tasksis to enter assignmentand test gradesinto the gradebook. Now that the semesteris almost over,you needto createformulas to calculatecategoryaverages, the overallweightedaverage,and the letter gradefor eachstudent.In addition, Dr. Gerberwants to see generalstatistics,suchasaverage, median,low, and high for eachgradedassignmentand test,aswell as categoryaveragesand total averages. Furthermore,you needto createthe gradingscaleon the documentationworksheetand useit to displaythe appropriateletter gradefor eachstudent. a. Open the ea2mjgrades workbook and saveit ase02m3grades_LastnameFirstname. b. Usebreakpointsto enterthe gradingscalein the correctstructureon the Documentation worksheet,and then namethe gradingscalerangeGrades.The gradingscaleis asfollows:

95+

A

90-94.9 87-89.9

B*

8ffi6.9

B

8ffi2.9

B-

?7-79.9

U+

73-.76,9

7172.9 67-69.9

D+

63-66.9

D

6o-62.9

D_

0;5]g,9

F

c. Calculatethe total latrpoints earnedfor the fust studentin cell T8 in the Gradesworksheet.The

first studentearned93 latr points. d. Calculatethe averageof the two midterm testsfor the first studentin cell W8. The student's midterm test averaeeis 87. Mid-LevelExercises. #x*#l

*fl"{*

e. Calculatethe assignment average for the first studeritin cellI8. The formulashoulddrop the lowestscorebeforecalculatingthe average. Hint: Yotineedto usea combinationof three functions:SUM, MIN, and COUNT.The first student'sassignment is 94.2afterdropping average the lowestassignmentscore, f. Calculatethe weightedtotal points basedon the four categorypoints (assignmentaverage, liab points, midterm average,and final exam)and their respectiveweights(storedintthe range 840:843)in cellY8. Userelativeand absolutecell references asneededin the ---.forrilula. -.-' The first student'stotal **glriJ scoreis 90. .ui.ofu*.,n.letter,Srade eeuivalentin celfZ8.Usethe range C. Uset\ .gtryf.lii. fg;; name in the function. The first studentt letter gradeis A-. h. Copythe formulasdown their respective columnsfor the otherstudents. i the pabsing,score thresholdin cell 85 with,the rangenarnePassing.Displaya message in YT.

H:i1$.?:.*;ff :il[:f,frffiil1?.I*];;:i;ffi,1lil'$T"::;;:"3;::;:ffi

j. Calculatethe average,median,low, and high scoresfor eachassignment,lab,test,category Y . . - Y average,and total score.Displayindividual averages with no decimalplaces;displaycategoryand final scoreaverages with one decimalplace.Displayotherstatisticswith no decimalplaces. k. lnserta list of rangenamesin the designated areain the Documentationworksheet. Completethe documentationby insertingyout tra-i, today'sdate,and a purposestatementin the designated DtscovEn

m V

,. ff;:rr. instructor'sdiscrelion,add a column to displayeachstudent'srank in the class.UseHelp to learnhow to insertthe RANK function. m. Selectpagesetupoptionsasneededto print the Gradesworksheeton onepage. n, Insert a footer with your name on tlre left side,the sheetname codein the center,and the file codl on the right sideof eachworksheet. lame o. Saveand closethe workbook,and submit basedon your instructor'sdirections.

CHApTEFI A r Formulasand Functions

Youare a salesrepresentative at the localfitnesscenter,Buff and TuffGym. Yourmanagerexpectseach representative to track weekly new membershipdata, so you created a spreadsheetto store data. Membershipcosts are based on membershiptype. Clientscan renta lockerfor an additional annualfee. You are requiredto collecta down payment basedon membership type,determinethe balance,andthen calculatethe monthlypaymentbasedon a standardinterest rate. In addition,you needto calculategeneralstatisticsto summarizefor your manager.Spot-checkresultsto make sureyou createdformulasand functionscorrectlv. Perform Preliminary Work Youneedto openthestartingworkbookyoucreated, acknowledge theexistingcircularreference error,andassign a rangenameto the membership lookuptable.Youwill correctthe circularreference error later.

z.1 x .: - / /

a. Opentheg@mworkbook, click Help, read about circular references,closethe Help window that appears,and savethe workbook as g@*gf m _LastnameFirstnam e. , ^ AL3/( b. ASiSn the name Membership to the rangeA1S:C20. c. Insert a function to display the current date in cell B2. Calculate Cost, AnnualTotal, and Total Due You arereadyto calculatethe basicannualmembershipcostand the total annualcost.The basicannualmembershipis determinedbased on eachclient'smembershipQpe,usingthe lookup table. a. Insert a function in cell C5 to display the basic annual membership cost for the first client. b. Use a function to calculatethe annual total amount, which is the sum of the basiccost and locker feesfor those who rent a locker. The Locker column displays les for clients who rent a locker and No for those who don't. c. Calculatethe total amount due for the first client based on the annual total and the number of yearsin the contract.

b. Find and correct the circular referencefor the balance. The balanceis the differencebetweenthe total due and the down payment. c. Copy the two formulas for the rest of the clients.

Galculatethe MonthlyPayment Clientspay the remainderby making monthly payments.Monthly paymentsarebasedon the number of yearsspecifiedin the cliends contractand a standardinterestrate. a. Insert the function to calculatethe first client's monthly payment, using appropriate relative and absolutecell references. b. Copy the formula down the column. c. Edit the formula try changingthe appropriate cell referenceto a mixed cell reference.Copy the formula down. Finalize the Workbook You needto per{orm somebasicstatisticalcalculationsand finalize the workbookwith formattingand pagesetupoptions. a. Calculatetotals on row 14. b. Insert the appropriate functions inthe Summary Statisticssection of the worksheet:cells Hl8:H22. Format the payments with Accounting Number Format, and format the number of new members appropriately. c. Format the other column headingson rows 4 and.17 to match the filI color in the range El7:Hl7.Wrap text for the column headings. d. Format the monetary valuesfor Andrews and the total row with Accounting Number Format. Use zero decimal placesfor wholeamounts,and displaytwo decimalplaces for the monthly payment.Apply Comma Style to the internal monetary values.Underline the valuesbefore the totals, and then apply Double Accounting Underline (found in the Format Cells dialog box) for the totals.

d. Copy the three formulas down their respectivecolumns. Determine the Down Payment and Balance Youneedto collecta down pa1'rnentbasedon the tlpe of membership for eachnew client.Then you must determinehow much each clientowes. a. Insert the function to display the amount of down payment for the first client.

e. Set 0.3" left and right margins, and then ensurethe page prints on only one page. f.

Insert a footer with your name on the left side,the date code in the center,and the file name code on the right side.

g. Saveand closethe workbook, and submit basedon your instructor's directions.

r Exn*l *01S CapstoneExercise

Exercise1, you worked with the Blue SkiesAirlines' daily flight statistics.If you did not complete BlueSkies InthatPractice exercise,review the introductory paragraph,steps,and Figure 2.33. Open e12bJblueand saveit as Airlines e02blblue*LastnameFirstName. It is inefficient to delete0.0% when a flight doesnot exist for a particular (GENERAL

day,so you will createa formula that entersan empty text string if the number column contains a hphen ) using the IF function to evaluateif the # Passcolumn doesnot contain a hyphen. The value-iltrue argument should calculatethe occupancypercentage.The value_ilfalse argument should enter an empq/ text string, indicated by "". Copy the function to other days'o/oFull columns. Calculatethe averagedaily percentagefull for Flight 4520 in the Weekly Statisticsarea of the worksheet. Note that you need to use nonadjacent cell addressesin the function. Include empty o/oFull cells so that you can copy the formula down for the other flights. The empty cells do not affect the results.The aierage daily occupancyrate for Flight 4520 is 85.0olo. and the rates.For Flight4520,thelowestoccupancyrateis7 l.4o/o, Calculate thelow and high dailyoccupancy Insert a function to count the number of dayseachflight was made.Reapply highest occupancyrate is 92.9ol0. the bottom border to the statisticsarea,if needed.Changethe scalingso that the worksheet fits on one page. Include a footer with your name on the left side,the date code in the center,and the file name code on the directions. right side.Saveand closethe workbook,and submitbasedon your instructor's

CASE

MallLease Rates EESE/\RCH

C/\SE

ParkCity Condo RentalI l

I

r:

#B$::T@

As generalmanager of a shopping mall, you developeda spreadsheetto list current tenant data. Open e12b2mall,and saveit as e02b2mall-LastnameFirstname. You need to calculatethe expiration date, price per squarefoot, annual rent, and monthly rent for eachtenant. Use Help to researchhow you can construct a nested DATE function that will display the expiration date. The function should add the number of years for the ieasebut should subtract one day. For example, a five-year leasethat started on January 1, 2010, expireson December31,2014,not on January1,20l5.Assign three rangenames:the lookup table and the fwo constants.The price per squarefoot is basedon two things: length of the leaseand the number of square feet. If a tenant'sspaceis lessthan the threshold, the tenant's price per squarefootage is basedon the regular price. If a tenant'sspaceis at least3,000squarefeet,the tenant's price is basedon the adjusted squarefootage price. Use Help or searchthe lnternet to learn how to nest the VLOOKUP within the IF function. You will need two nestedfunctions. Use range names in the functions. Calculate the annual rent, which is the product of the square footage and price per square footage per tenant. Then calculatethe monthly rent. Avoid circular referencesin your formulas. Format values appropriately. Include a footer with your name on the left side, the date code in the center,and the file name code on the right side. Saveand closethe workbook, and submit basedon your instluctor's directions.

You and some friends are planning a Labor Day vacation to Park City, Utah. You have secureda four-day condominium that costs $1,200.Some people will stay all four days; others will stay part of the weekend. One of your friends constructed a worksheet to help calculateeach person'scost of the rental. The people who stay Thursday night will split the nightly cost evenly.To keep the costsdown, everyoneagreedto pay $30 per night per person for Friday, Saturday,and/or Sundaynights. Depending on the number of people who stayeachnight,the group may owe more money.Kyle,lan, Isaac,and Daryl agreedto split the differthe cirencein the total rentalcostand the amount the group memberspaid.Open eO)b3parkcity,address cular referenceerror messagethat appears,and savethe workbook as e02b3parkcity-LastnameFirstname. Reviewthe worksheet structure, including the assumptionsand calculation notes at the bottom of the worksheet.Check the formulas and functions, making necessarycorrections.With the existing data, the number The total paid given the aboveassumptionsis ofpeople stayingeachnight is 5,7,10, and 10,respectively. $1,110,givipg a difference of $90 to be divided evenly among the first four people. Kyle's share should be $172.50.In the cellscontainingerrors,insertcommenl.sto describethe error,and rhen fix the formulas. Verify the accuracyof formulas by entering an IF function in cell 11to ensure the totals match. Nick, fames, and Body inform you they can't stay Sundaynight, and Rob wants to stay Friday night. Change the input accordingly. The updated total paid is now $1,200, and the difference is $150. Inciude a footer with your name on the left side,the date code in the center,and the file name code on the right side.Saveand closethe workbook, and submit basedon your instructor's directions.

*$-!&pTHfi * o Formulasand Functions