Computer Applications for Business 4

1
Computer Applications for Business (4)

• Last Week
• Organizing your files
• More Word Processing
• Styles Creating Templates
• Inserting fields and tables
• This week
• Excel Practical graphs, Linking data between
  sheets
• Checkpoints Graphs Styles Inserting fields
  and symbols
• Cross-references
• Newsletters and brochures
• Keeping track of changes
• Assignment Workshop

Lets check that it all worked out
2
Checkpoint 1 Can you do Word Styles?

• Start by getting Normal style right
• Do this with Format / Styles and Formatting
• Choose font, size, Tab positions and Paragraph
  formats
• Then fix up the Headings to your taste
• Build new styles as required
• For example ordered and unordered lists,
  table-cells
• Easy way to create a new style
• get a paragraph to be what you want, select it,
  then give it a name in the Style box of the
  formatting toolbar
• To bring an existing style into line with your
  selection
• click down-arrow on the style you want to change
• and select Update to Match Selection

3
Table stuff to try on your own

• Create full-width tables with Insert Table (or
  use a button on the Table and Borders toolbar)
• Adjust column width by dragging with the
  mouse,or use the Table menu to adjust things
• You may need some new styles to
• Avoid justifying text in cells of table
• Centre the data in some cells
• Provide Column- and Row-Headings
• Try splitting and merging cells to get complex
  layouts How do you display a new toolbar or
  edit contents?

4
Graphs in Excel

• Often the most convincing way to show information
• Though you can use it to mislead
• Always suspect charts with the origin missing!
• 3-D bars make it hard to compare their height
• Any more such tricks?
• Excel makes it easy to build charts from tables
• Just select the range you want to plot
• Then use the chart wizard
• Experiment with changing scales, legends,
  layout...
• Its often easier to start again than to modify
• For example if you dont get the title cells
  included at first

5
Paste Special

• So far, weve used the Paste button in Excel
• Same as pulling down Edit and clicking Paste
• If you copied a formula, thats what gets pasted
  in
• Paste Special lets you select what is pasted
• For example, the value the source cell currently
  contains
• Or the format or everything except the borders
• Typically used when youre copying a snapshot of
  last years figures into accounts (for
  comparison)
• Paste Link (a button under Paste Special)
• Instead of pasting in a snapshot, it creates a
  dynamic link to the source cell(s)
• Lets you make use of the latest values of the data

6
DDE and Paste Link

• When you Copy to the clipboard, the application
  saves more than just the characters, it also
  records source of the area copied (name, type of
  the source program, a coded form, and sometimes
  a pictorial form as well)
• This isnt used much within a document, but is
  vital when you Paste to other document types
• Using its knowledge of source document type,
  target can do appropriate conversions (e.g.
  number to character)
• When you Paste Link, the receiving application
  uses the name, and requests the source to provide
  the current value of what youve linked the
  link is dynamic
• For Paste Link to work properly, both source and
  client applications must implement Dynamic Data
  Exchange (DDE)

7
More about Excel

• Note spreadsheet is divided into sheets
• Three by default when you create a file
• Right-click on the tab to see what you can do
  with a sheet
• Move or copy a sheet, rename it
• Helps you keep related spreadsheets together
• For example, a Tax computation
• Objective is to print out a sheet to summarize
  Interest and Dividend income
• Create sheet for interest (one line per account,
  plus totals)
• Each line contains details of the account, plus
  payment date, gross interest, tax deduction, net
  interest received
• And a sheet for dividends (one line per payment,
  plus total)

8
Excel Practical (2)

• Exercises on page 5-6 of Practice.doc
• Exercise 5 Making a graph of the data
• Exercise 6 Multiple Sheets
• Covers operations on sheets within the file
• Date formats
• Pasting between sheets (static)
• To save you typing in all the data, start
  withhttp//www2.winchester.ac.uk/bm/courses/bs19
  04/bs1904ss.xls but dont try to edit it within
  Internet Explorer
• Better still follow the links from the BS1904
  home page
• Exercise 7 Dynamic Linking
• Repeat the pasting to use the current value of a
  cell, not a historic snapshot

9
Checkpoint 2 Graphs

• Did it all work on the Supermarket spreadsheet?
• When would you use stacked bars/towers?
• Did you have problems getting axis scales to
  display?
