2014 S01 P03 EXCEL NO EXCEL PART 3

Use the provided information and answer the following questions:

Note: Where it is indicated that your formula will be copied to other cells, your formula must take absolute and relative addresses into account, but only where necessary!

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 84)

d) The available workdays in a month are 20 days (refer to cell B6)

e) Available information per machine

• Purchase price excluding VAT (refer to row 11) and the monthly installment 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 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 installment 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 installment, the amount is charged and for all other machines, a monthly maintenance cost of 9% of the monthly installment 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.

The following spreadsheet has been compiled by you:

OPEN PDF HERE

Please login to get access to this quiz
s2Member®