You have decided to develop a worksheet for each manufacturing facility manager to submit each week. It should show total production and revenue, and total scrap and cost for each product for the week, if a facility drops below 5,000 units of production on the mini-gizmo, the production number should be highlighted in yellow. Explain, how you will set this up so that the managers enter only the number of units for each product's production and rejects.
To set up the worksheet for each manufacturing facility manager to submit each week, follow the steps below:
Step 1: Open a spreadsheet program like Microsoft Excel or Google Sheets.
Step 2: Create column headers for the required information. For example, you might have columns labeled "Product Name," "Production Units," "Revenue," "Scrap Units," and "Cost."
Step 3: In the "Product Name" column, list all the products for which the manager needs to enter the production and reject units.
Step 4: Leave the "Revenue" and "Cost" columns blank for now, as they will be calculated based on the entered data.
Step 5: In the "Production Units" and "Scrap Units" columns, the manager should enter the respective number of units produced and rejected for each product.
Step 6: To highlight cells where the production drops below 5,000, you can use conditional formatting. Select the cells in the "Production Units" column where the mini-gizmo production is entered. Then, go to the conditional formatting options and set a rule that highlights the cells in yellow if the value is less than 5,000.
Step 7: To calculate the "Revenue" for each product, create a formula that multiplies the production units by the price per unit. You can either hardcode the price per unit or create a separate column where the manager can enter the price for each product.
Step 8: Similarly, calculate the "Cost" by multiplying the "Scrap Units" with the cost per unit. Again, you can either hardcode the cost per unit or create a separate column for the manager to enter the cost for each product.
Step 9: After setting up the calculations, the manager needs to enter the production and reject units for each product, and the "Revenue" and "Cost" columns will be automatically calculated based on the entered data.
Step 10: The manager can review the worksheet, ensuring that the production numbers for the mini-gizmo are highlighted in yellow if they are below 5,000 units. They can make any necessary corrections to the entries.
By following these steps, the worksheet will allow managers to enter only the number of units for each product's production and rejects while automatically calculating the total production and revenue, total scrap and cost for each product for the week.