Industrial Engineer Office Automation cheat sheet

Spreadsheet formula references: A1 is relative (shifts on copy), $A$1 absolute (fixed), $A1 column-locked, A$1 row-locked; a circular reference occurs when a formula's dependency chain returns to its own cell and blocks calculation until resolved.

Spreadsheet lookup functions: VLOOKUP searches the leftmost column of a range and returns a column index; XLOOKUP/INDEX-MATCH remove the left-column restriction and default to exact match, reducing silent errors from approximate-match defaults.

Mail merge requires a main document, a data source (table/sheet with a header row), and merge fields; one record produces one output document, and filtering rules applied to the data source control which records merge.

Macro security: VBA macros can execute arbitrary code, so macro-enabled files (.xlsm/.docm) are blocked by default in modern suites; Trust Center policy governs enablement, and signed macros from trusted publishers are the controlled alternative.

Document versioning and co-authoring rely on server-side storage (cloud or document management system) with check-out/check-in or real-time co-authoring locks; local-only copies break auditability and create merge conflicts.

Accessibility compliance for office documents: alternative text on images, logical heading structure for screen readers, minimum contrast ratio 4.5:1 for normal text (WCAG 2.x AA), and tagged PDF export preserve reading order.

Productivity metrics: a macro or template's payback period = development cost / (time saved per use x uses per period x loaded labor rate); automate only tasks whose annual savings exceed maintenance cost.

Template governance: store approved templates in a read-only central library with version numbers; users instantiate copies, so brand and compliance changes propagate without overwriting completed documents.

Normalization: 1NF removes repeating groups (atomic values); 2NF removes partial dependencies on part of a composite key; 3NF removes transitive dependencies (non-key attributes depending on other non-key attributes); BCNF requires every determinant to be a candidate key.

ACID properties: Atomicity (all-or-nothing), Consistency (constraints preserved), Isolation (concurrent transactions do not interleave illegally), Durability (committed changes survive failure). Isolation levels trade consistency against concurrency, with anomalies such as dirty read, non-repeatable read, and phantom read.

SQL joins: INNER returns matching rows only; LEFT/RIGHT return all rows from one side with NULLs for unmatched; FULL returns both; CROSS is the Cartesian product (m x n rows).

Keys and integrity: a primary key is unique and non-null; a foreign key references a primary or unique key and enforces referential integrity; a candidate key is any minimal unique identifier, and one is chosen as primary.

Data warehouse vs. OLTP: OLTP systems are normalized for write throughput; warehouses use star schemas (fact table plus dimension tables) or snowflake schemas for read-oriented analytical queries, with ETL/ELT loading and slowly changing dimensions to track history.

Indexing: a B-tree index speeds equality and range lookups at the cost of write amplification and storage; a covering index includes all columns a query needs; low-selectivity columns (e.g., gender) rarely benefit from indexing alone.

Backup and recovery: RPO (recovery point objective) sets maximum tolerable data loss and drives backup frequency; RTO (recovery time objective) sets maximum tolerable downtime and drives restore architecture; a differential backup captures changes since the last full, a log backup captures changes since the last log backup.

Data quality dimensions: completeness, accuracy, consistency, timeliness, uniqueness, and validity; master data management assigns one system of record per entity to prevent conflicting golden records.

Little's Law: average work-in-process (WIP) = arrival rate x average flow time (L = lambda x W). Use it to predict queue buildup or to infer flow time from WIP and throughput.

Process cycle efficiency (PCE) = value-added time / total lead time; low PCE indicates waiting and rework dominate, so improvement targets queues rather than touch labor.

BPMN core elements: events (start, intermediate, end), activities/tasks, gateways (exclusive XOR splits one path, parallel AND forks all paths, inclusive OR takes one or more), sequence flows within a pool, and message flows between pools.

Rework loops and decision points are modeled with gateways and back-flows; a process with a rework probability p has expected visits per unit = 1/(1-p) for a single loop, which inflates effective capacity demand.

