Category Archives: Tech Apps

Tech Apps Class from T1 2015-16

Web Advertisement Analysis

Develop an Excel workbook with a data sheet and a reporting sheet.

The datasheet needs the following columns:

  • Site the ad is on
  • Company advertising
  • Product
  • Product Category (data validated from list on reporting sheet)
  • # of males pictured in the ad
  • # of females pictured in the ad
  • # of cats in the ad
  • # of dogs in the ad

It may be necessary to disable ad blockers for this assignment.

You’ll go looking around the web — especially sites that provide information or service for free — think news or television sites, weather, recipes, song lyrics or what not, and look at the ads companies are paying to put on those sites.  Find at least twenty-five different ads.

The reporting tab should calculate:

  • Count of Advertisements
  • Total number of males depicted
  • Total number of females depicted
  • Average number of males depicted
  • Average number of females depicted

It should perform those calculations for all the advertisements combined, and also separately for each individual category.  The categories are:

  • Food
  • Entertainment
  • Financial Services
  • Healthcare
  • Technology
  • Cosmetics
  • Home goods (appliances…)
  • Clothing
  • Transportation
  • Recruitment

 

Business Plan

Here is the description of the business plan project.  Please see me with questions.  Also, attached is the Word Doc version of this assignment description.

Decide on a business you’d like to start. You will need to sell a product or service

Excel:

Develop a spreadsheet that lays out the anticipated expenses for starting up your business. You may need to rent/Lease/Buy office, retail, or studio space. Look up realistic numbers for the area you would want to place your business.

You may need furniture, computers, and initial inventory. You might also need to hire some people. Don’t forget to add associated payroll costs to that (possibly an additional 40% of their salary to pay for taxes and benefits).

Provide line items in one area, and totals by category in another. Remember to use formulas that will still be correct even if the line-item data changes (even if you change what category a line-item belongs in)

Develop another spreadsheet that lays out expected revenue and profit amounts. How much will you charge for your service/goods? What will it cost to provide that service or those goods? If you’re selling lots of different items (like a grocery store or clothing store, assume a consistent markup).

Provide charts to show how long it will take that business to become profitable (that is, to make up for the startup costs with the profits you’ll be earning).

Word

Write up a flyer for your business proposal ‘presentation’ that will show off the key details of your proposal. Why would someone want to invest in your idea? Include charts, images, a logo, and descriptions of your mission that goes beyond the dollars and cents presented by your spreadsheet.

 

Tech Apps Business Proposal Project

Excel Skills List

Formulas:

Math:

+, -, *, /

=sum()

=sumif() [sums rows where a criteria is met], =sumifs(…) [allows multiple criteria]

=count(…)

=countif() [counts each row where a criteria is met], =countifs() [allows multiple criteria]

 

=average()

String Manipulation:

=left(), =right(), =mid(), =len() [Gets the length of a string],

=find(…) [returns the location of a string wtihin another string, like finding the @ in an email address]

 

—we got distracted, so we’ll stop there.

Pivot Tables

Today we did something awesome.  Sam said that, and Nick says to just leave it at that.  Nonetheless…

 

Select the data you want, go to Insert (Windows) or (Data) and select Pivot Table.

Then, in the builder, you can select where you want it to find the columns, the rows, and what it should do for the related values.  For instance, you might drag ‘team’ to the column, ‘vendor’ to the row, and ‘Price’ to the values field.  This would give you the amount spent at each vendor for each team along with totals for each vendor, each team, and the whole table.

 

See, awesome, right?

 

Happy Halloween.  Bring your leftover candy to Mr. McLain.

 

Conditional Formatting and range lookup

Today we expanded our knowledge of Vlookup, to understand that “True” at the end is the default, and creates a range lookup.  In a range lookup, it returns whichever value is closest to the lookup value without the lookup going over the return value.

So, in a case like a grade range lookup, that works great. But if you’re looking for a specific value (service code, id number, that kind of thing), it can create some false results. In my experience, the last argument in a vlookup should almost always be ‘false’.

 

We also looked at conditional formatting.  Select the range you want to apply, and go to conditional formatting.  Set up the condition, and then set up the format you want to have as the result.  If you need to change or remove rules, go to the conditional formatting menu item, and select “Manage Rules”.

 

And we learned that pink is a weird color that should just be called ‘not green’.

 

Fake Tracking Numbers

Here are a bunch of falsified tracking numbers.  I’ve put a * before the service code, and the shipper numbers are of varying lengths.  Your job is to grab the two characters to the right of the *, and then use vlookup to figure out what the service name is.

 

