The batch weights listed in column C are currently specified as "pounds, ounces." We would like to change the batch weights to pounds with a decimal fraction.
others
Description
D. The batch weights listed in column C are currently specified as "pounds, ounces." We would like to change the batch weights to pounds with a decimal fraction.
- Insert two blank columns to the right of column C.
- Apply the Text to Columns tool to cell range C10:C34 to separate pounds and ounces into separate columns based on the comma delimiter character. Change the column C heading to Quantity (Pounds). Insert column D heading as Quantity (Ounces).
- In column E, insert a formula that adds the weight in pounds to the weight in ounces converted to a decimal pound value. Note, you convert weight in ounces to a decimal pound value by dividing the ounces by 16. Add a heading to column E of Total Weight. Format all values in cell range E10:E34 to display four decimal places.
E. Turn cell range A9:L34 into a table with table style Medium 15.
F. Add a Total row to the table.
G. Create a formula in column G to calculate the actual number of days the cheese has aged, based on difference between today's date found in G7 (leave as (9/1/19) and the date the batch was created in column F. Format the result as a number with two decimal places.
H. Each cheese type has an optimal aging time (in months), shown in cells A39:B48. In column H, create a function to insert the optimal aging time for the type of cheese made in each batch, using the lookup table in cell A39:B48.
I. A problem with Actual Aging Time exists. The Optimal Aging Time in column H is in months, while the Actual Aging Time in column G is in days. Adjust the formula in column G to convert the calculated days to months by dividing the calculated days by 30.
J. With the date unit problem corrected, create a function in column I to display Yes if the Actual Aging Time is greater than the Optimal Aging Time and No if otherwise.
K. Insert a new column between the Selling Price (Per Pound) column and the Cost to Produce column. Insert Batch Revenue as the column heading. Insert a formula in the new column to calculate the revenue from each batch (total weight of cheese in the batch multiplied by the selling price).
L. In column N, insert Total Storage Cost in cell N9 as the new column heading. Then insert a formula to calculate the cost of storing the batch based on the actual number of months it has aged and the monthly storage cost.
M. In column O, insert Total Profit in cell O9 as the new column heading. Create a formula in column O to calculate the total profit expected from the cheese batch (batch revenue minus all batch expenses). For expenses, remember that each batch incurs the cost to product the batch and the actual cost of storing the batch. Apply the Accounting format to the entire Total Profit column. The sum of the Total Profit column should be :$15,857.08.