Value stream mapping distinguishes value-added, necessary non-value-added, and pure waste steps; the seven wastes (TIMWOOD: transport, inventory, motion, waiting, overproduction, overprocessing, defects) guide the tagging.

Capacity and utilization: utilization = arrival rate / service rate; as utilization approaches 1, queue length and wait time grow nonlinearly (queuing effect), so design headroom rather than 100% loading.

Theory of Constraints: identify the bottleneck, exploit it, subordinate other steps to it, elevate it, then repeat; system throughput is set by the constraint, and non-bottleneck improvement does not raise output.

Process mining uses event logs (case ID, activity, timestamp) to reconstruct actual process flows; conformance checking compares the discovered model against the intended model to expose deviations and rework.

Standard time = observed (normal) time x (1 + allowance factor), where normal time = observed time x performance rating; allowances cover personal, fatigue, and delay (PFD).

Time study sample size: n = (z x s / (e x x_bar))^2 for a desired relative error e at confidence z (1.645 for 90%, 1.96 for 95%), using a preliminary sample's mean and standard deviation.

Predetermined time systems (e.g., MTM) build standard times from elemental motion codes (reach, move, grasp, position, release) rather than stopwatch observation, giving consistent times without rating subjectivity.

Work sampling estimates proportion of time in an activity: n = z^2 p (1-p) / e^2; use p = 0.5 for the most conservative sample size when p is unknown.

Learning curve: time for unit n is T_n = T_1 x n^b, where b = log(learning rate)/log(2); an 80% curve means each doubling of cumulative output reduces unit time to 80% of the prior value.

Ergonomics limits: NIOSH lifting equation computes a recommended weight limit from load constant, horizontal/vertical/distance/asymmetry/frequency multipliers; a lifting index above 1.0 signals elevated risk.

Anthropometric design: use 5th percentile for reach limits and 95th percentile for clearance dimensions; adjustability accommodates the range between rather than a single average user.

Workstation guidelines: monitor top at or slightly below eye level at about 50-70 cm viewing distance, elbows near 90 degrees, feet supported, and neutral wrist posture; repetitive strain risk rises with force, repetition, posture, and lack of recovery time.

IPv4 private ranges: 10.0.0.0/8, 172.16.0.0/12, 192.168.0.0/16; a /24 subnet has 256 addresses with 254 usable hosts (network and broadcast reserved).

DHCP assigns addresses dynamically via DORA (Discover, Offer, Request, Acknowledge); DNS resolves names to addresses; a DHCP reservation pins a device to a fixed address by MAC.

VLANs segment a LAN logically at Layer 2; inter-VLAN traffic requires Layer 3 routing, and 802.1Q tags carry VLAN IDs on trunk links.

Wi-Fi standards: 802.11n (Wi-Fi 4) on 2.4/5 GHz; 802.11ac (Wi-Fi 5) 5 GHz only; 802.11ax (Wi-Fi 6) on 2.4/5/6 GHz with OFDMA for dense-client efficiency; WPA2/WPA3 encrypt with AES-CCMP/GCMP.

PoE (802.3af up to 15.4 W, 802.3at up to 30 W, 802.3bt up to 60/90 W) powers phones, access points, and cameras over the same Ethernet cable, simplifying office cabling.

RAID levels: RAID 0 stripes with no redundancy; RAID 1 mirrors; RAID 5 stripes with single parity (survives one drive failure); RAID 6 dual parity (survives two); RAID 10 mirrors then stripes for performance plus redundancy.

UPS types: offline/standby switches on power loss; line-interactive regulates voltage with an AVR; online/double-conversion continuously conditions power for critical servers. Runtime scales with load and battery capacity.

Cloud service models: IaaS supplies virtual compute/storage/network; PaaS supplies a managed runtime; SaaS supplies the finished application. Shared responsibility splits security duties between provider and customer by model.

Critical path method: the critical path is the longest path through the network; its length equals the shortest project duration, and its activities have zero total float. Total float = LS - ES = LF - EF.

