JUST DO ONE EXCELL THE FIRST ONE

Williamson Manufacturing Transportation Model Data Shipping Cost Per Unit Units Shipped Instructions: 1. Deliverables: 3 Excel files submitted to Blackboard only. No email sent submissions will be accepted. For each problem, please name the file as: "A3_Problem\#_yourLastNameFirstName". Problem\# is Problem1, Problem2, etc. 2. Late assignments will NOT be accepted. All assignments must be individual and own works. Students submitting similar works may both/all receive 0 s grade and face possible disciplinary actions. No handwriting assignments will be accepted. 3. For each problem, please create an Excel workbook for it (or use the provided data file), and work on sheets inside that workbook. If the problem has multiple parts, such as (a), (b), (c), you should name/arrange your answer/response explicitly. For instance, you may name the sheets as part a, part b, etc. Please use comment or textboxes generously in sheets to answer, describe and annotate your response to the problems, your approach, model, and ideas as clearly as possible. If the problem requires or implies an explicit answer, such as "compute the coefficient of correlation", please work out the computation and type your answer explicitly. 4. You are allowed to use as needed any Excel built-in or add-in functionalities to solve the problems. Before submitting your work, make sure to properly test your Excel files to assure no corrupted file submissions. For visualization components such as charts and tables, you are expected to follow the guidelines described in the textbook to produce them as professional as possible. 5. Grading will be based on your performance, not efforts. The total is 100 points. 6. These assignment problems come from the textbook, except the last problem. You can utilize the template/example of the data files used through the book. Problem 1 (name the Excel file as A3Problem1_yourLastNameFirstName.xlsx) 35 points total Eastman Publishing Company is considering publishing an electronic textbook about spreadsheet applications for business. The fixed cost of manuscript preparation, textbook design, and web site construction is estimated to be $160,000. Variable processing costs are estimated to be $6 per book. The publisher plans to sell single-user access to the book for $46. (No Data file needed, create from the given information) a. (15 points) Build a spreadsheet model to calculate the profit/loss for a given demand. What profit can be anticipated with a demand of 3,500 copies? b. (15 points) Use a data table to vary demand from 1,000 to 6,000 in increments of 200 to assess the sensitivity of profit to demand. c. (5 points) Use Goal Seek to determine the access price per copy that the publisher must charge to break even with a demand of 3,500 copies. Problem 2 (name the Excel file as A3Problem2_yourLastNameFirstName.xlsx) 35 points total (Data file needed: A3Problem2.xlsx) Newton Manufacturing produces scientific calculators. The models are N350, N450, and N900. Newton has planned its distribution of these products around eight customer zones: Brazil, China, France, Malaysia, U.S. Northeast, U.S. Southeast, U.S. Midwest, and U.S. West. Data for the current quarter (volume to be shipped in thousands of units) for each product and each customer zone are given in the file A3Problem2.xlsx. Newton would like to know the total number of units going to each customer zone and the total units of each product shipped. There are several ways to get this information from the data set. One way is to use the SUMIF function. The SUMIF function extends the SUM function by allowing the user to add the values of cells meeting a logical condition. The general form of the function is: =SUMIF( test range, condition, range to be summed ) The test range is an area to search to test the condition, and the range to be summed is the position of the data to be summed. So, for example, using the file Newton, we use the following function to get the total units sent to Malaysia: =SUMIF(A3:A26,A3,C3:C26) Cell A3 contains the text "Malaysia"; A3:A26 is the range of customer zones; and C3:C26 are the volumes for each product for these customer zones. The SUMIF looks for matches of "Malaysia" in column A and, if a match is found, adds the volume to the total. Use the SUMIF function to get total volume by each zone and total volume by each product