You have been given some financial data from a stationary company, they have recently expanded and are thinking of moving to accounting software instead of using Excel for their financial information. One of the accountants, Jade Golem has extracted the sales and purchases information for the first six months of 2021. You have also been given information on tax codes used, supplier and customer details.
a) Create an invoice that the accounts department can use for customer purchases. The invoice needs to have as much pre-populated information as possible such as address, VAT number, any discounts applicable etc. The invoice should also conform to HMRC VAT rules in terms of information available on the invoice.
b) Using the financial data given by Jade, prepare the VAT returns for Qtr 1 and Qtr 2, each box should be completed using formula which clearly show where the information has been collected from. If pivot tables are used they should be collated on one worksheet for reference.
c) As per the VAT returns created in part b, prepare a VAT control account, clearly showing the necessary figures for each quarter and any VAT owing, owed, paid or received.
d) Jade has been given a project to complete over the coming weeks, the sales manager has asked her to prepare an interactive table where reps can see how well they are doing against targets. They have currently been given a minimum sales value of £150 and per order and a minimum number of sales of 15 per month. The sales manager is also keen to have monthly sales data per region but that information hasn’t been collected so far and Jade is unsure how to produce figures from the extracts she has been given.
Using PowerPivot you must create a visual traffic light system which shows each rep against their targets for the month without disclosing any financial information, to be displayed in the company staff room. The sales manager has also requested a chart that shows which region is bringing in most sales without mentioning the sales rep related to that region (due to morale issues).
