Farmers Groceries To You (FGTY) is a fast-growing company started by JMU alumni, which delivers fresh...
Fantastic news! We've Found the answer you've been seeking!
Question:
Transcribed Image Text:
Farmers Groceries To You (FGTY) is a fast-growing company started by JMU alumni, which delivers fresh groceries collected in Virginia farms to families in the Shenandoah region. As part of an expansion plan, FGTY is considering approaching the northern Virginia market through printed product lists (PLs) containing deals, coupons s and customers testimonies. The initial plan is to send 200,000 PLs to households in Fairfax and Arlington, expecting new customers to place orders. The company estimates to incur a fixed cost of $30,000 associated with design, printing setup, and transportation contracting. Beyond this, the average transportation cost per PL is €20, while the printing cost per PL is c10. Also, FGTY incurs in an average cost of $25 for processing each order placed by a customer using the PL's promotion codes. The expected size of an order is $50, and FGTY's variable cost per order (mainly product costs and labor) is close to 80% of the order's average. You've been hired to help FGTY develop a model to determine the profit from PLs' orders and see how sensitive the profit is to the percentage of households receiving a PL that place an order. Please complete the tasks below: 1. Write an expression to calculate the minimum percentage of households receiving a PL who place an order for FGTY to break even in their expansion endeavor. Show the mathematical procedure to get to the expression 2. Develop a spreadsheet model for FGTY in Excel where you implement the expression found above and format the corresponding cell as a percentage with three decimals. Make sure to format dollar amounts with Sand zero decimals (dollar amounts less than $1 should display two decimal points). Also, use thousands separators when appropriate. names this sheet FGTY. 3. Produce a data table to show how the profit varies when the percentage of households receiving a PL who place an order ranges from 1% to 10% (in increments of one percentage point). Make sure to put a border around the table and format the numbers inside the table with Ssigns and 0 decimal points. Comment on your results. 4. Build a data table to show the profit associated with the fixed cost ranging from $5,000 to $30,000 (in increments of $5,000 in the rows) and the percentage of households receiving a PL who place an order ranging from 1% to 10% (in increments of one percentage point in the columns). Make sure to put a border around the table and format the numbers inside the table with Ssigns and 0 decimals. Use conditional formatting to highlight profits greater than $27,000. Comment on your results. 5. Create a line chart showing the profit corresponding to fixed costs of $5,000, $15,000, $25,000 using the associated 10 columns of your data table. Move this chart to a *New Sheet", name each axis and also each data series with its fixed cost in a legend. Any visible pattern? Comment on your results. 6. For each value of the number of PLs distributed, from 150,000 to 220,000 in increments of 10,000, find the percentage of households receiving a PL who place an order for which FGTY breaks even. Provide a plot where the number mailed is on the horizontal axis, and the breakeven percentage on the vertical axis. Report the procedure that you followed to find the values plotted. Is there another way to do this? Is the relationship seen in the plot linear or nonlinear? Explain. Farmers Groceries To You (FGTY) is a fast-growing company started by JMU alumni, which delivers fresh groceries collected in Virginia farms to families in the Shenandoah region. As part of an expansion plan, FGTY is considering approaching the northern Virginia market through printed product lists (PLs) containing deals, coupons s and customers testimonies. The initial plan is to send 200,000 PLs to households in Fairfax and Arlington, expecting new customers to place orders. The company estimates to incur a fixed cost of $30,000 associated with design, printing setup, and transportation contracting. Beyond this, the average transportation cost per PL is €20, while the printing cost per PL is c10. Also, FGTY incurs in an average cost of $25 for processing each order placed by a customer using the PL's promotion codes. The expected size of an order is $50, and FGTY's variable cost per order (mainly product costs and labor) is close to 80% of the order's average. You've been hired to help FGTY develop a model to determine the profit from PLs' orders and see how sensitive the profit is to the percentage of households receiving a PL that place an order. Please complete the tasks below: 1. Write an expression to calculate the minimum percentage of households receiving a PL who place an order for FGTY to break even in their expansion endeavor. Show the mathematical procedure to get to the expression 2. Develop a spreadsheet model for FGTY in Excel where you implement the expression found above and format the corresponding cell as a percentage with three decimals. Make sure to format dollar amounts with Sand zero decimals (dollar amounts less than $1 should display two decimal points). Also, use thousands separators when appropriate. names this sheet FGTY. 3. Produce a data table to show how the profit varies when the percentage of households receiving a PL who place an order ranges from 1% to 10% (in increments of one percentage point). Make sure to put a border around the table and format the numbers inside the table with Ssigns and 0 decimal points. Comment on your results. 4. Build a data table to show the profit associated with the fixed cost ranging from $5,000 to $30,000 (in increments of $5,000 in the rows) and the percentage of households receiving a PL who place an order ranging from 1% to 10% (in increments of one percentage point in the columns). Make sure to put a border around the table and format the numbers inside the table with Ssigns and 0 decimals. Use conditional formatting to highlight profits greater than $27,000. Comment on your results. 5. Create a line chart showing the profit corresponding to fixed costs of $5,000, $15,000, $25,000 using the associated 10 columns of your data table. Move this chart to a *New Sheet", name each axis and also each data series with its fixed cost in a legend. Any visible pattern? Comment on your results. 6. For each value of the number of PLs distributed, from 150,000 to 220,000 in increments of 10,000, find the percentage of households receiving a PL who place an order for which FGTY breaks even. Provide a plot where the number mailed is on the horizontal axis, and the breakeven percentage on the vertical axis. Report the procedure that you followed to find the values plotted. Is there another way to do this? Is the relationship seen in the plot linear or nonlinear? Explain.
Expert Answer:
Related Book For
Cost Accounting A Managerial Emphasis
ISBN: 978-0133428704
15th edition
Authors: Charles T. Horngren, Srikant M. Datar, Madhav V. Rajan
Posted Date:
Students also viewed these programming questions
-
Harbour Company makes two models of electronic tablets, the Home and the Work. Basic production information follows: Direct materials cost per unit Direct labor cost per unit Sales price per unit...
-
Planning is one of the most important management functions in any business. A front office managers first step in planning should involve determine the departments goals. Planning also includes...
-
Introduction Some 25 years ago, one of the authors wrote an article (Stevens, 1989) that sought to explicate the state-of-the-art in supply chain management (SCM). This was at a time when SCM was...
-
The US Womens Swimming Team won the 1500 m relay at the 2016 Olympic Games. Here are the completion times, in seconds, for all eight teams that competed in the finals: a. Calculate the sample...
-
Distinguish between random events and fixed events.
-
Nike reported book value per share of $15.93 at the end of its 2008 fiscal year. Analysts were forecasting earnings of $3.90 per share for 2009 and $4.45 for 2010, and were also forecasting a...
-
It is common to see share prices of a company increase with the announcement of good news from the company. It is also the case that record earnings qualify as very good news. The behavior of stock...
-
1. Is fairness the economic basis for government laws and regulations designed to remedy market failures? If so, why; if not, what is the economic basis? 2. Explain, using precise economic...
-
Determine the topics, subtopics and sections of the Codification for the following balance sheet events. Question content area bottom Part 1 Balance Sheet Event Codification Reference a. Inventory...
-
What is the difference between computer organization and computer architecture?
-
A-1 Athletic Shoes has wide variety in its product line. A firm with a wide product variety will find it Multiple Choice O O difficult to increase its product sales. difficult to achieve shorter...
-
Let f(x) = 2e cos(x). (a) Find f'(x) and f'(x). f'(x) = f"(x) =
-
2. The company recently joined together with another programmer, Kendal, who had designed an app to help golfers. SPI agreed to carry the app and golfers can downloaded the app for $4.99 off SPI's...
-
5 1 points eBook a. Equipment with a book value of $79,500 and an original cost of $167,000 was sold at a loss of $30,000. b. Paid $103,000 cash for a new truck. c. Sold land costing $325,000 for...
-
Company has a recent gross profit history of 60% based on cost Date available from the accounting record for 3 months ended March 31 Inventory - Jan 1 350,000.00 Purchases 3,650,000.00 Sales...
-
What roles do trust and commitment play in business relationships and Why might the trust and commitment theory be insufficient in explaining the performance of these relationships?
-
What are the effects of coupon rate to the sensitivity of a bond price and to changes in interest rates?
-
7. FALSE DILEMMA 8. GANDWAGON Definition: Fallacy example: How to revise argument: Definition: Fallacy example: How to revise argument:
-
Safeclear, Inc., is the leading manufacturer of automotive glass components such as windshields. The company uses a process-costing system to account for its work-in-process inventories. When Job 26,...
-
Peach Computer Co. outsources the production of motherboards for its computers. It is currently deciding which of two suppliers to use: Alpha or Beta. Due to differences in the product failure rates...
-
Tuff Kids Jeans Co. sells blue jeans wholesale to major retailers across the country. Each pair of jeans has a selling price of $ 30 with $ 21 in variable costs of goods sold. The company has fixed...
-
Arthur Jansky and Edward Thayer are partners, each with $27,000.00 equity in an existing business. The partners share equally in all changes in equity. On March 1 of the current year, Dean McGee is...
-
On July 1 of the current year, Roy Hatfield and Michelle Allen form a partnership. The partners share equally in all changes in equity. The partnership assumes the assets and liabilities of Roy's...
-
Helen Cole, her husband, and her son were partners in a business. Mrs. Cole's husband died, leaving his equity in the partnership to Mrs. Cole. Mrs. Cole and her son plan to form a new partnership...
Study smarter with the SolutionInn App