Final2
1ZB121G*0171542491
1Z3SL3YG*0269313750
1ZZ5Q9VI*0384486378
1ZEH0PX8*8059923484
1ZWZXK58*RS35927971
1ZMQ4X6R*1A54072855
1ZW3Q4LS*0189714238
1Z7MG14P*0239681205
1ZVPLDMG*0357895285
1Z7OW0UV*0588872611
1Z1S0N08*RS30063854
1ZZ2L736*1A86069940
1ZYDROQB*B014386086
1ZS71B0S*0233742771
1ZIFN64*0389128007
1ZTGWR30*0574056592
1ZTCB52D*RS89374006
1Z1R5WHR*1A55092187
1ZZEHH0A*0124231013
1Z7XN5FY*Y025989683
1Z9JAXZN*0317682058
1ZXG0PY*0556828503
1Z2999FB*RS85214527
1ZKH42VG*1A20863370
1ZEMBBU2*0195394192
1Z6AJNC4*0267305926
1ZMMPXLI*I034584409
1Z5IKSYD*0578555384
1ZAI4TYN*RS32630585
1ZU5TYZS*1A88928113
1ZNTQCSK*0111710813
1ZABKHXT*0221789689
1ZM0KA2T*T038164541
1ZGFER30*0552629480
1ZYK132I*RS70961736
1ZXYZL9Z*1A80796527
1ZKQHWJA*0182425263
1ZUYY5T4*0241409611
1Z9BYG1H*0352650755
1Z7FO9LW*0569550599
1ZT3JG9P*RS11407954
1Z0VE7F9*1A92075099
1Z43HLY9*0123148529
1ZQGLWLC*0290424199
1Z61XSD7*0346174186
1Z4YQO67*0571405833
1Z8FC2KK*RS93932552
1Z04PD8M*1A35458789
1Z978IJ3*0188607616
1Z98W33C*0253657157
1ZKYLLR8*0384457576
1ZF3C1PZ*0564502914
1ZFGXLGL*RS18944256
1Z9ONWQI*1A17859341
1ZV4QO60*0180649090
1ZMYY9MR*0288283910
1ZQFIH8D*0365438804
1ZZYRR8M*0564523000
1ZT3I4XL*RS67952397
1Z1FRE3D*1A22126135
1ZOXNUH8*0104504315
1ZT6X9WR*0216557620
1ZRYUXTH*0383413868
1Z2B5YWM*0546198024
1ZGET0NC*RS59691484
1ZKESO4G*1A80817482
1ZCTGW82*0121651046
1ZO56AZS*0284760256
1ZQ6TRTA*0348497638
1Z92WJ0B*0511465144
1ZJRBQXX*RS98075207
1ZQQ32NP*1A18151621
1ZWZRZQZ*0155334033
1ZXWXCOA*0256456426
1ZDKFZ13*0383393325
1Z4XKQRP*0568758116
1ZLJQHDU*RS58653164
1ZMYSJA1*1A46037987
1ZMCY2RC*0195243432
1ZYYL7OS*S023235704
1Z4HQ325*0368917884
1ZPOG5FN*0597700962
1ZQUCOS5*RS92460460
1Z9HYAP3*1A58781685
1ZDKIJPQ*0182670917
1ZXAMR8I*0211372266
1ZHS11RB*0328753991
1ZJSHP1G*1G05624561
1Z3YCBEP*RS56368906
1Z9TLK7*1A65073207
1Z96HY50*0160750837
1Z7JYADR*0268819569
1ZIN6LJS*0314963747
1ZTERTWJ*0561521529
1ZGU1Y6H*RS86968612
1ZJ47YJ7*1A47708075
1ZHFD2S*0111990947
1Z790CX1*0270498849
1Z8TWNBT*T032848757
1ZXNQJCT*0535249322

Intro to String Manipulation

Today we started looking at manipulating strings in Excel.

Concatenating (combining) strings:

&

e.g. “Bob” & ” Brian” = “Bob Brian”

Left characters:

=left(text, number of characters)

e.g. =left(“Bob”, 1) results in “B”

Right characters:

Same as left, only with the word right.

e.g. =right(“Bob”, 2) results in “ob”

 

Middle characters:

=mid(text, start, number of characters)

=mid(“summer”, 3, 2) results in “mm”

And remember, mid does have some relevance, eve if it is not the fundamental truth from which all other truths derive (at least in this universe — we suspect).

 

 

Wednesday Resources

Sorry I couldn’t be with you all today.   Today’s lesson is focused on lying with charts.

Here’s an example from 2012.  Take a look at it and see if you can figure out how this chart perhaps misrepresents the data as it is labeled:  fbn-cavuto-20120731-bushexpire

 

Next, I want you to read through this:  https://cseweb.ucsd.edu/~ricko/CSE3/Lie_with_Statistics.pdf

 

And then, I want you to try it out with your advertising analysis.  Can you create charts that imply contradictory things.  For instance, can you provide one chart that shows women are underrepresented and one that shows they are overrepresented. No cheating, you have to use the numbers you actually found (if you want to add someone else’s numbers to your data to get a broader sampling, you may).