You are needed to develop working solutions in Excel which will manage the annual budget, current month’s inventory, and a list of the current month’s transactions for a company of your choosing. Your solutions must use formulas and also functions specifically demonstrating use of “sum”, “if”, and “vlookup”. You should as well demonstrate appropriate use of filtering, sorting, referencing and formatting where suitable.
The solution must illustrate the following:
Correct use of both formulas and functions such as “sum”, “if”, and “vlookup” must be displayed. Absolute, relative, and mixed referencing and also filtering, sorting and formatting should as well be used where suitable. Charts and graphs must be employed to give visual representation of the data, where suitable.
Budget:
Make an annual budget in an Excel spreadsheet for the company that details projected expenses and income for the year. The expenses must comprise: salaries/staff, rent/lease, telephone/internet, business rates, loans, electricity, gas, water, insurance and stock items. The projected income, taxes, and profit as well as average, minimum and maximum expenses for the year should as well be displayed by using formulas and functions.
Inventory:
The inventory spreadsheet must display a detailed list of items offered by the company and must monitor the number of items left in stock for the current month. It must as well comprise the “sum” function also as two other suitable functions.
Transactions:
The transactions spreadsheet must provide a detailed display of the current month’s transactions or sales taken out for the company. It must as well comprise the “sum” function and also two other suitable functions.