2014 S02 P03 EXCEL

GlasBot's production manager, Mr Mashile, was so impressed with the spreadsheet you have created for him previously that he asked you for further help with the new machine investigation.
Mr Mashile asked you to create a spreadsheet that will calculate the estimated net profit per machine taking the downtime days and the error margins into account.

Mr Mashile provided you with the following information

a) All the amounts are exclusive of Value Added Tax (VAT)
b) The cost price per unit excluding VAT is R15 per unit (refer to cell B3)
c) The mark-up margin is 50% (refer to ceil B4)
d) The available workdays in a month is 20 days (refer to cell B6)
e) Available information per machine

  • Purchase price excluding VAT (refer to row 11) and the monthly instalment amount (refer to row 19)
  • The error margin % (refer to row 9) is the percentage of flawed bottles produced. The error margin is expressed as a % of the total quantity of bottles produced by each machine per day.
  • Flawed bottles cannot be sold.
  • The total quantity of bottles that each machine can produce per day before the error margin is taken into account (refer to row 12)
  • The saleable quantity (refer to row 13) is calculated as the quantity produced per day reduced by the quantity of flawed bottles.
  • Downtime days per month (refer to row 10) are the number of days in a month the machine will not produce bottles due to planned maintenance.
  • Productive workdays per month (refer to row 14) are the available workdays less the downtime days per month ie the number of days in a month the machine will produce bottles.
  • f) The mark-up amount calculation assumes that all the saleable quantities will be sold.
    g) The monthly maintenance cost is calculated as a percentage of the monthly instalment amount. For all machines with a purchase price (excluding VAT) of R5 million or more a monthly maintenance cost of 11.5% of the monthly instalment, amount is charged and for all other machines, a monthly maintenance cost of 9% of the monthly instalment is charged.
    h) Each machine's monthly operational cost (refer to row 21) calculation is based on the monthly operational cost per unit (excluding VAT) of R560 (refer to cell B5), the total quantity produced per day and productive workdays.


    Please login to get access to this quiz