Computer Applications for Business 6

1
Computer Applications for Business (6)

• Last Week
• Storing pictures and sounds
• Composite documents Integration of Excel and
  Word
• Business Cases
• Assignment Workshop everyone should have BaU
  model
• Checkpoint review of the module so far
• This week
• Review of Business Graphics graphical display
  of numeric data
• Excel What if models IF and LOOKUP functions
• Practical work Currency Conversion Exercise
• Assignment workshop

2
Review of the module so far

• Operational, Production and Management use of IT
• Communications and Decision Support
• Finding and using information
• Business Communications documents, letters,
  faxes, reports
• Psychology of business comms getting your point
  across
• Excel graphs and charts (to be reviewed today)
• Electronic Mail considerations
• Word-processing
• fonts, spelling checkers, grammar checkers, using
  styles
• Layouts and Tables Inserting things into your
  document
• Creating User Templates Letters, Reports,
  Newsletters, etc.
• Exploring a Business Case

3
The Rest of the Module

• Still to Come
• Excel What if models IF and LOOKUP functions
• Introduction to Databases
• Goals of a database Simple Works and cardfile
  DBs.
• Single-table Access Practical
• The Relational model Relating multiple tables in
  Access
• Mail-merge using the data with a word-processor
  
• Mail-merging with a spreadsheet or word-processor
  table
• Publishing on the Internet
• Anything else we should be covering?
• What changes are needed? Tell me after the
  break
• Or use the LN forum?

4
Handling Numeric Data

• We have looked at spreadsheets as a way of
  recording and modelling business finances
• You should already know how to
• Calculate results from a combination of values on
  the spreadsheet, using - / and the functions
  SUM and AVERAGE
• Copy and replicate cells
• Format numbers, money and dates
• Exploit the way that Excel adjusts row and column
  addresses when you copy formulae about.... and
  how to avoid it when its undesirable
• Create a range of graphs to display data
• We need to apply these ideas to business problems

5
Business Graphics

• Sometimes used to mean making presentations
• For example, PowerPoint or Freelance Graphics
• Covered by Mike Davies for Business Single
  Honours
• Well consider two main areas
• Representing numerical data graphically
• For example, with Excel charting tools
• This weeks focus
• Visualizing concepts
• Done with any drawing tools, including Microsoft
  Draw (included in Word, Excel and PowerPoint)
• For example flowcharts, data relationships

6
Forms of chart

• Bar chart
• Rectangular block with height proportional to
  value
• Excellent for presenting observations
• Can present multiple series of data by having
  several bars for each (x-axis) point
• Bars can be stacked, side-by-side or proportional
• Horizontal or vertical bars
• Line graph
• Traditional x-y plot
• Also supports multiple data series
• Scatter diagram used for irregular x-axis
  values
• Pie chart parts of a whole
• Visually clear, if rather space-consuming

7
Some Choices

• Vertical or horizontal bars
• Vertical implies good (up) and bad (down)
• Excellent for financial data
• Stick to horizontal when you want to avoid
  value-judgement
• Axis ranges
• Start y-axis at zero to show proportions
• Brand X is only half as good as we are
• Start higher to focus on absolute differences
• You could make Brand X look even worse by
  concealing the bottom third of the axis!
• Need to do this when tracking small changes in
  big values
• Colours will you be using a monochrome printer?

8
Choosing Chart Variants

• Lines and data-points can be
• Shown or omitted
• Points can show error-lines to indicate accuracy
• Three-dimensional bars
• Improves appearance at expense of clarity
• Useful when you want to avoid clear comparisons
• Exploded Pie
• Useful to put focus on a particular component
• (or to obscure)
• Pies can also have hollow middles (doughnuts)

9
An Aside on Dates

• Newspapers thought the Year 2000 problem was
  about 2-digit years in programs
• Reasonably true for Mainframe Computers
• Not a significant problem for PC users
• Spreadsheets store dates as offset from 1900
• Unless you deliberately subvert this!
• What you see is just a result of formatting
• All these are different formats for 9 March
  2003
