XYZ Manufacturers manufacture genuine leather travel bags. The factory is located in KwaZulu Natal (KZN) and the bags are distributed across the country by means of traveling sales representatives. You are the newly appointed accountant of this registered VAT vendor.
XYZ Manufacturers was established in the first quarter of the year 2012 and has been operating for five (5) full financial years since its inception. Due to the fact that business was booming almost from the first day, accounting records were not considered a high priority.
After sifting through the archives, emails, and source documents you decided to compile a spreadsheet to show the most important cash flow results for the years 2012 to 2016.
You obtained the following information from management to take into consideration in your spreadsheet.
Value Added Tax (VAT) percentage 14% (refer cell B4)
The machinery used in manufacturing was bought on the first day of 2012 when the operation commenced. The cost price of the machinery on the transaction date was R1,687,500 including VAT (refer to cell B7). To finance this transaction, a loan was acquired from the bank based on the terms below :
• Finance period/term is 5 years (refer cell B9)
• Annual Interest rate 10.35% (refer cell B10)
• Equal annual payments are made at the beginning of each year (refer range B23:F23)
• The loan is fully settled after the five year period (end of 2016)
• Sales commission is paid annually to all sales representatives. The sales representatives earn a commission at a rate of 2.5% (refer to cell E4) when less than 50,000 have been sold in a year and 2.75% (refer cell E5) when 50,000 or more units have been sold in a year. Sales commission per year is calculated by multiplying the total sales amount (refer range E18:F18) with the mentioned rates.
• The annual sales target is an average of 40,000 units (refer cell E8)
• Number of units sold per year for 2012 to 2016 (refer B13:F13). Production is based in orders, resulting in the number of units produced always being equal to the number of units sold.
• The mark-up margin per year for 2012 to 2016 (refer range B14:F14)
• Cost of production (refer range B19:F19) is calculated based on the mark-up margin
• The sales price per unit excluding VAT (refer range B15:F15)
• The factory rental amount per year is R35,000 (refer range B24:F24)
OPEN PDF HERE
Please login to get access to this quiz
