You will need to work on MS Office files, must of the work are done in Access file(BUS220_SP20) that I will provide and you also need to work on some Excel, Word, and PowerPoint. In addition, I will provide a word.doc file for more instructions.

BUS220 Applied Business Applications

Final Project: Integration of Microsoft Access, Excel, Word and PowerPoint to ANALYZE a Business

————————————————————————————————————————————–

Objective:

The MAIN objective of this course has been to learn to use database and spreadsheet applications to manage business data, and to use that data to analyze and resolve basic, real-world business problems. Although you have spent a great deal of time this semester learning the functionality of the MS Office applications, that was only a secondary objective. As explained at the beginning of the semester, this was an important distinction for you to understand so that you approached the coursework appropriately and paid attention to the HOW and WHY the applications are used by a business. In this project you will use what you have learned in this course, as well as what you know from your previous business course(s), to help you make the connection between the applications and basic business functions.

The objectives of this project are to:

• demonstrate the ability to apply and integrate the applications of the Microsoft Office suite: Access, Excel, Word, and PowerPoint, to complete a business case analysis
• critically analyze business data for the purpose of making basic financial business decisions

Instructions:

You have been provided with a populated Access database (YourLastname_BUS220_SP20.accdb) that contains the financial data for a fictional grooming salon. Rename the file by replacing the “X” with YOUR last name (for example: Smith_BUS220_SP20.accdb). Use Access and Excel to analyze business performance, Word to document your results in a formal written report, and PowerPoint to develop a presentation for the client.

Business Description:

The business and all data is fictional. No business name has been given, so create your own name for the grooming salon.

The business data that you will be analyzing is from a small (fictional) business that provides grooming and boarding services for dogs and cats. They also sell a limited supply of dog- and cat-related items. The salon has only been in business for a few months. The owner, Sarah Anderson, is a professional groomer. She currently takes a salary of $300 per week (@$15/hour * 20 hours per week), although she works as many hours as necessary each week to service customers as she attempts to grow the business. Sarah employs a high school student, Joe Howard, who works 10 hours per week at minimum wage giving baths and providing walk/play services. Joe has expressed an interest in going to grooming school after his high school graduation. In addition to employee salaries, Sarah spends $500 per month rent for the salon space, which includes her utilities.

Sarah has been getting great reviews on social media and she believes her business is growing, but she would like your help to analyze her business performance to-date and to make suggestions for further business growth and development.

Step 1: Familiarize yourself with the Business and the Data

Familiarize yourself with the business by carefully reviewing the tables’ structure, relationships, and data. The database includes a form for entering invoice information. Navigate through the invoices to review and further familiarize yourself with the data.

Step 2: Create a Form, add new records

• Create a new well-styled form with a subform for entering customers and their pets. Style the form attractively and appropriately for usability. Name the form frmCustomerAndPets.
• Using this newly created form, enter yourself as a customer with at least one pet.
• Use the provided form frmInvoice to add at least one invoice for each of the pets that you entered. Date the invoices 7/1/19.

Step 3: Write Queries – Write the following queries to help you begin extracting data for analysis.
Use these as a starting point. These should NOT be the only queries that you write.

• Write a query to calculate and display only the items sold in December 2018. Include the following fields: InvoiceNum, InvoiceDate, Category, Item#, ItemDescription, RetailPrice, Cost, Profit (calculated). The query criteria should be written in such a way that the results will still be accurate even if the data changes (for example, invoices with a date in December 2018 are added or deleted). Add a total row to the datasheet to display a count of the number of items sold, and the sums of each: RetailPrice, Cost, and Profit. Name the query qryItemsPurchasedDec2018.
  See screen shot for expected results:
• Write a query similar to the previous query to calculate and display only the items sold in January 2019. Include the same fields. Name the query qryItemsPurchasedJan2019.
  See screen shot for expected results:

• Write a query to calculate and display the details of each invoiced service, for all dates. Include the following fields: InvoiceNum, InvoiceDate, InvoiceServiceID, ServiceCode, Description, ServiceFee, TimeToComplete, SuppliesCost, LaborCost (calculated), and Profit (calculated). Name the query qryInvoicedServices_Details. See screen shot for expected results: (note – your values may differ slightly depending on the services that you included on your invoice(s) in step 2)
• Write a query based on qryInvoicedServices_Details to calculate and display a summary of invoiced services grouped by service code. Name the query qryInvoicedServices_Summary.
  See screen shot for expected results: (note – your values may differ slightly depending on the services that you included on your invoice(s) in step 2)

Step 4: Create a REPORT

• Create a report to display an organized, attractive, formatted, printable list of customers and their pets. Name the report rptCustomersAndPets.

Step 5: Use Access and Excel to ANALYZE the Data – ALL of the following areas (services sold, items sold, personnel, equipment purchase, and marketing) are REQUIRED to be analyzed.

REQUIRED file names:

