Scenario details The following scenario is fictional, and not based on any real business. It is also highly simplified, and should not be used as a basis for a real business management system. Pawriffic Veterinary Care is a small veterinary clinic, working with domestic companion animals. Mostly they work with dogs, cats, and smaller animals such as hamsters and rabbits. Pawriffic Veterinary Care is run by a team of two veterinarians, Amy and Bernard, and one administrative staff member, Cynthia. Team hours Most of the time, team members work the following shifts at Pawriffic Veterinary Care: Amy works 6.5 hours on Mondays, Wednesdays and Fridays; Bernard works 6.5 hours on Tuesdays and Thursdays; and Cynthia works 3.5 hours every weekday. Costs Amy, as the most experienced veterinarian, costs $45 per hour to employ. Bernard, as a veterinarian, costs $38 per hour to employ. Cynthia costs $30 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 $49 per hour for the veterinarians and $39 per hour for Cynthia. During their leave weeks, Amy, Bernard, and Cynthia continue to be paid. Fixed costs such as permits, rent, and insurance are $16384 per year, and monthly utility costs (such as electricity and gas) are $390 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 Pawriffic Veterinary Care. 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 Pawriffic Veterinary Care 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 $45 per hour, but the clinic closed on Fridays? Document your findings in your report (approximately 100 words). History data When customers attend Pawriffic Veterinary Care, they have a consultation with whichever veterinarian is available that day. In order to improve their business processes, Cynthia 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. CRICOS Provider No. 00103D Page 3 of 5 Task 2 - Analysis Using Excel, process the history spreadsheet and use any approach to answer the following: Which animal type has the fewest emergency visits overall? After about two months, the clinic started running a discount day for dog checkups, to try to reduce emergency visits. Which day did they run them on, and were they successful at reducing emergencies? Support your claims using appropriate analysis or data visualization (approximately 150-200 words). Task 3 - History visualization Using Excel, process the history spreadsheet and use any appropriate charts to visualize the following: How consultations differ between animal types; How prices or expences have changed over time; 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 200-300 words). Include your visualizations as images. Be sure to use appropriate titles and labels. Task 4 - Price consistency The team are unhappy with some of the differences detected above, and would like to make them more consistent. For vaccinations and check-ups, the team would like to standardize the prices for each is the same for all small animals (birds, rabbits, and hamsters) and the same for all larger animals (dogs and cats). Using Excel, analyse the historical data you have available, and create a spreadsheet that allows Cynthia to enter the animal type and consultation type, and gives a quote amount. 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 Pawriffic Veterinary Care team (or by a University lecturer). It is up to you to determine an appropriate quoting method, however it's vital that the new system doesn't lose money compared to the previous approach. Describe and justify your approach in your report (approximately 150 words). Include how you tested your solution, and how you made sure the clinic is not worse off under your new approach. Task 5 - 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 4 can be applied to CRICOS Provider No. 00103D Page 4 of 5 improve the processes at Pawriffic Veterinary Care. Justify your approach in your report (approximately 150 words).