write a memo on cost estimation using the excel below
ApEc 3002 Spring 2019 T. Hurley Lab Assignment 7: Cost Function Estimation Due Date: Tuesday, March 25 (11:59 pm online) You’re on the job with the online grocery ordering/delivery service introduced in Lab 5. With plans to expand to new areas of the Metro and the number of orders per week growing in the areas you currently serve, your boss has asked you to analyze how expansion will affect the cost side of the business. Over the past six months, the average revenue per order has remained fairly constant at approximately $95/order. Only since late November, however, have combined average costs for warehouse and delivery operations consistently fallen below $100. The investor group that has funded the start-up of the business has expressed concern about the ability of the business to cover costs and provide them with some return on their investment. Once again, you’ve gone to company records for data you can use in your analysis. It’s saved in an Excel worksheet named APEC 3002 Lab 07S19.xlsx. In addition to data on orders per week (labeled Orders in the worksheet), you’ve asked accounting to provide data on weekly costs. They maintain and have provided cost records on two distinct aspects of your company’s operations. One series of cost figures is for warehouse operations (labeled Warehouse Variable Cost in the worksheet). This includes labor and supply costs for all activities related to ordering product and assembling grocery orders for customers, as well as the cost of goods sold. The second series of cost figures is for delivery of orders to customers’ homes (labeled Delivery Variable Cost in the worksheet). This includes labor for drivers and dispatchers, fuel, and other vehicle expenses. Because these two aspects of the overall business may be affected differently by expansion, you’ve decided to examine cost-output relationships for them separately. Since the time period for the analysis is relatively short and all the data are from the same operation, you have decided to ignore prices in your analysis. You’ll simply estimate parameters for two variable cost functions that are simple relationships between cost and output: VCWH = f(Orders) where VCWH stands for Warehouse Variable Cost VCDEL = f(Orders) where VCDEL stands for Delivery Variable Cost You can choose the functional form. The multiplicative exponential and polynomial are good candidates. In your write-up (which should be a memo addressed to your boss, Matt Bombyk), you should describe the basic features of your analysis including the purpose and your data sources, explain your choice of functional form, and present your estimation results.1 Then you should turn to the real issue – how expansion from nearly 3,500 to as many as 7,000 orders per week will affect costs in the warehouse and for deliveries. Are there economies or diseconomies of size for these activities? Will average variable costs increase or decrease for these two activities with expansion? What will happen to overall average variable costs per order – i.e., the sum of average variable costs for the two activities? What do your findings suggest about the ability of the business to cover variable costs as your service expands? 1 Include Excel output as an attachment, but do not put the Excel output in the main body of your report. Rather, in your memo you should report estimation results in equation form, as we did in earlier labs. Grading Rubric for Lab Assignment 7 Not Present Weak Satisfactory Strong Outstanding Background Information Purpose of analysis 0 2 4 5 5 Data – description and source 0 2 4 5 5 Analysis Identify and justify choice of functional form 0 2 4 5 5 Present and interpret estimation results, including discussion of economies of size. 0 10 16 18 20 Project AVC for Warehouse, Delivery, and the combination of the two over the range 3,500 to 7,000 orders – present in a table or a graph. 0 10 16 18 20 Interpretation Discussion of prospects for future profit contributions. 0 5 8 9 10 Address Target Audience 0 5 8 9 10 Memo Format 0 2 4 5 5 Layout and Appearance 0 5 8 9 10 Grammar and Mechanics 0 5 8 9 10 Comments: Final Grade: