Task 1.0

This worksheet will be used to solve a simple business problem a buy or lease decision.

MegaWidget International needs a heavy duty laser printer for the busy Sales department. The company has a three-year replacement cycle for all ICT equipment.

One supplier has recommended buying a new printer for 10,000. The printer will cost around 800 per year to maintain and will have a second-hand (scrap value) of 1000.

Exercise is about using Microsoft Excel to do these tasks

A second supplier recommends leasing the same model of printer for an annual cost of 1,500. Print costs will be 0.07 per print, assuming a volume of 50,000 copies per year.

1. Create a simple worksheet to calculate the cost of buying versus the cost of leasing.

Task 1.1

1. If necessary, modify the worksheet so that a user can alter any variable and see the effect on the results.

2. If necessary, change the layout of the worksheet so that information is presented logically and clearly. On financial statements, for instance, costs are often listed as fixed and variable costs.

3. If necessary, change the presentation of the worksheet so that important information (e.g. totals) is highlighted. Use colour, shading, different type faces, borders and other effects to improve the appearance of the worksheet.

Task 1.2

1. Use your worksheet to answer the following questions. Dont forget to reset the values in the worksheet before answering each question.

• What must the cost per page become before leasing is less expensive than buying? (Calculate this to the nearest penny)

• What must the expected usage become before leasing is less expensive than buying? (Calculate this to the nearest thousand)

• If maintenance costs double, does buying become more expensive than leasing?

• What happens if the planning period becomes 5 years?

Task 1.3

Create a payback table showing the cost of leasing/buying in relation to usage. Your table will have a minimum of three columns total annual usage, total cost of leasing and total cost of buying.

The second-hand value of the printer will vary according to total usage. At 50,000 copies per year, the printer will have an end-of-service value of 1000. This will decrease by 1.00 for each extra 1000 copies produced. When the value of the printer drops below 200, it will be considered to have no remaining second-hand value (i.e. it becomes zero). Introduce this change to your worksheet.

Change the cost of printing to 0.04 per page.

1. Use your table to find the breakeven point (to the nearest thousand copies), where the cost of purchasing the printer becomes equal to the cost of leasing. In other words, how many copies can be produced before leasing becomes a more expensive option than buying?

In order to complete the task, you should produce figures showing the difference between leasing and buying (i.e. total lease cost total buying cost). If you wish, you can add an extra column to the worksheet to show this information.

2. Optional task: can you modify the worksheet to calculate the exact number of copies to reach the breakeven point?

Task 1.4

1. Modify the worksheet so that the future value of money is taken into account for both buying and leasing the printer. Assume an interest rate of 20%.

• How does this new factor affect the decision? Use the original worksheet created for Task 1.0
• Hint: Use the FV function to handle this task.