• And a few theoretical points
• What does a pie-chart show?
• How do you choose between a line-graph and a
  tower or bar chart?
• How do you emphasise small differences in large
  numbers
• and how do you minimize them?

10
Properties and Fields

• File Properties let you define document
  attributes such as Author, Title, Subject
• Some show up when you hover over name in
  Explorer
• And act as Fields you can insert into the text
• Properties also shows statistics such as
  word-count, and how long you spent editing the
  document
• Other Fields are automatically defined, for
  example
• Page number, document name (freddo.doc), date,
  time
• Particularly useful for insertion in text of
  footer
• Many of these will be useful if you build a new
  document from the current one
• Just change the Title and Subject properties
• Other stuff will have changed automatically

11
Inserting Fields

• Example building a footer
• View the footer (View Header and Footer, or
  double-click if youre in Page View)
• Insert author name on left of footer
• Tab to the middle and add the page number
• Tab to right and type Printed on followed by
  date
• To get the author name in, youll need to Insert
  Field
• Author is a piece of Document Information
• Can also do page and date that way, but its
  easier to use the buttons in the Footer dialogue
  box
• Beware Field updates are not shown immediately
• Put cursor on one and press F9 to update it

12
Inserting Cross-references

• Used when you want to refer to another part of
  the document such as see Chapter 2 Making a
  Fortune
• Insert / Reference / Cross-Reference
• You can choose headings, numbered items etc as
  targets
• And select what is to appear in the text
• Bookmarks
• If the target isnt one of these items, you can
  bookmark it
• Rather like HTML name, you attach a name to some
  text
• The name cannot include spaces
• Can use it as a way of creating symbolic
  names,for example, every reference to
  ProductName produces whatever text was given that
  bookmark name

13
Cross-reference Exercise

• Download http//www2.winchester.ac.uk/bm/document/
  BizGuidelines.doc
• Insert a new cross-reference into your copy
• Find Also see information at Leeds Metro and
  Sheffield Universities (it should be on p.11)
• Change it to read Also see xxx and information
  at Leeds Metro and Sheffield Universities where
  xxx is the reference
• Your reference is to the heading
  Acknowledgements so should appear as Also see
  Acknowledgements on p.16 and information
• Note that you need to put the text and
  page-number part of the reference in separately
• Challenge See if you can add a hyperlink on p.7
  to the Academic Regulations on the Portal (in
  Public Documents)

14
More Templates in Word

• Templates are files in which you store
• Style definitions (last week)
• Repeated text
• So its useful to have a template for your
  letters
• Easy to make
• File Page Setup for margins
• Type in the fixed text
• Define the styles you need
• As shown, plus lists etc
• Provide page-number footing (but not on page 1)
• Save As document template, for example letter.dot

Your address heading goes here
Email G.Bloke_at_winchester.ac.uk Phone
[phone]
2008
Space forAddressee nameand address
Dear
Dear ,
Space for subject
Yours sincerely, Gary Bloke
15
Newsletters

• Generally has a Masthead across the front page
• Then multiple columns, newspaper-style
• Word lets you divide a document into Sections,
  and format each differently in this example
• Section 1 is single-column for the masthead
• Section 2 is two-column
• You dont need to make a separate section for the
  footer
• Use of pictures breaks up text that might
  otherwise be boring

Bear-fanciers News
March 2007 Volume 3
Issue 3
Sooty Rupert There have been many sightings of
Rupert at various altitudes since the Professor
developed his new ultra-rising yeast, but few as
strange as that shown in this photograph, tak
en above the chimney of Wilsons factory.
Wilsons have consistently denied
that their smoke-stack causes pollution, and
the Brown family has long sought evidence that it
does. Managing director Magnus Fumare will appear
in Nutwood Magistrates court next Tuesday, under
charges laid by the Environment Agency. Rupert is
undergoing treatment at Nutwood Royal Laundry
Published each month by Arctophile Enterprises
Page 1
16
Brochures

• Example is tri-fold flyer
• Set page layout to Landscape
• Divide page into three columns (fine-tune them so
  you can fold the brochure)
• pour text into each page of the brochure,
  ending with a column break (Insert menu)
• Sometimes its easier to lay out the design using
  a table
• Define both width and height of each logical page
• Have full formatting power within these cells

Revealed when you open
Back cover
Front cover
Page 2
Page 4
Page 3
17
Layouts the Easy Way

