BUS 1011 Excel e Book Guide PDF

Preview

Summary

A guide on how to complete the eBook exercises in detail...

Description

!

EXERCISE!1:!BASIC!FORMA TTING! AND!FORMULAS ! ! The!very!first!slide!your!professor!in!the!large!lecture!showed!you,!on!the!very!first!day!of!class,!was!the!most! fundamental!equation!in!business:!

Profits(=(Revenues(-(Expenses(( Your!first!Excel!spreadsheet!assignment!is!to!create!a!simple!two-row,!three-column!spreadsheet!with! precisely!defined!headings.!Using!a!“formula”,!you!will!plug!in!the!values!for!expenses!and!revenues!and!the! spreadsheet!will!calculate!profit.!!It!must!look!exactly!as!the!example!shown!below!to!score!the!maximum! number!of!points.!!!

Profit'= Revenue'Expenses $%%%%%%%%%%30,000 $%%%%%%%%100,000 $%%%%%%%%%%70,000 ! To!start,!open!the!Excel!document!titled!“Bus(10011(Excel(Student(Spreadsheets”!you!have!been!provided! with.! Renaming(and(Saving(an(Excel(Spreadsheet:!! Before!doing!anything!else,!go!to!“file”!in!the!extreme!upper!left!hand!corner!and!“save!as”![Your!Name]!Excel! Spreadsheet.! Renaming(a(Worksheet(within(an(Excel(File:!! At!the!very!bottom!of!the!spreadsheet!are!a!number!of!tabs!with!different!headings,!each!for!an!exercise!you! will!complete.!!Each!tab!pertains!to!a!worksheet,!and!the!worksheets!together!make!up!an!Excel!workbook.! Left!click!on!the!very!first!and!leftmost!worksheet/tab!labeled!“p=r-e.”!!This!is!the!template!you!will!start!with.!!! First!rename!the!worksheet.!Change!the!headings!from!lower!case!to!upper!case,!that!is,!p=r-e!to!P=R-E.!!To! do!so,!right!click!on!the!icon!and!select!“rename”.!!Type!in!the!uppercase!heading!P=R-E.! Entering(Text(into(Cells:!Now!go!back!to!the!spreadsheet!itself.!!First,!type!in!the!Headings:! • • • •

Type!“Profit!=”!in!Cell!A1!(Column!A,!Row!1)! Type!“Revenue!-”!in!Cell!B1!(Column!B,!Row!1)! Type!“Expenses”!in!Cell!C1!(Column!C,!Row!1)! Use!upper!and!lower!case!where!applicable.!

Entering(Numbers(into(Cells:!Second,!type!in!the!Numbers!you!will!be!working!with:! • !

Type!“100,000”!in!Cell!B2!(Column!B,!Row!2)! 4

• •

Type!“70,000”!in!Cell!C2!(Column!C,!Row!2)! Do!not!type!in!dollar!signs!($)!or!commas!–!we!will!change!the!format!of!the!cells!at!a!later!stage.!

Enter(Formulas:!Third,!type!in!the!Formula!to!calculate!profits:! •

In!Cell!A2!(Column!A,!Row!2)!enter!the!formula!“=B2!–!C2”!(Do!not!use!the!quotation!marks,!but!do!use! the!equal!sign.!!The!equal!sign!tells!Excel!a!formula!is!about!to!follow.)!

Fourth,!you!must!next!Re-format!the!table!making!changes!to!the!font,!alignment,!Column!width,!and!number! format.! Changing(the(Font(and(Font(Size:( • •

The!font!is!set!at!“Times!New!Roman”!12-point!in!the!“Home”!ribbon! On!the!spreadsheet,!click!the!box!in!the!extreme!upper!left!hand!side!of!the!table—just!to!the!left!of! the!letter!A!and!just!above!Row!1.!!When!you!do!so!the!entire!worksheet!will!be!highlighted/selected,! meaning!you!are!able!to!formal!all!the!selected!cells!at!once.!!!

•

Change!the!font!to!“Calibri”!14-point!with!bold(typeface.!

Changing(the(Alignment:( •

•

The!data!you!have!entered!into!the!Cells,!be!it!numerical!or!text,!is!“right!justified.”!As!you’ve!already! done!above,!highlight!the!table!by!clicking!on!the!extreme!upper!left!hand!side!of!the!table—just!to!the! left!of!the!letter!A!and!just!above!Row!1.!!! On!the!toolbar,!click!the!alignment!icon!and!“Align!Center”!the!data.!!!

Changing(the(Column(Width:( • •

Begin!by!highlighting!the!entire!worksheet!once!more.! The!column!width!is!currently!set!at!20.!!Go!to!“Format”!on!the!toolbar!at!the!top!of!the!table!and!click! on!“Column!Width.”!!Reset!the!column!width!by!typing!15!in!the!dialog!box!that!appears.!!If!set!in! inches,!type!in!1.32.! o For!Macs:!The!width!is!currently!set!at!14.5!Click!on!“Format”!under!the!“Cells”!option!in!the! “Home”!ribbon.!Reset!the!Column!width!by!typing!15!in!the!dialog!box!that!appears.!If!set!in! inches,!type!in!1.32.!

Changing(the(Number(Format:( • •

The!numbers!are!formatted!as!“General”!on!the!template!you’ve!created.!!Go!to!“Numbers”!and!click! “Accounting”!to!change!the!numbers!format.! Finally,!in!the!“Numbers”!section!you!will!see!icons!to!increase!or!decrease!the!decimal!places.!!Reset! the!spreadsheet!to!no!decimal!places.!

Your!spreadsheet!should!now!look!exactly!like!the!one!shown!above.!!Save!it.!

!

5

EXERCISE!2:!COPYING!AND!CALCULATING!SUMS!AND!AVERAGES! ! Let’s!build!on!the!last!example!and!make!it!a!bit!more!complicated.!!Let’s!assume!that!the!company!has!three! stores!--!A,!B,!and!C!--!only!one!of!which!is!shown!above.!!! Copying(Rows:(

•

Start!by!copying!Rows!1!and!2!to!Rows!5!and!6.!To!do!so,!select!Row!1!from!the!extreme!left!hand,! outer!cell!and!drag!down!to!Row!2.!Right!click!to!‘Copy’.!Select!Cell!Row!5!and!right!click!again!to!Paste.!!! Select!Row!6!by!clicking!in!the!extreme!left!hand,!outer!cell.!

• • •

Copy!the!content!in!Row!6!to!lines!7!and!8.! Type!the!numbers!in!B7!and!B8!as!well!as!C7!and!C8!as!shown!in!the!image!below.! Add!a!4th!Column!titled!“Store!Location”!as!shown!below.!

•

!

Profit'= Revenue'Expenses $%%%%%%%%%%30,000 $%%%%%%%%100,000 $%%%%%%%%%%70,000

%

Profit'= Revenue'Expenses Store'Location $%%%%%%%%%%30,000 $%%%%%%%%100,000 $%%%%%%%%%%70,000 Queens $%%%%%%%%%%50,000 $%%%%%%%%160,000 $%%%%%%%%110,000 Brooklyn $%%%%%%%%%%25,000 $%%%%%%%%%%90,000 $%%%%%%%%%%65,000 Bronx $'''''''105,000 $'''''''''35,000 '

$'''''''350,000 $'''''''245,000 $'''''''116,667 $'''''''''81,667 '

Sums Averages

!

( Calculating(Sums:( •

To!calculate!the!“Sum!of!Profits,”!type!“=SUM(A6:A8)”!in!Cell!A10.!

Calculating(Averages:( • • • !

!

To!calculate!the!“Average!of!Profits,”!type!“=AVERAGE(A6:A8)”!in!Cell!A11.! Select!and!then!Copy!Cells!A10!and!A11!and!Paste!them!into!Cells!B10,!B11!and!C10,!C11.! Add!the!headings!in!Cells!D10!and!D11!as!shown.! !

6

!

EXERCISE!3:!CREATING!A!“PIVOT!TABLE”! ! Pivot!Tables!allow!you!to!create!reports!on!a!new!spreadsheet!from!data!stored!on!a!separate,!often!large! spreadsheet.!!Employers!tell!us!that!knowing!how!to!create!and!manipulate!Pivot!Tables!is!an!essential!skill!for! their!businesses.!!! For!this!exercise!you!do!not!need!to!type!in!the!data.!!It!is!provided!to!you.!!Open!the!“Bus(1011(Excel(Student( Spreadsheets.”!!Look!to!the!bottom!of!the!spreadsheet!and!click!the!tab!titled!“Pivot!Table!Data.”!!There!you! will!find!a!spreadsheet!that!has!two!additional!Columns—“4!Quarters,”!which!lists!sales!(revenues)!for!each!of! the!four!quarters!in!a!year,!and!“2!Products,”!which!lists!the!two!key!products!sold!by!the!stores—Apple’s! iPhone!and!the!Samsung!Galaxy.!!

Manipulating(this(data(will(allow(you(to(answer(the(following(questions:( 1. Which(store—the(one(in(Queens,(Brooklyn(or(the(Bronx—was(the(most(profitable?( Which(one(was(the(least(profitable?( 2. Which(quarter(has(the(most(sales((revenue)(overall?( 3. Which(product—the(Apple(iPhone(or(the(Samsung(Galaxy—was(more(profitable( overall?( ( To!answer!these!questions:! • Click!on!any!cell!(say!A3)! • Click!on!“Insert,”!then!click!“PivotTable”!on! the!extreme!left.!The!following!“Create!PivotTable”! box!should!appear.! o For!Macs:!Click!on!the!“Data”!tab,!then! click!“Manual!Pivot!Table”!under!Analysis.!! • Simply!click!“OK,”!choosing!the!defaults.! • A!new!worksheet!will!be!created.!Rename!the! tab!“PT!Analysis.”! • On!the!right!hand!side!of!the!spreadsheet,!a! box!titled!“PivotTable!Field!List/Builder”!appears.! o For!Macs:!Click!inside!the!newly! created!blank!table!for!the!“PivotTable!Builder”!to!appear.!!

!

7

Let’s(start(by(answering(the(question:(Which(store—the(one(in( Queens,(Brooklyn(or(the(Bronx—was(the(most(profitable?(( • •

Click!on/check!the!box!for!“Profits.”! Then,!click!on/check!the!box!for!“3!Stores.”!

• Note!that!“Sum!of!Profit!=”!should!appear!under!Values.! • “3!Stores”!should!appear!under!Row!Labels.!! • A!Pivot!Table!should!appear!in!the!upper!left!hand!corner.!! • Click!and!draw!fields/variables!to!manipulate!the!Pivot!Table! and!arrive!at!the!rest!of!the!answers.!! ! Now!you!can!answer!the!questions:! • Which(store(is(the(most(profitable(overall?( • Which(quarter(has(the(sales(revenue(overall?(( As!an!added!step,!try!clicking!on!the!“2!Products”!box!in!the!PivotTable! Field!List.!!This!is,!you!will!now!have!a!total!of!four!boxes!selected— Profit,!3!Stores,!4!Quarters!and!2!Products.!!! ! ! ! !

!

!

8

!

EXERCISE!4:!CREATING!AN!INCOME!STATEMENT! ! Return!for!a!minute!to!the!very!first!exercise—the!one!where!you!created!the!equation!“PROFIT!=!REVENUE!–! EXPENSES.”!!That!equation!forms!the!basis!for!one!of!the!most!widely!used!financial!statements!in!accounting! called!the!Income!Statement.! The!Income!Statement!consists!of!three!parts:!

1. Revenue(-(Cost-of-Goods(Sold((COGS)(=(Gross(Profit(Margin( 2. Gross(Profit(Margin(-(Operating(Expenses(=(Net(Income(Before(Taxes( 3. Net(Income(Before(Taxes(-(Taxes(=(Net(Income((The(Bottom(Line)((( In!this!exercise!you!will!create!a!simple!Income!Statement!step-by-step.!!!

Income'Statement Revenue

$********100,000

20000 Units*x $*************5.00 per*Unit

Cost*of*Goods*Sold Gross'Profit'(Margin)

$**********30,000 $''''''''''70,000

Gross*Profit Operating*Expenses

$**********70,000

Net'Income'Before'Taxes

$************5,000 $**********20,000 $************6,000 $************1,000 $''''''''''38,000

Net*Income*Before*Taxes Taxes

$**********38,000 $************5,700

Net'Income

$''''''32,300

Advertising Salaries Rent Gas/Electric

! ! !

9

15% Tax*Rate !

Go!to!the!“Bus(1011(Excel(Student(Spreadsheets”(and!click!on!the!third!tab/worksheet!in!the!workbook,! labeled!“Income!Statement.”!

•

Change!the!Column!margin!in!Column!A!to!“30,”!!Change!the!Column!margins!for!Columns!B,!C,!D!to! “15.”!If!set!in!inches,!change!column!margin!in!Column!A!to!“2.64”!and!Column!margins!for!Columns!B,! C,!D!to!“1.32.”! Going!down!Column!A,!type!the!words:!“Revenue”!in!Cell!A3,!“Cost!of!Goods!Sold”!in!A5,!etc.!as!shown! on!the!sample!spreadsheet.! Be!sure!to!skip!Rows!1,!27!and!15!as!shown.!!Also!be!sure!to!boldface!the!headings!in!Rows!6,!14!and! 18!as!shown!on!the!sample!spreadsheet.! Next,!type!in!the!numbers!in!Column!B!as!shown!below!BUT$DO$NOT$TYPE$IN$NUMBERS$IN$THE$ BOLDFACED$ROWS—Rows!6,!14!and!18.! Now!type!in!the!formulas:!

• • •

In!Cell!B6!type!“=B3-B5”!!! In!Cell!B14!type!“=B8-(B10+B11+B12+B13)”! In!Cell!B18!type!“=B16-B17”!

•

• • •

Now!let’s!complicate!the!issue!somewhat.!!Instead!of!typing!the!number!“5,700”!in!Cell!B17!for!taxes,!let’s!set! a!tax!rate!by!percentage!formula.! • • • •

Type!“15”!in!Cell!C17.! Go!up!to!the!number!section!in!the!toolbar!and!reset!the!value!as!“Percentage.”! Write!the!words!“Tax!Rate”!in!Cell!D17.! Be!sure!to!reduce!the!font!size!to!12!in!Columns!C!and!D.!

•

Finally,!in!Cell!B17!type!the!formula!“=B16xC17”!which!will!calculate!15!percent!of!$38,000.!

Go!back!up!to!the!top!and!let’s!redefine!revenue!as!a!formula!rather!than!a!number.! • • • • • •

Type!“20000”!in!Cell!C3.!! Go!up!to!the!number!section!in!the!toolbar!and!reset!it!as!“Number.”! Type!“5”!in!Cell!C4.!!! Go!up!to!the!number!section!in!the!toolbar!and!reset!it!as!“Accounting.”! Type!the!words!“Units!x”!and!“per!Unit”!in!Cells!D3!and!D4!as!shown.! Finally,!in!Cell!B3!type!the!formula!“=C3xC4”!

As!shown!in!the!sample!spreadsheet,!under!these!assumptions!the!company’s!income!is!$32,000.! Inserting(Rows:( For!one!last!complication,!insert!an!additional!Row!in!the!Income!Statement!between!“Salaries”!and!“Rent.”! Call!it!“Transportation!Costs”!and!value!it!at!$4,000.!Under!these!assumptions!the!company’s!Net!Income! should!have!decreased!to!$28,900.!!Using(this(version(of(the(Income(Statement(as(a(template,(manipulate( the(entries(to(calculate(Net(Income(for(each(of(the(following(questions.! !

10

!

What(would(the(company’s(net(income((bottom(line)(be(if:( 1. Taxes(could(be(reduced(to(2%?( 2. The(company(pressured(suppliers(for(lower(prices((COGS)(from(30,000(to(26,000?( 3. An(advertising(campaign(was(created(that(raised(the(amount(spent(on(advertising( from($5,000(to($8,000(but(that(campaign(resulted(in(sales(revenue(rising(from(20,000( to(30,000(units?( 4. The(company(moved(and(restructured—moving(to(South(Carolina(where(rent(is(one( third(of(what(it(currently(is?((And(offered(an(incentive(program(to(eliminate(a(number( of(highly(paid(middle(managers,(which(would(lower(salaries(from(20,000(to(13,000?( Lastly,!you!can!use!an!Income!Statement!to!calculate!widely!used!financial$ratios.!!One!of!the! most!popular!is!Basic(Earnings(Per(Share,!which!is!Net!Income!divided!by!the!number!of! shares!outstanding.!!!!! 5. Assume(that(the(company(has(14,000(shares(outstanding.((Using(the(formula(Basic( Earnings(Per(Share(=(Net(Income(/(Number(of(Share(Outstanding(calculate(EPS.(( (

!

(

11

( (

EXERCISE!5:!USING!“LOOKUP”!AND!“COUNTIF”!FUNCTIONS!TO!RANK! EMPLOYEE!PERFORMANCE! ! Managers!often!like!to!rank!employees!performance.!!One!scheme!called!“20-70-10,”!or!more!disparagingly!as! “Rank!and!Yank,”!categorizes!the!top!20!percent!of!employees!as!“Best,”!the!middle!70!percent!of!employees! “Near!Best,”!and!the!bottom!10!percent!as!“Below!Best.”!!!!! Go!to!the!“Bus(1011(Excel(Student(Spreadsheets”!and!click!on!the!tab/worksheet!labeled!“Lookup(Table”.!! There!you!will!find!the!performance!ratings—on!a!ten-point!scale—for!20!employees.!!Each!employee!has!3! performance!ratings.!!! • •

In!the!5th!Column,!or!Column!E,!calculate!the!average$performance$rating!for!each!employee!to!one! decimal!place.! Then,!in!the!6th!Column,!or!Column!F,!use!the!Lookup(formula!below!to!classify!the!employees.!!! o =LOOKUP(E2,{0,4,9,10},{“Below(Best”,(“Near(Best”,(“Best”})(

The!Lookup!formula!is!structured!as!follows:! • • • •

E2!is!the!Cell!containing!the!data!for!which!the!calculation!should!be!made.! 0!to!4!is!the!classification!of!“Below!Best”.! 4!to!9!is!the!classification!of!“Near!Best”.! 9!to!10!is!the!classification!of!“Best”.!

•

The!words!“Below!Best”,!“Near!Best”,!“Best”!are!placed!for!each!of!the!three!classifications!according! to!an!employee’s!average!rating.!

Using(the(results,(do(the(following:( 1. List(the(names(of(the(“Best”(employees( 2. List(the(names(of(the(“Near(Best”(employees( 3. List(the(names(of(the(“Below(Best”(employees( Next,!redefine!the!Lookout!function!to!classify!employees!as!“Above!Average”!and!“Below!Average”!based!on! whether!they!scored!above!or!below!an!average!of!5!in!their!three!performance!scores.!Place!the!results!in! the!7th!Column,!or!Column!G.!! Next,!we’re!going!to!count!how!many!employees!were!categorized!as!either!“Above!Average”!or!“Below! Average”.!Place!the!following!results!in!cells!B23!and!B24.!!!! !

12

4. How(many(people(scored(“Above(Average?”( 5. How(many(people(scored(“Below(Average?”( To!answer!these!questions!use!the!COUNTIF!function.!!Its!form!is!=COUNTIF(G2:G21,!“Above!Average”),!where! G2:G21!is!the!range!of!cells.!In!this!example,!the!function!counts!the!number!of!employees!that!were!“Above! Average”!according!to!the!former!classification.!! Lastly,!you!will!notice!that!more!people!scored!“Above!Average”!than!“Below!Average”!using!5!as!the!cut! point.!!!Redefine!the!COUNTIF!formula!so!that!a!roughly!equal!number!of!employees!are!classified!as!“Above! Average”!as!“Below!Average.”!!

6. What(is(the(new(cutoff(point?( (

!

(

13

(

EXERCISE!6:!BREAKEVEN!ANALYSIS! ! Breakeven!Analysis!allows!you!to!assess!how!profitable!a!project!will!be!based!on!how!much!you!sell!each!unit! for!and!how!many!units!you!sell.!!! In!the!following!example,!assume!you!own!a!small!company!that!has!developed!a!new!exercise!device—called! “The!Strider”—that!tells!someone!how!far!they!have!walked,!how!many!calories!they!have!burned!and!how! many!paces!per!mile!they!have!tallied.!!Assume!the!following!facts.!!! •

The!machine!that!makes!the!device!was!purchased!for!$11,500.!!

•

The!microprocessors,!the!plastic!case!materials!and!the!labor!needed!to!assemble!it!costs!$15!for!each! device.!

You!have!decided!to!sell!the!product!directly!to!the!consumer!through!a!website!promoted!through!ads.! •

The!price!you!intend!to!charge!each!buyer!for!the!“Strider”!is!$42.!!

Before!you!market!the!product,!you!decide!to!create!a!spreadsheet!to!estimate!the!revenues,!costs,!profits! and!“Breakeven!Point.”!!Go!to!the!“Bus(1011(Excel(Student(Spreadsheets”(and!click!on!the!tab/worksheet! labeled!“Breakeven(Analysis.”!!Enter!the!following!data:! • • •

In!B3!type!10000!units!sold.! In!B4!type!$42!per!unit!sales!price.! In!B5!enter!the!formula!“=B3*B4”!to!calculate!the!revenue.!

• •

In!B7!type!$11,500!in!fixed!costs.!! In!B8!type!15!as!the!variable!costs!associated!with!making!each!unit.!!!

•

In!B10!enter!the!formula!“=B7+B8*B3”!which!is!the!fixed!costs!plus!the!$15!for!each!unit!multiplied!by! the!total!number!of!units!sold!10000.!

• •

In!B12!enter!the!formula!“=B5-B10”!to!calculate!the!profits!as!revenues!–!costs.!! B14!calculates!the!Breakeven$Point,!which!is!FIXED(COSTS/((PRICE(–(VARIABLE(COSTS).!!Enter!the! formula!“=B7/(B4-B8)”!

Note!that!the!Breakeven!Point!is!in!units!and!should!therefore!be!rounded.!You!should!set!up!the!spreadsheet! with(the(appropriate(formulas!to!change!the!price,!cost!and!sales!volume!quickly.!!

Answer(the(following(questions:( 1. What(are(your(revenues,(total(expenses(and(profit(if(you(sell(10,000(Striders?((( 2. What(are(your(revenues,(expenses(and(profit(if(you(sell(15,000(Striders(by(lowering( the(price(to($35(each?( !

14

3. How(many(Striders(do(you(need(to(sell(at($42(to(make($500,000(in(profits?((Find(the( answer(by(entering(different(numbers(in(the(appropriate(Cell(until(you(get(the(desired( result)( 4. How(many(Striders(do(you(need(to(sell(at($42(to(break(even?(( !

!

!

15

!

EXERCISE!7:!CREAT ING!A! CLUSTERED!COLUMN !AND!LINE! CHART ! ! Charts!are!visual!representations!of!data.!!Charts!are!useful!because!they!often!make!it!easier!to!understand! the!data!because!users!can!pick!out!patterns!and!trends!that!are!illustrated!in!the!chart!that!are!otherwise! difficult!to!see.!! For!this!exercise!you!will!analyze!the!changes!in!stock!price!for!five!different!airlines.!The!data!is!provided!for! you.!! Go!to!the!“Bus(1011(Excel(Student(Spreadsheets”!and!click!on!sheet!labeled!“Line(Chart.”! Open!the!file!and!find!the!spreadsheet!with!Columns!for!each!airline!and!Rows!for!each!month.!The!data!is!the! percent!change!in!stock!price!over!the!course!of!the!month.!! In!order!to!compare!the!stock!performance!of!the!five!airlines!we!will!create!a!chart.!Follow!the!instructions!to! create!a!chart.! ! • Select!the!range!“B1:F6.”!! • On!the!Insert!tab,!in!the!Charts!group,!choose!Column,!and!select!Clustered!Column.! o For!Macs:!!On!the!Data!tab,!select!choose!Insert!Chart,!and!select!Clustered!Column.!! ! Accurate(labels(are(necessary(so(that(the(user(can(interpret(the(data.(( ! • Right!click!on!the!graph!and!click!Select!Data.!In!the!section!that!says!Legend!Entries!(Series)!click!on! series!1!then!click!on!Edit!and!change!to!August!2014.!Change!the!reminding!four!series!to!the!correct! month.!!...