Computer Applications for Business Week 5 Spreadsheet Applications

Computer Applications for Business Week 5 Spreadsheet Applications 1

Business Application Packages l Last Week » Introduction to Spreadsheets » “What if” models l This week – More on Spreadsheets [Knight (1999) Ch. 5] » “What if” models – Choosing a company car – Assessing the Business Case for an Investment » Useful Excel functions – IF, LOOKUP – A currency conversion example 2

Handling Numeric Data l Last week we looked at spreadsheets as a way of recording and modelling business finances l 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 it’s undesirable » Create a simple graph to display data l Let’s now apply these ideas to business problems 3

“What If” models l Consider making a choice of Company Car (we should be so lucky!) l Will depend on: » monthly leasing cost, including extras » how much of this the Company pays » Tax payable (depends on list price of car) » Fuel costs (depend on mileage and thirst) l We can use published price and fuel economy data » Plug in assumptions about distance covered » Evaluate costs with and without options l Then vary some of the assumptions » Mileage, fuel cost, tax rates 4

Example from 1995 l Personal Use Charge is: l » Monthly Cost of Ownership » minus allowance l Extras: » Add to taxable price » Increase Co. O l l Allowance Tax payable is percentage of value less PUC Gives cost to keep car Add fuel to compute total cost Fuel Price 5

Putting it together The only extras I cared about Private Use Charge + Tax + Fuel Then vary mileage, fuel price, tax assumptions. . . 6

Making an Investment Decision l Business consist largely of taking calculated risks with the expectation of their being profitable » How do we calculate the risk? » And the potential gains to be made? l A good guess is that “Tomorrow will be like today, unless somebody does something to make it different” » So project forward the “Business as usual” situation » And compare with result of making the change – Justify any assumptions – Cost out the impact of the investment » Set goal for the profit that will justify investment – Usually have “hurdle rate” below which it’s not worth the trouble of further investigation 7

Evaluate this Business Case l Your company issues its software on diskettes, and this is becoming a significant expense as the size of your product increases. l You ship 1000 packages a week, and have been offered a CD-burner that can handle all your production requirements in 3 hours a day, but it costs £ 15, 000. l The blank discs cost £ 1 each, making them only slightly cheaper than the six 20 p diskettes you currently use for each package. CD labels cost you 10 p each, against the 2 p for diskette labels. l Assume that you need someone dedicated to the machine for one hour each day, to set up a production run – this is the same labour cost currently incurred setting up to copy diskettes. Assume also that your current diskette-copier is fully-depreciated, but may start needing maintenance in the next year. l Work out the cost of producing the media component of each package. Do the savings obtained justify the proposed investment? What changes are likely to improve or degrade this business-case? 8

A Possible Approach Correct this error 9

An Aside on Dates l Newspapers think the “Year 2000” problem was about 2 -digit years in programs » Reasonably true for Mainframe Computers » Not a significant problem for PC users l Spreadsheets store dates as offset from 1900 » Unless you deliberately subvert this! » What you see is just a result of formatting l All these are different formats for “ 10/03/1999” » Exercise – Work out how to produce these formats 10

Excel Functions l We’ve already met SUM(range) and AVERAGE(range) l Other useful functions are: » TODAY() gives today’s date » NOW() gives current date and time (useful on print-out) » WEEKDAY(somedate) gives day in range 1: 7 (Sun=1) » WEEKDAY(somedate, 2) gives day in range 1: 7 (Mon=1) l Logical Functions: » IF(test, value-if-true, value-if-false) for example IF(2=(WEEKDAY(TODAY())), "BS 1904", "Other") 11

Questions ? 12