Question: Background Forrest runs Y Not Flowers, Inc. (YNF) , a wholesale flower distributor with stores in several major metropolitan areas of the U.S. He is
Background
Forrest runs Y Not Flowers, Inc. (YNF), a wholesale flower distributor with stores in several major metropolitan areas of the U.S. He is considering expanding his business, but he thinks his current accounting and financial systems are restricting that expansion. He also worries that YNF is not adequately prepared to face expected business risks. He has come to you for assistance.
Instructions
Your supervisor met with Forrest and discussed his requirements in detail. At that meeting, they agreed on the following deliverables for your project.
- Document process activity using BPMN. After gaining an understanding of YNF's business processes, you will document current processes using business process modeling notation (BPMN). This involves documenting at least five current business processes. These diagrams will serve multiple purposes. YNF expects to use them to train new employees. Plus, they will provide information about potential improvements to those processes by the efficient use of information technology. Additionally, they will also highlight areas where YNF is exposed to unnecessary risks in his business.
- Prepare an integrated data model using UML class diagrams and the REA framework. This data model will serve as the blueprint for YNF's prototype database that you will also develop. Also, prepare a corresponding table listing for the prototype database. Identify where different fields/attribute names provide the same information and develop recommendations for changes in field names.
- Prepare a prototype database for YNF. Forrest has agreed to provide financial data from his business for the 1st quarter of his fiscal year. That data will be in Excelformat. You will move the data into Access, structure it appropriately, and set relationships consistent with your data model. Make changes as necessary in file structures to implement efficient tables and eliminate duplication.
- Prepare financial reports using your database. You will prepare queries as necessary to provide the following reports and management information:
- YNF income statement for the 1st quarter.
- YNF balance sheet as of the end of the 1st quarter.
- Income statements for each YNF stores for the 1st quarter.
- Other performance measures that you believe YNF should adopt.
- Evaluate the financial performance of YNF for the 1st quarter. Use your Access queries to also prepare formal financial statements and compute appropriate ratios to evaluate performance against the industry and past performance. Clearly explain what those ratios mean for Mr. Z's business.
- Assess the business case for use of information technology in the business. Y Not Flowers wants to move to a cloud-based accounting system, such as Netsuite or QuickBooks Online. Your assessment should identify the costs of the change in technology, while also specifying which business processes would change and how they would change in as much detail as possible.
- Recommend changes to YNF business processes to make it more effective and efficient.
Project Resources
You will need to make use of the following files as you work through the project:
- Summary of Y Not Flowers interviews
- Excel spreadsheet of Y Not Flowers'current data
- Additional Excel spreadsheet of Y Not Flowers' historical data
Additionally, the following files have been provided for assistance:
- Instructions on how to move data from Excel to Access
- List of modeling software
- List of project management software
Requirements
Complete the Y Not Flowers project using the instructions and resources listed above. For your Connect assignment, Sections 1 and 2 should be attempted after you have completed Project Deliverable 2 listed above. Section 3 should be attempted after you have completedProject Deliverable 3.
1. Section 3, Question 10
Move the Excel data to Microsoft Access and prepare queries to answer the following questions. Required: What was the revenue for the Austin TX store in the first quarter?
Multiple Choice
None of the choices are correct., $170,612.00, $145,201.00, $178,463.50, $166,982.00
2. Section 3, Question 11
Move the Excel data to Microsoft Access and prepare queries to answer the following questions.
Required: Use the query prepared for question 4 in this section and join it with the Stores table. Compute the cost of goods sold (COGS) (the sum of flower purchases, since YNF maintains no inventory)for each store in the first quarter of 2022. What was the total COGS/purchases for the Boston MA store in the first quarter?
Multiple Choice
$1,099,834.86, $176,205.50, $94,050.13, $83,119.92, $94,119.92
3. Section 3, Question 12
Move the Excel data to Microsoft Access and prepare queries to answer the following questions.
Required: What was the total COGS for the Austin TX store in the first quarter?
Multiple Choice
$91,701.28, $89,433.40, $1,129,664.89, $94,119.92, $83,119.92
4. Section 3, Question 13
Move the Excel data to Microsoft Access and prepare queries to answer the following questions.
Required: Use the query prepared for question 5 in this section. Compute the payroll expense, including employer FICA and MC contributions, for each store in the first quarter of 2022. What was the total payroll expense for the Boston MA store in the first quarter?
Multiple Choice
$515,108.20, $44,232.53, $92,860.84, $43,398.66, $42,370.47
5. Section 3, Question 14
Move the Excel data to Microsoft Access and prepare queries to answer the following questions.
Required: Which store had the highest payroll expense for the first quarter 2022?
Multiple Choice
Phoenix, New York, Boston, Austin, San Diego
6. Section 3, Question 15
Move the Excel data to Microsoft Access and prepare queries to answer the following questions.
Required: Which store had the lowest payroll expense for the first quarter 2022?
Multiple Choice
New Orleans, Boston, Miami, Washington DC, New York
7. Section 3, Question 16
Move the Excel data to Microsoft Access and prepare queries to answer the following questions.
Required: Use the query prepared for question 6 above and join it with the Stores table. What was the total administrative/miscellaneous expense for the Boston MA store in the first quarter 2022?
Multiple Choice
None of the choices are correct., $1,194.00, $1,906.00, $1,240.00, $1,224.00
8. Section 3, Question 17
Move the Excel data to Microsoft Access and prepare queries to answer the following questions.
Required: Which store had the highest administrative/miscellaneous expense in the first quarter?
Multiple Choice
Seattle, Portland, Philadelphia, San Francisco, Miami
9. Section 3, Question 18
Move the Excel data to Microsoft Access and prepare queries to answer the following questions.
Required: Which store had the lowest administrative/miscellaneous expense in the first quarter?
Multiple Choice
New Orleans, San Diego, San Francisco, Boston, Seattle
10. Section 3, Question 19
Move the Excel data to Microsoft Access and prepare queries to answer the following questions.
Required: Use the queries prepared for questions 9 and 11 in this section to compute revenue and cost of goods sold for each store. Join them with the Stores table by store# and compute gross profit (revenue minus cost of goods sold) dollars and percentage (gross profit divided by revenue) for each store in the first quarter of 2022. What was the gross profit (dollars) for the Austin TX store for the first quarter?
Multiple Choice
$77,548.60, Less than $74,000, $84,054.69, $87,649.10, More than $85,000
11. Section 3, Question 20
Move the Excel data to Microsoft Access and prepare queries to answer the following questions.
Required: What was the gross profit (dollars) for the Boston MA store for the first quarter?
Multiple Choice
$87,649.10, $84,413.37, Less than $74,000, More than $85,000, $84,054.69
12. Section 3, Question 21
Move the Excel data to Microsoft Access and prepare queries to answer the following questions.
Required: Which store had the highest gross profit in dollars in the first quarter?
Multiple Choice
None of the choices are correct., Seattle, Portland, Miami, San Diego
13. Section 3, Question 22
Move the Excel data to Microsoft Access and prepare queries to answer the following questions.
Required: Which store had the lowest gross profit in dollars in the first quarter?
Multiple Choice
None of the choices are correct., Seattle, Miami, San Diego, Portland
14. Section 3, Question 23
Move the Excel data to Microsoft Access and prepare queries to answer the following questions.
Required: Which store had the highest gross profit percentage in the first quarter?
Multiple Choice None of the choices are correct., Miami, New York, New Orleans, Phoenix
15. Section 3, Question 24
Move the Excel data to Microsoft Access and prepare queries to answer the following questions.
Required: Which store had the lowest gross profit percentage in the first quarter? Multiple Choice
None of the choices are correct., Philadelphia, San Francisco, Miami, Austin
16. Section 3, Question 25
Move the Excel data to Microsoft Access and prepare queries to answer the following questions.
Required: Use the queries prepared for questions 19, 13, 16, 7, 8 in this section to compute the income before taxes (gross profit minus payroll expense minus admin expense minus truck depreciation expense minus store depreciation expense) and the income percent of sales in the first quarter of 2022. Which store had the highest income (dollars) in the first quarter?
Multiple Choice
None of the choices are correct., Seattle, Washington DC, Portland, Miami
17. Section 3, Question 26
Move the Excel data to Microsoft Access and prepare queries to answer the following questions.
Required: Which store had the lowest income (dollars) in the first quarter?
Multiple Choice
None of the choices are correct., Philadelphia, Austin, Seattle, Portland
18. Section 3, Question 27
Move the Excel data to Microsoft Access and prepare queries to answer the following questions.
Required: Which store had the highest income as percent of sales in the first quarter?
Multiple Choice
None of the choices are correct., Austin, New York, Boston, Miami
19. Section 3, Question 28
Move the Excel data to Microsoft Access and prepare queries to answer the following questions.
Required: Which store had the lowest income as percent of sales in the first quarter?
Multiple Choice
None of the choices are correct., San Francisco, Austin, Seattle, Portland
20. Section 3, Question 29
Move the Excel data to Microsoft Access and prepare queries to answer the following questions.
Required: Create a new query to present overall income statement information for YNF (use the query prepared for question 25). What was the total revenue for Y Not Flowers in the first quarter of 2022?
Multiple Choice
$2,071,954.50, $2,141,359.50, $2,105,610.50, Less than $2,000,000, More than $2,200,000
21. Section 3, Question 30
Move the Excel data to Microsoft Access and prepare queries to answer the following questions.
Required: What was the total income before tax for Y Not Flowers in the first quarter of 2022?
Multiple Choice
$444,060.72, $401,173.22, Less than $400,000, $489,948.25, More than $500,000
22. Section 3, Question 31
Move the Excel data to Microsoft Access and prepare queries to answer the following questions.
Required: What was the overall gross profit percentage (percent of sales) for Y Not Flowers in the first quarter of 2022?
Multiple Choice
45.75%, More than 49%, Less than 45%, 48.68%, 47.28%
23. Section 3, Question 32
Move the Excel data to Microsoft Access and prepare queries to answer the following questions.
Required:What was the overall net profit percentage (income before taxes as a percent of sales) for Y Not Flowers in the first quarter of 2022?
Multiple Choice
More than 22%, 19.66%, Less than 19%, 21.09%, 19.80%
24. Section 3, Question 33
Move the Excel data to Microsoft Access and prepare queries to answer the following questions.
Required: What was Y Not Flowers cash balance (total cash) as of January 1, 2022?
Multiple Choice
None of the choices are correct., Less than $290,000, More than $430,000, $292,145.00, $429,758.45
25. Section 3, Question 34
Move the Excel data to Microsoft Access and prepare queries to answer the following questions.
Required: What was the total amount Y Not Flowers disbursed (paid) to growers during the first quarter of 2022?
Multiple Choice
None of the choices are correct., Less than $1,100,000, More than $1,150,000, $1,129,664.89, $1,109,973.39
26. Section 3, Question 35
Move the Excel data to Microsoft Access and prepare queries to answer the following questions.
Required: What was the total amount Y Not Flowers disbursed to miscellaneous/admin vendors during the first quarter of 2022?
Multiple Choice
$10,343.00, Less than $10,000, More than $11,000, $10,433.00, $10,543.00
27. Section 3, Question 36
Move the Excel data to Microsoft Access and prepare queries to answer the following questions.
Required: What was the total amount Y Not Flowers disbursed for payroll expenses during the first quarter of 2022?
Multiple Choice
$365,336.75, More than $520,000, Less than $350,000, $478,502.60, $515,108.20
28. Section 3, Question 37
Move the Excel data to Microsoft Access and prepare queries to answer the following questions.
Required: What was the cash balance (cash on Jan 1 plus cash received minus cash disbursed for growers, administrative expense, and payroll) for Y Not Flowers as of March 31, 2022?
Multiple Choice
Less than $1,000,000, $1,069,715.81, $1,109,973.39, More than $1,120,000, $1,046,519.81
29. Section 3, Question 38
Move the Excel data to Microsoft Access and prepare queries to answer the following questions.
Required: What was Y Not Flowers' accounts receivable balance as of March 31, 2022?
Multiple Choice
None of the choices are correct., $2,071,954.50, $2,141,359.50, $2,082,414.50, $0.00
30. Section 3, Question 39
Move the Excel data to Microsoft Access and prepare queries to answer the following questions.
Required: What was Y Not Flowers' current assets value as of March 31, 2022?
Multiple Choice
None of the choices are correct., Less than $1,050,000, $2,296,317.40, $1,069,715.81, $1,056,658.34
31. Section 3, Question 40
Move the Excel data to Microsoft Access and prepare queries to answer the following questions.
Required: What was Y Not Flowers' net store value (net of accumulated depreciation including the depreciation in the first quarter of 2022) as of March 31, 2022?
Multiple Choice
Less than $300,000, More than $1,100,000, $758,644.06, $306,505.94, $1,065.150.00
32. Section 3, Question 41
Move the Excel data to Microsoft Access and prepare queries to answer the following questions.
Required: What was Y Not Flowers' net trucks value (net of accumulated depreciation including the depreciation in the first quarter of 2022) as of March 31, 2022?
Multiple Choice
$865,159.06, $374,500.00, Less than $370,000, More than $875,000, $567,000.00
33. Section 3, Question 42
Move the Excel data to Microsoft Access and prepare queries to answer the following questions.
Required: What was Y Not Flowers' total assets (including cash, accounts receivable, and fixed assets (stores and trucks) as of March 31, 2022?
Multiple Choice
None of the choices are correct., $1,065,150.00, $1,069,715.81, $2,239,659.06, $2,202,859.87
34. Section 3, Question 43
Move the Excel data to Microsoft Access and prepare queries to answer the following questions.
Required: What was Y Not Flowers' accounts payable to growers as of March 31, 2022?
Multiple Choice
More than $150,000 and less than $1,000,000, $149,771.62, $0.00, More than $31,000 and less than $145,000, $30,200.41
35. Section 3, Question 44
Move the Excel data to Microsoft Access and prepare queries to answer the following questions.
Required: What was Y Not Flowers' accounts payable to miscellaneous vendors as of March 31, 2022? Multiple Choice
More than $150,000, More than $6,000 and less than $145,000, $0.00, $149,771.62, $5,168.00
36. Section 3, Question 45
Move the Excel data to Microsoft Access and prepare queries to answer the following questions.
Required: What was Y Not Flowers' accounts payable for payroll as of March 31, 2022?
Multiple Choice
None of the choices are correct., Less than $125,000, More than $480,000, $478,502.60, $149,771.62
37. Move the Excel data to Microsoft Access and prepare queries to answer the following questions.
Required: What was Y Not Flowers' total liabilities as of March 31, 2022?
Multiple Choice
None of the choices are correct., Less than $145,000, More than $190,000, $149,771.62, $154,939.62
38. Section 3, Question 47
Move the Excel data to Microsoft Access and prepare queries to answer the following questions.
Required: What was Y Not Flowers' total equity as of March 31, 2022
Multiple Choice
Less than $880,00, More than $2,250,000, $884,575.78, $2,202,859.87, $2,047,920.25
Step by Step Solution
There are 3 Steps involved in it
Get step-by-step solutions from verified subject matter experts
Study Help