files attached
Instructions Using the information provided in the Excel workbook of your Businesses - Source Documents provided by your teacher you need to complete the following Tasks, each task will need to be on a New Sheet in this Excel Workbook. You are to brand each spreadsheet up with your businesses name and address. You need to also ensure you use appropriate numbering formats on all the cells and that you use borders and create easy to read headers on rows and columns (Size 12 font & Bold). Ensure your spreadsheets looks professional, and only include the information the business owner has requested. Assessment Task 5 - Business Folio D Instructions Part 1 - Sales Invoice Template The owners would like to speed up the invoicing process for their sales staff, currently all invoices are hand written. You are required to create a Invoice template on a new worksheet titled "Tax Invoice" in excel based on their current invoice template image provided in your Business Source Docs. Create a copy of your Assessment Task 3 "Stock on Hand" worksheet into a new worksheet tab called "Stock Data" (It doesn't matter if this assessment is not yet mark or satisfactory, as long as it contains columns for Product Code, Item Name and a Unit Selling Price. These are the columns you need for your VLookup in this Assessment) You need to create your Invoice template on a new worksheet called "Tax Invoice Template" you will be linking between these 2 worksheets, so make sure you have them saved in the same excel file. The template needs to be designed so the Sales staff can reuse it. When staff enter product information, they should just have to enter the Product Code and Qty and the template will use a VLOOKUP on the spreadsheet you created in Assessment Task 3 to fill in the rest of the product information (Description & Sale Price). You also need to use IFERROR to prevent your invoice displaying #N/A or #VALUE! when rows in the template are blank. (Hint: All cells that use a formula like =Vlookup or do a calculation will need =IFError) You must include in the template a formula for all totals Included GST (Subtotal + Shipping & Handling needs to be divided by 11), Don't forget all prices already include GST. Shipping & Handling is calculated at 5% of the Subtotal of Sales Part 2 - Macro 1 - Setup Layout for Printing After you completed the invoice template you need to create a MACRO to do the following: ● Change the page size to A4 ● Orientation to Portrait ● Margins to Narrow ● Scale to Fit - 1 Page Wide and 1 Page High You will need to copy make a copy of the Macro Code into a worksheet called "Invoice Layout Macro" Run the macro and save a copy of the invoice as a PDF and upload to VUCollaborate also. Part 3 - Macro 2 - Clear Template After you completed the first MACRO you need to create a second MACRO to clear the contents of the Invoice, so you can enter a new sale, ensuring the following cells are cleared: • Sales Person • P.O. Number • Invoice Number • Customer Name, Address and Contact Details. • Product Code • Quantity You will need to copy make a copy of the Macro Code into a worksheet called "Clear Invoice Macro" NOTE: DO NOT FORGET FOLLOWING STEP. Once you have completed this step ensure you save the Workbook as a "Macro Enabled Workbook" Part 4 - Sorting & Saving Files You need to sort all your files on your memory stick into folders and rename files appropriately to demonstrate you know how to maintain your files and documents. Once sorted and renamed you must then screenshot using the Snipping Tool in Microsoft Windows to show the root Directory and the content of at least Three (3) folders, with final folder showing all your work for this unit bsbitu402-assess-5-v3-bkqbrvik.xlsx Instructions Using the information provided in the Excel workbook of your Businesses - Source Documents provided by your teacher you need to complete the following Tasks, each task will need to be on a New Sheet in this Excel Workbook. You are to brand each spreadsheet up with your businesses name and address. You need to also ensure you use appropriate numbering formats on all the cells and that you use borders and create easy to read headers on rows and columns (Size 12 font & Bold). Ensure your spreadsheets looks professional, and only include the information the business owner has requested. Assessment Task 5 - Business Folio D Instructions Part 1 - Sales Invoice Template The owners would like to speed up the invoicing process for their sales staff, currently all invoices are hand written. You are required to create a Invoice template on a new worksheet titled "Tax Invoice" in excel based on their current invoice template image provided in your Business Source Docs. Create a copy of your Assessment Task 3 "Stock on Hand" worksheet into a new worksheet tab called "Stock Data" (It doesn't matter if this assessment is not yet mark or satisfactory, as long as it contains columns for Product Code, Item Name and a Unit Selling Price. These are the columns you need for your VLookup in this Assessment) You need to create your Invoice template on a new worksheet called "Tax Invoice Template" you will be linking between these 2 worksheets, so make sure you have them saved in the same excel file. The template needs to be designed so the Sales staff can reuse it. When staff enter product information, they should just have to enter the Product Code and Qty and the template will use a VLOOKUP on the spreadsheet you created in Assessment Task 3 to fill in the rest of the product information (Description & Sale Price). You also need to use IFERROR to prevent your invoice displaying #N/A or #VALUE! when rows in the template are blank. (Hint: All cells that use a formula like =Vlookup or do a calculation will need =IFError) You must include in the template a formula for all totals Included GST (Subtotal + Shipping & Handling needs to be divided by 11), Don't forget all prices already include GST. Shipping & Handling is calculated at 5% of the Subtotal of Sales Part 2 - Macro 1 - Setup Layout for Printing After you completed the invoice template you need to create a MACRO to do the following: ● Change the page size to A4 ● Orientation to Portrait ● Margins to Narrow ● Scale to Fit - 1 Page Wide and 1 Page High You will need to copy make a copy of the Macro Code into a worksheet called "Invoice Layout Macro" Run the macro and save a copy of the invoice as a PDF and upload to VUCollaborate also. Part 3 - Macro 2 - Clear Template After you completed the first MACRO you need to create a second MACRO to clear the contents of the Invoice, so you can enter a new sale, ensuring the following cells are cleared: • Sales Person • P.O. Number • Invoice Number • Customer Name, Address and Contact Details. • Product Code • Quantity You will need to copy make a copy of the Macro Code into a worksheet called "Clear Invoice Macro" NOTE: DO NOT FORGET FOLLOWING STEP. Once you have completed this step ensure you save the Workbook as a "Macro Enabled Workbook" Part 4 - Sorting & Saving Files You need to sort all your files on your memory stick into folders and rename files appropriately to demonstrate you know how to maintain your files and documents. Once sorted and renamed you must then screenshot using the Snipping Tool in Microsoft Windows to show the root Directory and the content of at least Three (3) folders, with final folder showing all your work for this unit bsbitu402-assessment-task-2-business-folio-a-chozin-maitland-s8024311-ase4wa3h.xlsx Instructions Using the information provided in the Excel workbook of your Businesses - Source Documents provided by your teacher you need to complete the following Tasks, each task will need to be on a New Sheet in this Excel Workbook. You are to brand each spreadsheet up with your businesses name and address. You need to also ensure you use appropriate numbering formats on all the cells and that you use borders and create easy to read headers on rows and columns (Size 12 font & Bold). Ensure your spreadsheets looks professional, and only include the information the business owner has requested. Assessment Task 2 - Business Folio A Instructions Part 1 - Cash Receipts Tracking Spreadsheet The owners of the store have asked you to create a "Cash Receipts Tracking" spreadsheet so they can better track the money coming into the business from sales. The column headers they require on the spreadsheet is as follows: • Date of Sale • Receipt Number • Details of Sale • Payment Type • Accounts for Tracking Sales • Debtor Control or Account Receivable (This is for receipts from someone that we invoiced and they pay later) • Service Fees • Sales Revenue • GST Collected • Total Received • Totals for all columns (These cells also need to be a SUM calculation) • Using the source documents provided for your business enter all the Cash Sales you have for June. Here is a rough example from the owner as to how they would like it laid out. (Make sure you make it presentable and use appropriate formatting, colours, fonts, borders and styles in your version) Part 2 - Using Help Using the Microsoft Excel Help system search for "Resizing a Cell" click on the: Get Help on "Resizing a Cell" Help will display a list of different options choose "Change the column width and row height" option from the results page. Using the windows snipping tool take a screenshot of the results and save it to a new spreadsheet titled "Using Help" Business Folio A Kennedy Tax and Business ServicesCash Receipts Tracking 4/207 Barkly St, Footscray VIC 3011 (03) 9939 6480 ABN: 55 635 329 523 DateRec. no. DetailsPayment TypeDebtors controlSales RevenueService FeesGST CollectedTotal ReceivedTotal Received 6/5/19176Service FeesCash$ 1,500.00$ 150.00$ 1,650.00$ 1,650.00 6/7/20177Service FeesEFTPOS$ 800.00$ 80.00$ 880.00$ 880.00 6/10/20178Payment of AccountHRM Pty Ltd$ 960.00$ - 0$ 960.00$ 960.00 6/15/20179Service FeesCash$ 200.00$ 20.00$ 220.00$ 220.00 6/17/20180Payment of AccountPaul Smiths$ 1,320.00$ - 0$ 1,320.00$ 1,320.00 6/20/20181Service FeesEFTPOS$ 300.00$ 30.00$ 330.00$ 330.00 6/21/20182Product SalesCash$ 250.00$ 25.00$ 275.00$ 275.00 6/24/20183Product SalesCash$ 200.00$ 20.00$ 220.00$ 220.00 6/28/20184Service FeesEFTPOS$ 600.00$ 60.00$ 660.00$ 660.00 6/29/20185Product SalesCash$