ITECH1100 Process And Automation: Complete Animal Clinic Homework Answer

pages Pages: 4word Words: 890

Question :

Assignment - Complete Animal 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. Do not distribute this specification. If your personal specification is made publicly available online, academic misconduct charges may be applied.

Timelines and Expectations

Percentage value of task: 25%

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.

Complete Animal 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.

Complete Animal Clinic is run by a team of two veterinarians, Amanda and Bhavesh, and one administrative staff member, Caesar.

Team hours

Most of the time, team members work the following shifts at Complete Animal Clinic: Amanda works 7.5 hours on Mondays, Tuesdays and Wednesdays;

Bhavesh works 7.5 hours on Thursdays and Fridays; and

Caesar works 6.5 hours every weekday.


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

Caesar 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 Caesar.

During these four weeks, Amanda, Bhavesh, and Caesar 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 Complete Animal 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 Complete Animal 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 Complete Animal Clinic, they have a consultation with whichever veterinarian is available that day.

In order to improve their business processes, Caesar 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.

This spreadsheet is available for you to download on Moodle.

Task 2 - History visualization

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

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

How consultations have differed 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 Caesar 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 Complete Animal 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 Complete Animal 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.

It is possible to attain full marks for this assignment without completing this challenge task.

Show More

Answer :

Task 1weekly number of hours and operating cost

  • 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). 
  • The yellow cells are input cells and are linked to all other cells. If yellow cells are changed, calculations will get updated automatically.
  • As seen from Cell E21, yearly cost is projected at $162,530.
  • If the annual cost in Cell E21 is divided by 12, approximate monthly cost projection can be seen at $13,544.17. Hence, the lab will need a minimum monthly income of $13,545 to cover the expenses.
  • Below, the yellow cells have been changed to reflect hourly rate of $50/vet and lab being closed on Wednesdays. It can be seen that in this case, the labour costs for the year (including replacement) are $115,816 and the total costs are $139,336. The lab needs a minimum monthly income of $11,611.33 to meet its expenses. Hence, expenses have lowered a little as lab is closed on one day per week. This may also indicate corresponding reduction in income as emergency cases and some other customers who would have come on Wednesday will represent lost opportunity.lab closed one day in a week

Task 2

The additional columns in the provided data are:

  1. Profit: Price minus expenses so as to ascertain profitability from different angles
  2. Month: Using ‘MONTH formula’ on the date and using ‘Vlookup formula’, month name has been found to see difference across months
  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 

Following trends were found by busing Pivot tables:

1. Monthly Trendsvolume of animal types in different monthscount of consultations other than animal types

  • The above charts visualise data on a monthly basis.
  • The first chart indicates volume of various animal types consulted in different months. The maximum number of consultations was in October (347) and July (325) while the minimum was in November (156). 
  • The maximum consultations are for cats (695) and dogs (681) with the minimum being for rabbits (64).
  • The maximum consultations are check-ups (691) with the minimum being for vaccination (264).

2. Vet Trendsvolume of various animal types for ananda versus bhaveshchange in trend when consultations numer is similiar

  • The above charts visualise data for Amanda versus Bhavesh.
  • The first chart indicates volume of various animal types consulted. It can be seen that Amanda (1025) has much higher number of consultations compared to Bhavesh (689). A plausible reason maybe the number of weekly working hours which is much more in case of Amanda (22.5 hours/week as compared to 15 hours/week for Bhavesh). 
  • However, the trend changes in November when the number of consultations for both the vets is almost same (81 for Amanda and 75 for Bhavesh).

3. Profit Trends
average price and revenue by each vetsaverage expense and total expense by each vetsaverage profit and total profit by each vets

  • The above charts visualise data for Amanda versus Bhavesh with respect to expenses, price and profit.
  • The first two charts indicate average price and revenue generated by each of the vets. The second two charts indicate average expense and total expenses incurred by each of the vets. Finally, the last two charts indicate average profit and total profit earned by each of the vets.
  • The average price, expense and profit by Amanda was $264.8, $119.7 and $145.0, respectively. The average price, expense and profit by Bhavesh was $259.2, $99.5 and $159.6, respectively. The overall average price, expense and profit was $262.5, $111.6 and $150.9, respectively. 
  • Hence, while Amanda charges higher price, the average expense is also higher, leading to lower than overall average profitability. However, Bhavesh charges a little lower than overall average price but has lower expenses leading to lower than overall average profitability. While Bhavesh does only 40.2% of total consultations during the period, he generates almost 42.5% of the profit. It must also be considered that his working hours and days are fewer than Amanda.

Task 3

On the basis of average price charged for each type of consultation and animal, a standardized price was arrived at: standardized price

Using a combination of INDEX and MATCH formulae on the above average price pivot table and Data Validation (list) on ‘Animal Type’ and ‘Consultation Type, an automated spreadsheet was created such that only date and expense are manual entries while animal type and consultation type needs to be selected from dropdown. Once this is done, automatically, the price to be charged appears along with the vet name and profit. The automated fields appear in green. 

The spreadsheet with random sample entries looks as follows:Swimline diagram

Task 4

Swimline diagram is as follows:

Swimline diagram

As discussed earlier, the improved process involves only entering animal type and consultation type on arrival of a customer, that too from the dropdown options. This leads to generation of the quote which can be given to the customer. Once customer approves, the available vet for the day will treat the animal and quoted amount can be collected from the customer who will be given the bill. In the end, the incurred expenses need to be entered in the spreadsheet so as to close the transaction. This will lead to automatic generation of profit from the transaction.