You are the financial manager for GlasBot Pty Ltd, a registered VAT vendor GlasBot manufactures a range of glass perfume bottles.
GlasBot wants to invest in a new glass bottle-manufacturing machine. The production manager, Mr. Mashile has short-listed four machines. He asked your help in creating a spreadsheet calculating the monthly installment amount as well as extracting the downtime days and error margins from the given information.
Mr. Mashile provided you with the following information
a) The Value Added Tax (VAT) percentage is 14% (refer to cell B3)
b) All the machines will be financed using the same loan finance terms
• Interest is compounded monthly at the end of each month at an annual interest rate of 8% (refer to cell B4)
• The finance period is five (5) years (refer to cell 85)
c) The purchase price including VAT for each machine (refer to range B15:B19)
d) The residual value of each machine at the end of the five (5) year term (refer to range C15:C19)
e) Each machine has a specific UMDG scale code. A machine's UMDG scale code will fall within a specific scale bracket (refer to cell range A7 C13), which indicates the error margin percentage (%) at which each machine operates. For example, a machine with a UMDG code of 120 will fall in the scale bracket 100 to 199 and the machine will, therefore, have an error margin of 2 5%.
f) Each machine's name (refer to range A15:A19) has a specific structure. The machine name contains the UMDG scale code as well as the downtime code.
The machine name structure is as follows :
Characters 1-3 MAN
Character 4 Space
Character 5 X (machine configuration)
Character 6 - (hyphen/dash)
Character 7-9 UMDG scale code (also refer to point e)
Character 10 Downtime Code (also refer to point g)
g) Each machine has either a B or a C downtime code. The downtime code indicates the number of downtime days ie the number of days per month a machine will not be operational due to routine maintenance. Two (2) days downtime is indicated by a B and a C downtime code will be equal to one (1) day downtime.