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...
-
The rms current in a copy machine is 6.50 A, and the resistance of the machine is 18.6 V. What are (a) The average power and (b) The peak power delivered to the machine?
-
In an experiment to find the velocity and acceleration of a particular cam rotating at \(10 \mathrm{rad} / \mathrm{s}\), the values of displacements and velocities are recorded. The slope of...
-
What is the FASBs view of what the purpose of financial accounting should be?
-
On December 31, 2008, before the books were closed, the management and accountants of Keltner Inc. made the following determinations about three depreciable assets. 1. Depreciable asset A was...
-
Two tangents of a circle that meet outside of the circle at one point are the same congruent perpendicular
-
If the resultant force acting on the bracket is FR = {? 300i + 650j + 250k} N, determine the magnitude and coordinate direction angles of F. 45 30- F= 750 N
-
Brandon, an individual, began business four years ago and has sold $1231 assets with $5,000 of losses within the last five years Brandon owned each of the assets for several years. In the current...
-
A small store has ten categories of product and the following costs and annual demands: Do an ABC analysis of these items. Product Unit Cost (Dm) Annual demand ('00s) H1 60 3 m 232 P2 A3 X1 75 382 R...
-
Describe a periodic review system with an interval of two weeks for the company described in Problem 10.5. Problem 10.5 A company advertises a 95% cycle-service level for all stock items. Stock is...
-
Recall from Chapter 1 that Convex Productions produces full-length motion pictures for distribution worldwide. Convex has just purchased the rights to a movie script entitled Native Sun, which it...
-
Joe Henrys machine shop uses 2,500 brackets during the course of a year. These brackets are purchased from a supplier 90 miles away. The following information is known about the brackets: a) Given...
-
Consider the following five situations: maintenance of a network of ATMs, produc- tion and packaging of cake mix, a barn raising in which an entire fast-food restau- rant is torn down and completely...
-
Read pp 10-25 in the text "Sustainable Fabrics for Fashion" and summarize it in a 245 words. Source a fashion article or trade article that has to do with this topic. List this article in the paper...
-
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...
-
Baer Inc. has been manufacturing its own shades for its table lamps. The company is currently operating at 100% of capacity, and variable manufacturing overhead is charged to production at the rate...
-
Florescent Minerals processes materials extracted bom mines. The most common raw material that it processes results in three joint products: Sarco, Barco, and Larco. Each of these products can be...
-
Michelle Tracy, a recent graduate of Rolling's accounting program, evaluated the operating performance of Poway Company's six divisions. Michelle made the following presentation to Povvay's Board of...
Study smarter with the SolutionInn App