Computer Applications for Business 4 l Last Week

Computer Applications for Business (4) l Last Week » Organizing your files » More Word Processing – Styles & Creating Templates – Inserting fields and tables l Let’s check that it all worked out 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 BS 1904 Week 4 1

Checkpoint 1: Can you do Word Styles? l l l 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 l 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 l 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” BS 1904 Week 4 2

Table stuff to try on your own l l l 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 l Try splitting and merging cells to get complex layouts * How do you display a new toolbar or edit contents? BS 1904 Week 4 3

Graphs in Excel l l 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? l 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. . . l It’s often easier to start again than to modify » For example if you don’t get the title cells included at first BS 1904 Week 4 4

Paste Special l So far, we’ve used the Paste button in Excel » Same as pulling down Edit and clicking Paste » If you copied a formula, that’s what gets pasted in l 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 you’re copying a snapshot of last year’s figures into accounts (for comparison) l 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 BS 1904 Week 4 5

DDE and Paste Link l l l 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 isn’t 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 you’ve linked – the link is dynamic For Paste Link to work properly, both source and client applications must implement Dynamic Data Exchange (DDE) BS 1904 Week 4 6

More about Excel l 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 l 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) BS 1904 Week 4 7

Excel Practical (2) Exercises on page 5 -6 of Practice. doc l Exercise 5 — Making a graph of the data l 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 with: http: //www. winchester. ac. uk/bm/courses/bs 1904 ss. xls but don’t try to edit it within Internet Explorer » Better still – follow the links from the BS 1904 home page l Exercise 7 — Dynamic Linking » Repeat the pasting to use the current value of a cell, not a historic snapshot BS 1904 Week 4 8

Checkpoint 2: Graphs l Did it all work on the Supermarket spreadsheet? When would you use stacked bars/towers? Did you have problems getting axis scales to display? l And a few theoretical points: l l » 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? BS 1904 Week 4 9

Properties and Fields l 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 l 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 BS 1904 Week 4 10

Inserting Fields l Example: building a footer » View the footer (View Header and Footer, or double-click if you’re 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 l To get the author name in, you’ll need to Insert Field » Author is a piece of “Document Information” » Can also do page and date that way, but it’s easier to use the buttons in the Footer dialogue box l Beware: Field updates are not shown immediately » Put cursor on one and press F 9 to update it BS 1904 Week 4 11

Inserting Cross-references l 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 l Bookmarks » » If the target isn’t 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 Product. Name produces whatever text was given that bookmark name BS 1904 Week 4 12

Cross-reference Exercise l Download http: //www 2. winchester. ac. uk/bm/document/Biz. Guidelines. doc l 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…” l l 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) BS 1904 Week 4 13

More Templates in Word l Templates are files in which you store: » Style definitions (last week) » Repeated text l l So it’s 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 BS 1904 Week 4 Your address heading goes here Email: G. Bloke@winchester. ac. uk Phone (01962) 820 123 2008 Space for Addressee name and address Dear , address style heading style Space for subject Yours sincerely, normal style Gary Bloke 14

Newsletters l l l 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 don’t need to make a separate section for the footer l Use of pictures breaks up text that might otherwise be boring BS 1904 Week 4 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, 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. taken above the chimney of Wilson’s factory. Wilson’s have consistently denied Rupert is undergoing treatment at Nutwood Royal Laundry Published each month by Arctophile Enterprises Page 1 15

Brochures l 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) l Sometimes it’s 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 BS 1904 Week 4 Revealed when you open Page 2 Back cover Front cover Page 3 Page 4 16

Layouts the Easy Way l Word lets you print 2 -up and 4 -up » » l Useful for saving trees Don’t 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 A 4? Your printer may do this for you through “Finishing” on the Printer’s Properties menu » What options are supported on your printer? » Some duplex printers support “Booklet printing” BS 1904 Week 4 17

Keeping Track of Changes l l 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 l Once you’ve 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 l Notice that the Reviewing Toolbar appears in Word XP l Find out how to alter the appearance of changes BS 1904 Week 4 18

Comparing and Merging Documents l What’s the difference between Doc 1 and Doc 2? » » l Open Doc 1 Select Tools / Compare and Merge to open Doc 2 Display is as if Doc 2 were the result of updating Doc 1 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 l In Word 2003, you can also display files side-by side » It’s an option on the Window menu BS 1904 Week 4 19

Electronic Mail l Simple principle, modelled on a (paper) post office » Sender uses program to edit an e-mail note file, consisting of an Address header (user@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 l Different servers have different behaviours » Most ISPs delete the file when you read it in » College system leaves the mail on the server BS 1904 Week 4 20

Hotmail and Googlemail l 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 l Benefits and drawbacks + Can be accessed from anyone’s browser anywhere + Reasonably private (your mail is not on ISP’s server) – Encourages long periods online (though you can write text first, then paste it in) – Only moderately easy to organize mail into folders BS 1904 Week 4 21

Assignment Workshop 1 Remember the structure: l l Executive Summary (what you want & why it’s worth doing) Analysis of the Business Case » What happens if we do nothing (Ba. U – Business as Usual) » What should happen if we go ahead » What is the DIFFERENCE l Requires working out » Ba. U cashflow » Expected cashflow if we go ahead (all costs, all incomes) » And comparing them l Also consider what could go wrong » Work out the cashflow in this contingency situation BS 1904 Week 4 22

Estimating Revenue and Costs l Best thing is to construct two spreadsheets with months going down the sheet » First is Business as usual – assume you don’t build » Second is what happens if you do build l l 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) l Business case depends on the Difference between sheets BS 1904 Week 4 23

Mortgages l 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 l l Your introduction to Excel’s Financial functions Ensure your sheet contains: » The principal (loan to be repaid – so it’s negative) » Number of periods (60 months) » Interest period (you can approximate to 1/12 of annual) Hint l Name all these cells l Now find a function to calculate the Pay. Men. T and put it in one of the cells that work out monthly mortgage payments BS 1904 Week 4 24

Summary of Week 4 l Checked & extended knowledge of Word Techniques » Improving templates to get helpful defaults » Exploiting fields » Cross-referencing l Excel: » Creating graphs, including working out which layout is most meaningful » Linking spreadsheets together l Assignment » Starting the Excel part to calculate benefits BS 1904 Week 4 25