Earned value: EV = % complete x BAC; PV is planned value; AC is actual cost. CV = EV - AC, SV = EV - PV, CPI = EV/AC, SPI = EV/PV; EAC = BAC/CPI when cost performance persists.

PERT three-point estimate: expected = (O + 4M + P)/6, standard deviation = (P - O)/6; combine independent activity variances by summing variances, not standard deviations.

Scope control uses a change control board and a documented change request; unapproved scope additions cause scope creep and invalidate the baseline for EV measurement.

SDLC models: waterfall is sequential with late feedback; iterative/incremental delivers in cycles; agile uses timeboxed sprints with a prioritized backlog and a definition of done.

Go-live strategies: big-bang (fast, high risk), parallel run (redundant cost, high safety), phased (by module or site), and pilot (limited users first). Cutover planning includes data migration, rollback, and user training.

Risk response types: avoid, mitigate, transfer (insurance, contracts), accept; for opportunities: exploit, enhance, share, accept. A risk register records probability, impact, owner, and trigger.

Requirements traceability matrix links each requirement to design, build, and test artifacts; user acceptance testing (UAT) validates fitness for business use before sign-off.

CIA triad: confidentiality (encryption, access control), integrity (hashing, checksums, change control), availability (redundancy, backups, DDoS protection); non-repudiation uses digital signatures.

Symmetric encryption (AES) is fast and uses one shared key; asymmetric encryption (RSA, ECC) uses public/private key pairs for key exchange and signatures; TLS combines both with certificates from a trusted CA.

Hashing (SHA-256) is one-way and collision-resistant, used for integrity and password storage with salting and slow key derivation (bcrypt, PBKDF2, Argon2); encryption is reversible, hashing is not.

Authentication factors: something you know (password), have (token, phone), are (biometrics); MFA requires two or more different factor types, and phishing-resistant MFA uses hardware security keys or passkeys.

Access control models: RBAC assigns permissions to roles; ABAC evaluates attributes; MAC uses labels and clearance. Least privilege and separation of duties limit insider risk.

GDPR principles: lawfulness/fairness/transparency, purpose limitation, data minimization, accuracy, storage limitation, integrity/confidentiality, accountability; data subject rights include access, rectification, erasure, and portability, with breach notification generally within 72 hours to the supervisory authority.

Incident response phases: preparation, detection and analysis, containment/eradication/recovery, post-incident lessons learned; chain of custody preserves evidence for legal use.

Backup rule (3-2-1): at least three copies, on two different media, with one offsite or offline; immutable/air-gapped copies defend against ransomware encryption of connected backups.

Expected monetary value (EMV) = sum of probability x payoff across outcomes; for a decision tree, choose the branch with the highest EMV, and the value of perfect information = EV with perfect information - EV without it.

Break-even quantity = fixed cost / (price - variable cost per unit); contribution margin per unit is price minus variable cost, and operating leverage rises with fixed cost share.

Payback period = initial investment / annual net cash flow (uniform flows); NPV discounts all cash flows at the cost of capital and accepts projects with NPV > 0; IRR is the discount rate making NPV zero.

Linear programming: an objective function is optimized subject to constraints; the optimum lies at a vertex of the feasible region (corner-point method), and sensitivity analysis reports shadow prices and allowable ranges.

Forecasting: moving average smooths by averaging the last k periods; exponential smoothing uses F(t+1) = alpha x A(t) + (1-alpha) x F(t), where higher alpha tracks recent changes faster; MAD, MAPE, and MSE measure accuracy.

Economic order quantity: EOQ = sqrt(2DS/H), where D is annual demand, S is order cost, and H is holding cost per unit per year; total cost is minimized where ordering cost equals holding cost.

Queuing (M/M/1): utilization rho = lambda/mu; average number in system L = rho/(1-rho); average wait in queue Wq = rho/(mu - lambda). Stability requires lambda < mu.

Decision criteria under uncertainty: maximax (optimistic), maximin (pessimistic), Hurwicz (weighted optimism), minimax regret (minimizes worst opportunity loss), and Laplace (equal probabilities).