My Assignment Help

ITECH1100 Automation of Business Process: Summit Veterinary Clinic Assessment Answer

ITECH1100 Understanding the Digital Revolution

Assignment - Summit Veterinary Clinic


For this individual assignment, you will use skills acquired through practical laboratory exercises to automate a business process, and to visualize the impact of the automation.

You should use Microsoft Excel (or equivalent open-source software) for this assessment task.

Important: This assignment specification is generated just for you, 30383974. Do not distribute this specification. If your personal specification is made publicly available online, academic misconduct charges may be applied.

Validation code 4c0f6

Timelines and Expectations

Percentage value of task: 15%

Learning Outcomes Assessed

The following course learning outcomes are assessed by completing this assessment:

A1. Prepare a basic solution to a business problem;

A2. Select appropriate IT solutions for business functions;

A3. Apply business information software for data visualization and analysis purposes.

S1. Write basic programming logic;

S3. Interpret and construct representations of business data flow and processes;

K8. Outline the basic principles of programming.

Assessment Details

For this assignment, you will complete the following set of tasks using Excel, and create a report to describe your work.

Ensure you submit Excel files created for ALL tasks, along with your report. You may use an ePortfolio, a Word document or a PDF for your report submission.

Scenario details

The following scenario is fictional, and not based on any real veterinary clinic. It is also highly simplified, and should not be used as a basis for a real business management system.

Summit Veterinary Clinic is a small veterinary clinic, working with domestic companion animals. Mostly they work with dogs, cats, and smaller animals such as hamsters and rabbits.

Summit Veterinary Clinic is run by a team of two veterinarians, Anna and Beatrice, and one administrative staff member, Courtney.

Team hours

Most of the time, team members work the following shifts at Summit Veterinary Clinic: Anna works 7.5 hours on Mondays, Tuesdays and Wednesdays;

Beatrice works 7.5 hours on Thursdays and Fridays; and

Courtney works 6.5 hours every weekday.


Anna, as the most experienced veterinarian, costs $50 per hour to employ. Beatrice, as a veterinarian, costs $42 per hour to employ.

Courtney costs $27 per hour to employ.

These rates take into account other costs related to employment, you don't need to add any additional offsets.

All staff are also entitled to four weeks of paid annual leave, during which casual replacements are required at a cost of $54 per hour for the veterinarians and $35 per hour for Courtney.

During these four weeks, Anna, Beatrice, and Courtney continue to be paid.

Fixed costs such as permits, rent, and insurance are $18000 per year, and monthly utility costs (such as electricity and gas) are $460 per month.

Task 1 - Wages and Fixed Costs

Using Excel, create a spreadsheet called operating_costs.xlsx that calculates the projected annual outgoing costs of running Summit Veterinary Clinic. Only include the above expenses.

Your spreadsheet should be configured such that the working hours, hourly rates, and annual and monthly costs can be varied easily.

After building the spreadsheet, use it to answer the following questions in your report:

What is the projection of total costs for the next year, including labour, annual, and monthly costs? How much income does Summit Veterinary Clinic need per month to cover the above costs?

What is the projection of labour costs only for the next year, if both veterinarians were to cost $50 per hour, but the clinic closed on Wednesday?

Document your findings in your report (approximately 100 words).

History data

When customers attend Summit Veterinary Clinic, they have a consultation with whichever veterinarian is available that day.

In order to improve their business processes, Courtney has been keeping records of each consultation for several months in a spreadsheet.

This data includes a date, the consultation type, the animal type, the expenses incurred (such as tests, protective equipment, and so on) and the price charged.

Task 2 - History visualization

Using Excel, process the history spreadsheet and use any appropriate charts to visualize:

How some aspect of the business has changed over time (for example, prices, expenses, types of consultation, frequency of each animal, etc); and

How some consultations differ between veterinarians.

You will need to find a way to use Excel to associate each consultation with a staff member. Describe your approach and your findings in your report (approximately 250-400 words).

Include your visualizations as images. Be sure to use appropriate titles and labels.

Task 3 - Price consistency

For vaccinations and check-ups, the team would like to standardize costs, so that the price for each is fixed for animals of the same type.

Using Excel, analyse the historical data you have available, and create a spreadsheet that allows Courtney to enter the animal type and consultation type, and gives a quote amount for vaccinations and check-ups.

Ensure that your spreadsheet is usable - it should be simple and include appropriate text and formatting to make your spreadsheet easy to use by a member of the Summit Veterinary Clinic team (or by a University lecturer).

It is up to you to determine an appropriate quoting method.

Describe and justify your approach, including how you tested your solution, in your report (approximately 150 words).

Task 4 - Process improvement

Research the consulation process used in a typical veterinary clinic. Make sure you reference any sources, or indicate if you are basing your work on personal knowledge.

Using BPMN swimlane diagrams, show how the spreadsheet you created in Task 3 can be applied to improve the processes at Summit Veterinary Clinic.

Justify your approach in your report (approximately 150 words).

Bonus challenge task (optional!)

Disclaimer: This task is 100% optional, and you can receive full marks without attempting or completing it. It is intended to be a challenge if you are interested in such things, and the marks available do not reflect the significant research and effort required to implement it correctly. Tutors will not prioritize assistance for this challenge task.

Optional task 5

Reimplement Tasks 1 and 3 using either Python or HTML+JavaScript.

Write a brief overview of how to run your solution, and attach a zip file containing your code submission.

There are no partial marks awarded for this bonus task - you must complete all features to attain the bonus marks.


Task 1weekly number of hours

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.

  1. The total cost for a year is projected at $162,530.
  2. 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.operating cost

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.

Task 2

Firstly, in the given data, a few columns have been added for ease:

  1. Profit: It is simply price minus expenses
  2. Month: Month number has been found from date using ‘MONTH formula’. Then, using ‘Vlookup formula’, month name has been found
  3. Day: Day has been found by using ‘TEXT (“dddd”) formula on the date
  4. 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:

  1. Volume & Trendsmajority of consultation in month
  • 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)
  1. Vetscount of consultationcount of consultation 2
  • 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.
  1. Profitabilityaverage of pricesum of price

  2. average of expensesum of expenseaverage of profitsum of profit
  • 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.

Task 3

The quote was generated on basis of average price for each type of animal and consultation type through use of pivot table:pivot table for quote generation

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:data validation using vlookup formula

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’.spreadsheet

Above is the screenshot of spread sheet created. Sample data has been entered to reflect the output.

Task 4

Swimline diagram is as follows:Swimline diagram

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.

Customer Testimonials