ITECH1100 Automation of Business Process: Summit Veterinary Clinic Assessment Answer
The first table calculates weekly number of hours put by each worker in the lab. The second table provides all operating costs (salaries, utilities and fixed costs). While Col. D, E and F provide hourly, monthly and annual rates, respectively, as applicable. The blue shaded cells are input cells and can be changed easily.
- The total cost for a year is projected at $162,530.
- If the above annual total costs are divided by 12 as in cell E21 above, we can arrive at monthly costs for the lab. Hence, the lab will need a minimum income of $13,545 to cover the expenses.
If both vets cost $50/hr and lab remains closed on Wednesday, the weekly hours and rate/hour change as reflected in blue cells of the first table. In this case, the labour costs for the year (including replacement) are $115,816 and the total costs are $139,336.
Firstly, in the given data, a few columns have been added for ease:
- Profit: It is simply price minus expenses
- Month: Month number has been found from date using ‘MONTH formula’. Then, using ‘Vlookup formula’, month name has been found
- Day: Day has been found by using ‘TEXT (“dddd”) formula on the date
- Vet Name: Using ‘Vlookup formula’ on the day above, vet’s name has been found
Once this is done, pivot tables and graphs have been drawn to find trends as follows:
- Volume & Trends
- It can be seen that maximum number of consultations was in July (344) and October (338) while the minimum were in November (170).
- During the total period of analysis, majority of consultations are for cats (719) and dogs (671) with the minimum being for rabbits (87)
- During the total period of analysis, majority of consultations are checkups (740) with the minimum being for vaccination (277)
- It can be seen that the proportion of animal type treated is almost same between Anna and Beatrice.
- However, during the analysis period, Anna has been handling more number of consultations (1062) as compared to Beatrice (723). This is because Anna works three days a week while Beatrice works only 2 days a week.
- It can also be seen that in November, Anna has handled much fewer consultations (88). It seems that while Beatrice handles a much more similar number of requests every month, Anna’s number of consultations handled per month varies a lot.
- It can be seen that Anna has generated higher profit by charging higher average price ($270.1) as compared to Beatrice ($230.0). The overall average price is $253.9. Hence, while Anna charges above average, Beatrice charges below average.
- It can also be seen that Anna has higher average expenses/consultation ($121.1) as compared to Beatrice ($92.6). The overall average expense is $109.6.
- Despite higher expenses, Anna has higher average profit ($149.0) as compared to Beatrice ($137.3) while overall average profit/consultation is $144.3.
The quote was generated on basis of average price for each type of animal and consultation type through use of pivot table:
Additionally, Quote was generated by using a combination of INDEX and MATCH formulae on the pivot table.
Data Validation using list was applied to columns of ‘Animal Type’ and ‘Consultation Type. Also, Vlookup formula was used to generate Vet name:
For Profit column, a formula was added so that it will be generated automatically once expenses are entered.
So, the green columns are auto generated and ‘Animal Type’ and ‘Consultation Type’ have a dropdown list. The only manual entries are ‘Date’ and ‘Expense’.
Above is the screenshot of spread sheet created. Sample data has been entered to reflect the output.
Swimline diagram is as follows:
The process has been simplified as above. As soon as a customer comes, only animal type and consultation type has to be selected from the spread sheet dropdowns and quote will automatically be reflected. Once customer accepts the quote, animal can be treated by the vet and billed/quoted amount can be collected. To close the process, only date and expense amount needs to be entered in the spread sheet.