• Word lets you print 2-up and 4-up
• Useful for saving trees
• Dont have to worry about scaling, margins etc.
• But you may need to worry about sequence of pages
• What order do you need for a 4-page booklet
  folded from one sheet of A4?
• Your printer may do this for you through
  Finishing on the Printers Properties menu
• What options are supported on your printer?
• Some duplex printers support Booklet printing

18
Keeping Track of Changes

• Valuable facility when working with others on a
  report
• In Word, Tools/Track Changes lets you
• Highlight changes to the text as you make them
• Preserve deleted text (with or without showing
  it)
• Relate changes to the user making them
• Once youve tracked the changes, you can
• Accept or reject them to create a revised base
  document
• Print with or without showing the changed text
• Either way, you can print the revision bars
• Notice that the Reviewing Toolbar appears in Word
  XP
• Find out how to alter the appearance of changes

19
Comparing and Merging Documents

• Whats the difference between Doc1 and Doc2?
• Open Doc1
• Select Tools / Compare and Merge to open Doc2
• Display is as if Doc2 were the result of updating
  Doc1
• You can accept or reject those changes with the
  Toolbar
• Very useful when several people working on
  document
• For example, in Group projects later in your
  course
• Word will often spot that documents have a
  common ancestor, and offer to merge them for you
• In Word 2003, you can also display files side-by
  side
• Its an option on the Window menu

20
Electronic Mail

• Simple principle, modelled on a (paper) post
  office
• Sender uses program to edit an e-mail note file,
  consisting of an Address header (user_at_domain)and
  some contents (subject and message text)
• Signs on to a Mail server, and transmits the file
  to server
• Server examines address header forwards file
  across the Internet to mail server in domain
  given in header
• Receiving mail server waits for named user to
  request incoming mail, then sends the file
  containing e-mail
• Different servers have different behaviours
• Most ISPs delete the file when you read it in
• College system leaves the mail on the server

21
Hotmail and Googlemail

• Many web-sites offer free e-mail to get you to
  view their sites (usually containing advertising)
• Examples are Hotmail.com (Microsoft),
  Unforgettable.com, and various search-engine
  sites
• You compose on-line, creating mail-file directly
  on server
• Server routes your file to target domain
  mail-server
• Incoming mail stays on Hotmail server view it
  online
• Benefits and drawbacks
• Can be accessed from anyones browser anywhere
• Reasonably private (your mail is not on ISPs
  server)
• Encourages long periods online (though you can
  write text first, then paste it in)
• Only moderately easy to organize mail into
  folders

22
Assignment Workshop 1

• Remember the structure
• Executive Summary (what you want why its worth
  doing)
• Analysis of the Business Case
• What happens if we do nothing (BaU Business as
  Usual)
• What should happen if we go ahead
• What is the DIFFERENCE
• Requires working out
• BaU cashflow
• Expected cashflow if we go ahead (all costs, all
  incomes)
• And comparing them
• Also consider what could go wrong
• Work out the cashflow in this contingency
  situation

23
Estimating Revenue and Costs

• Best thing is to construct two spreadsheets with
  months going down the sheet
• First is Business as usual assume you dont
  build
• Second is what happens if you do build
• You can omit building costs because these are
  funded by the mortgage this means mortgage
  payments do have to be included
• For each month, include
• Average number of rooms occupied
• Income obtained from them
• Cost of mortgage (for the do it case)
• Business case depends on the Difference between
  sheets

24
Mortgages

• A mortgage is an instrument that allows you to
  pay interest and pay off a debt with constant
  payments
• At the start, most of the payment is interest
• By the end, most of it is repayment, as you owe
  less
• Your introduction to Excels Financial functions
• Ensure your sheet contains
• The principal (loan to be repaid so its
  negative)
• Number of periods (60 months)
• Interest per period (you can approximate to 1/12
  of annual)
• Name all these cells
• Now find a function to calculate the PayMenT and
  put it in one of the cells that work out monthly
  mortgage payments

Hint
25
Summary of Week 4

• Checked extended knowledge of Word Techniques
• Improving templates to get helpful defaults
• Exploiting fields
• Cross-referencing
• Excel
• Creating graphs, including working out which
  layout is most meaningful
• Linking spreadsheets together
• Assignment
• Starting the Excel part to calculate benefits