• The Access file must be named: accdb
• The Excel file must be named: xlsx
• Services Offered – Use Access and Excel to analyze the services offered by the grooming salon and make recommendations to the business owners based on your analysis. Do not simply summarize the data. Use these questions as a guide to get you started. These should NOT be the only questions that you answer or suggestions that you make. Your analysis should include more than this.
  1. Which services are the most and least requested?
  2. How much income is generated by each individual service? All services?
  3. How much profit is generated by each service? Is profit affected by which employee performs the service? Should this affect how assignments are delegated?
  4. Are there services that the salon should consider no longer offering? Justify your answer.
  5. Are there additional services that the salon might consider offering? Justify your suggestion.
  6. Is there room in the profit margins to lower prices (on specific items, on all items) for the purpose of generating more sales? Would a lower price but more sales generate a greater profit? Is there a point where more sales might not be profitable?
• Items Sold – Use Access and Excel to analyze the types of items that are sold by the grooming salon and make recommendations to the business owners based on your analysis.
  1. Answer similar types of questions as those suggested for services. Adjust accordingly for the differences between an item sold and a service rendered.
  2. Include calculations to summarize data and appropriate charts to display.
• Personnel – Sarah currently works as many hours as needed, and takes a small weekly salary. Her goal is to eventually work full-time in the salon and make a full-time salary.
  1. Recognizing that it takes time for a business to build a clientele, discuss the current and projected income levels in terms of ability to support personnel costs.
  2. Make recommendations regarding appropriate staffing levels, both current and future.
• Equipment – purchase consideration
  500. The grooming salon is in need of purchasing a new stainless steel bathing tub and two blow dryers, at an estimated total cost of $2,500. Since Sarah does not have the capital to purchase the tub outright, she will have to finance the purchase. She tells you that she feels she can afford a monthly payment of no more than $150.
  501. Begin by researching small business equipment loan rates to get an idea of current rates and terms. Use Excel to calculate various possible rates/terms/payments. Keep in mind that this is a new business with limited financial history, and has a credit score under 700 so the business may not be eligible for all financing options.
  502. Compare the business equipment loan to the purchase of the equipment on a small business credit card. You will need to research current rates for small business credit cards.
  503. Calculate and present various payment options and make a recommendation as to which option you feel would make the most business sense. Give a justification/explanation for your recommendation, including interest considerations.
• Marketing
  1. Develop some sort of marketing promotion piece directed at current customers. It can be a letter, flyer or some other appropriate form of marketing – be creative.
  2. Use mail merge to personalize the document (for example, customer name, pet’s name, type of pet, type of service, etc.).
  3. Discuss when/how/where your marketing piece would be distributed.
  4. Suggest other marketing plans and methods (i.e. social media) for the purpose of gaining new customers.

Step 6: Write a Report to the Client

• Use Word to write a business report to the client, documenting and explaining your analysis and recommendations. The report should be written from the perspective that you are a consultant writing to the client (not a student completing an assignment).
  • Title Page: include your full name and the date.
  • Business Description: Write a brief description of the grooming salon, including,
    day-to-day operations. Do not make up things about the business that is not part of the business description provided.
  • Objectives: Write a summary of what you are trying to do for the business.
  • Findings and Recommendations (the most important part of the report): What are your findings and recommendations – i.e. what are the “answers” to the questions. These come from the Access and Excel analysis you did. Use your judgment as to how to present this information. All areas (services, items, personnel, equipment, and marketing) must be addressed. This is the most important section of your report.
  • Formatting:
    • Calibri or Times New Roman font
    • 11pt or 12pt font size
    • 5 line spacing
    • Use section and heading font templates.
    • Page numbering (p. x of y) – exclude page number on title page
  • The Word file must be named: docx

Step 7: Create the Presentation

• Use PowerPoint to create a simple, well formatted and styled presentation containing:
  • Title slide, including the business name, your name, and the date.
  • Body slides – as many as needed to summarize your findings.
  • Conclusion/Summary slide
  • The presentation should be a summary of the details that are in your report. Its overall purpose is to present a SUMMARY of your analysis and recommendations for the business. There should not be any data or analysis in the PPT that is not in the report.
• DETAILED SPEAKER NOTES – REQUIRED. Think of the speaker notes as your script. The instructor should be able to read your speaker notes and “hear” pretty much everything that you would have said during your presentation.
• The PPT file must be named: pptx

Step 8: Submit for Grading

• Upload the Access database, Excel workbook, Word report, and PPT presentation to the Final Project drop-box in Blackboard.

“Business Rules”

• A customer can have many pets, but a pet can only have one customer (owner).
• An invoice is for a specific pet.
• An invoice may contain charges for multiple services rendered and items sold. Each charge (line-item) is an individual record in either the InvoiceService table, or the InvoiceItem table, depending on which type of charge it is (service or item).
• All invoiced services must have an assigned groomer, even if the service does not require a specific groomer (for example: overnight board). For those type of services, the assigned groomer is “staff”.