• Exercise Work out how produce these formats for
  today

10
Excel Functions

• Weve already met SUM(range) and AVERAGE(range)
• Other useful functions are
• TODAY() gives todays date
• NOW() gives current date and time (useful on
  print-out)
• WEEKDAY(somedate) gives day in range 17 (Sun1)
• WEEKDAY(somedate,2) gives day in range 17
  (Mon1)
• Logical Functions
• IF(test, value-if-true, value-if-false) for
  exampleIF(3(WEEKDAY(TODAY())),"BS1904","Other")
• Table Look-up
• Given the table
• LOOKUP(WEEKDAY(TODAY(),2),A6G6,A7G7) returns
  Monday

or Tuesday if it is
11
More Complex Functions

• Right-click and use Save Target As to download
  feedback.xls from this modules home page on the
  Business site you can go straight there at
  http//www.winchester.ac.uk/bm/courses/bs1904/
• This is how your predecessors feedback was
  summarized
• Open the spreadsheet
• Say No when offered chance to Update links
• Look around E10 on the BS1904 sheet to see how
  the COUNTIF function can be used to analyse
  surveys
• Financial Functions
• Excel also contains a set of functions for
  Mortgages and Annuities (like the PMT function
  you need for assignment)
• Not very intuitive make sure you get reasonable
  results

12
Financial Functions

• Think what a mortgage is
• Fixed payment, usually monthly
• Payment covers interest, and pays off a bit of
  the loan
• So each month the interest falls a bit capital
  payment grows mainly interest at the start,
  capital by the end
• So we need to know
• Initial value of loan (and any left at the end)
• Number of payment periods
• Interest rate (per payment period)
• Size of payment
• If you know all but one, you can find the other
• PMT function lets you find PayMenT

13
Practical

• Currency Converter from Knight, as described in
  your handout
• Key issues
• IF and MAX functions
• Use of VLOOKUP
• Cell Protection
• Use of background shading to identify inputs and
  outputs
• When you can do this, you can achieve most Excel
  benefits

14
Module Assignment

• You should now be competent with
• Microsoft Word and Excel
• Laying out logical letters and reports
• Goal of assignment is to get apply your skills to
  a business problem
• Use Excel to explore a business case
• Build a report to sell your conclusions to
  management
• The report will be a composite document including
  a spread-sheet to show projected cash-flow
• You should by now all the skills needed to do it
  all
• Please raise now any areas of uncertainty

15
What If models

• Consider impact of choices or potential changes
• Such as mortgage interest rate
• Or contingencies like delays in the building
• Note Contingencies are not optionsthey are
  (usually bad) things you have to take account of
• Two approaches both are useful
• Vary some of the assumed values, such as
• Interest rate, total cost, sales volumes
• This lets you see what matters
• Produce a copy sheet and vary key aspects
• In assignment, a key one is duration of building
  work
• Others could be economic factors (anybodys guess)

16
Assignment Workshop

• Once you have Business as Usual revenue over 6
  years,Work out the equivalent revenue if project
  goes ahead
• For this you need to evaluate
• Impact of building period
• Mortgage payments (apply to 60 months)
• Reasonable occupancy percentages of the 140
  rooms(this is where you can apply some business
  judgement)
• Then compare revenues from Do it and BaU
• Only the extra revenue can justify the investment
• Cumulative extra revenue tells you when it breaks
  even
• Final cumulative extra is total gain from the
  project
• What does a graph of cumulative extra revenue
  look like?

17
Submitting your Spreadsheet

• Please look at the Assignment on the Learning
  Network,and read the notes on Electronic
  Submission
• Key points are
• File names must start with your account name
• followed by _ and the code for the module
  (BS1904)
• You can choose the rest of the name, for
  exampleF.J.Nerk_BS1904_fred1.xls
• If the files dont start with your account, the
  credit for your work could go to the wrong
  person
• You can try it out by creating a suitable file
  and submitting it as an answer to the Test of
  Prior Knowledge
• Well cover Turnitin (for the report) next week

18
Any More mid-semester Feedback?

• Or other matters to raise