3 questions/problems, for each of them, create appropriate Excel models to analyse it (including some comments and documentation). Attached is the project brief, sample problems & solutions.

Documentation Wind speed kmph C4 Parameter a1 L4 Air temperature degree C E4 Parameter a2 N4 Windchill temperature degree C G4 Parameter a3 P4 Windchill effect degree C I4 Parameter a4 R4 Air temperatures degree C C7: T7 Parameter b1 T4 Wind speeds kilometers per hour B8: B19 Windchill temperature degree C C8 =$L$4 +$N$4* C$7+$P$4 *$B8^$T$4+$R$4*C$7*$B8^$T$4 Windchill Wind kmph Deg C: Air 0.3965 a4 Ef f ec t a2 10 5 9. 8 -0. 2 0.6215 13.12 a1 a3 -11.37 LeongTY ProblemSet1a_Soln.xlsx/1.04 Foreign Currency © 2014 Leong Thin Yin. All rights reserved. Ra t e 1. 000 0. 740 0. 620 100. 220 31. 600 1. 250 Equivalent 2,400 1,776 1,488 240,528 75,840 3,000 Smallest Change 50 50 20 5,000 1,000 Description US D Euro € Pound £ Yen ¥ Ba ht Total SGD Initial Basket 204. 43 151. 28 126. 75 20,488. 01 50,000 3,000 Initial % 8. 5 8. 5 8. 5 8. 5 65. 9 100. 0 Final Basket 200 150 120 20,000 50,000 2,973 Final % 8. 4 8. 5 8. 1 8. 4 66. 5 100. 0 Comments 1. The cells are color-coded for easy recognition of input cells.

2. This is particularly important here since inputs are done inside the table itself.

3. M ore work to be done to ensure the abov e model work s for all test cases.

Documentation US dolla r C3 , 1 Euro per USD D3 Pound per US D E3 Yen per USD F3 Ba ht per US D G3 SGD per USD H3 US dolla r C4 Equivalent in Euro D4 =$C4/$C$3*D3 Smallest change C5: G5 Initial USD amount C8 =(C9/100)*($H8/$H$3)*C$3 Input % C9: F 9 , =(100-$G9)/4 Ba ht input a mount G8 Ba ht a s % of initia l ba s k et G9 =(G8/G$3)*$H$3/$H8*100 Initial total SGD H8 =H4 Initial total % H9 =S UM (C9: G9) Final USD amount C11 =RO UND(C8/C$5,0)*C$5 Final USD % C12 =(C11/C$3)*$H$3/$H11*100 LeongTY ProblemSet1a_Soln.xlsx/1.05 Time Sheet © 2014 Leong Thin Yin. All rights reserved. Total Income Tax Fee $/wk Total 25 J ul 26 J ul 27 J ul 28 J ul 29 J ul 30 J ul 31 J ul 1087 22% 8. 00 816 Worker Sun h/day Mon Tue Wed Thu Fri Sat h/day Ra te $/hr Week Total Gross Pay Tax $ Union $ Net Pay Abel, Ann 6 11 5 8 8 12 5 4. 50 55 247. 50 54. 45 8. 00 185. 05 Jones, John 9 4 9 11 4 6 5 6. 50 48 312. 00 68. 64 8. 00 235. 36 Smith, Sam 7 6 7 4 8 6 7 5. 00 45 225. 00 49. 50 8. 00 167. 50 Wall, Joan 5 10 4 4 12 12 8 5. 50 55 302. 50 66. 55 8. 00 227. 95 Comments 1. The dates here are automated to show last week .

2. This giv es a complete week of data and thus allows the pay to be issued.

3. Rev ised documentation for the date cells C4:I 4 are Documentation Date C4 , =I F (B4="",TO DAY()-WEEKDAY(TO DAY())-6,B4+1) Total gross pay L4 =S UM (L7: L10) Income tax rate M4 Union fee N4 Total net pay O4 =S UM (O 7: O 10) Hours work ed C7: I 7 Hourly rate J7 Total hours K7 =S UM (C7: I 7) Gross pa y L7 =K7*J7 Income tax M7 =M $4*L7 Union fee N7 =N$4 Net pa y O7 =L7-M 7-N7 LeongTY ProblemSet1a_Soln.xlsx/1.07 Sprocket Hub © 2021 Leong Thin Yin. All rights reserved. SS/Order ratio 10% Demand Unit Order $ Unit Ca rry $ EOQ # Orders Order $ Ca rry $ Safety Stock $ Total $ 10,000 $0. 50 $15. 00 26 387 $193. 65 $193. 65 $2. 58 $389. 88 b 20,000 $0. 50 $15. 00 37 548 $273. 86 $273. 86 $3. 65 $551. 37 c Test Cases Demand U. O rder $ U. Ca rry $ Order Qty # Orders Order $ Ca rry $ Safety.S $ Total $ 10,000 $0. 50 $15. 00 600 17 $8. 33 $4,500. 00 $60. 00 $4,568. 33 10,000 $0. 50 $15. 00 700 14 $7. 14 $5,250. 00 $70. 00 $5,327. 14 10,000 $0. 50 $15. 00 775 13 $6. 45 $5,812. 50 $77. 50 $5,896. 45 10,000 $0. 50 $15. 00 800 13 $6. 25 $6,000. 00 $80. 00 $6,086. 25 10,000 $0. 50 $15. 00 900 11 $5. 56 $6,750. 00 $90. 00 $6,845. 56 Comments 1. Total cost does not change much when order quantities differ significantly from the EOQ . 2. This suggests that the EOQ need not be strictly followed. 3. The EOQ occurs at where order cost equals carry cost. 4. I t may be better to order at more ty pical time interv als, say daily , week ly or monthly .

5. For case b, the recommendation is to order monthly , i. e. , 12 times each y ear.

Documentation Safety/Order ratio D3 Annual demand B6 Unit order cos t C6 Unit ca rry cos t D6 EOQ E6 , =SQRT(2*B6*C6/D6) Orders/year F6 =B6/E6 Order cost G6 =C6*B6/E6 Ca rry cost H6 =D6*E6/2 Safety stock cost I6 =D$3*E6 Total cost J6 =S UM (G6: I 6) Grading Note s 1. Remember to format order quantities as integers (i. e. , 0 decimal places).

2. This does not mean that the quantities are changed to integers.

3. I t is usually wise to leav e v ariables as real numbers.

4. Where needed, real v alues are changed to integers using ROUND , ROUNDUP , or ROUNDDOWN . 5. The work sheets in this work book can be protected. To protect each work sheet, select Review/Protect Sheet . 6. I nput cells should not be protected when sheet protection is activ ated. They should be conditional formatted to appear shaded when empty .

7. Columns G to J are done for bonus points. The implications are explained in Comments . LeongTY ProblemSet1a_Soln.xlsx/1.08 Paper Products © 2014 Leong Thin Yin. All rights reserved. Annual Sales # 50,000 Breakeven Fixed $ Variable $ Price $ Volume # Volume $ Years Variable % Profit $ 10,000 0. 23 0. 30 142,857 42,857 2. 9 77% 0 1 20,000 0. 18 0. 30 166,667 50,000 3. 3 60% 0 2 50,000 0. 15 0. 30 333,333 100,000 6. 7 50% 0 3 Test Cases To tal Sales Fixed $ Variable $ Price $ Volume # Volume $ Years Variable % Profit $ 20,000 0. 18 0. 30 160,000 48,000 3. 2 60% (800) 20,000 0. 18 0. 30 170,000 51,000 3. 4 60% 400 20,000 0. 18 0. 30 175,000 52,500 3. 5 60% 1,000 20,000 0. 19 0. 30 185,000 55,500 3. 7 63% 350 20,000 0. 20 0. 30 205,000 61,500 4. 1 67% 500 20,000 0. 21 0. 30 230,000 69,000 4. 6 70% 700 Comments 1. At current sales v olumes, option 1 tak es 2. 9 y ears to break ev en.

The manual and electric hand tools currently in used are probably fully depreciated already .

2. At current sales v olumes, option 2 tak es 3. 3 y ears to break ev en.

3. At current sales v olumes, option 3 tak es 6. 7 y ears to break ev en.

4. Sales is howev er expected to grow and thus options 2 and 3 may break ev en sooner.

Documentation Current a nnua l v olume C3 Fixed cost B6 Variable cost C6 Price D6 Brea k ev en v olume E6 =B6/(D6-C6) Brea k ev en s a les F6 =E6*D6 Brea k ev en y ea rs G6 =E6/C$3 Variable cost as % of price H6 =C6/D6 Profit $ I6 =E6*(D6-C6)-B6 Sales volume E14 LeongTY ProblemSet1a_Soln.xlsx/1.08 © 2014 Leong Thin Yin. All rights reserved. Grading Note s 1. M arginal contribution per unit sold is (Price - Cost).

2. Total marginal contribution = M arginal contribution per unit sold * Units sold.

3. Break ev en occurs when the Total marginal contribution = Fixed cost.

4. Equations such as these are not be giv en in real life.

5. You need to consult y our textbook , accountant, or just think on these basics.

Evaluation Is the worksheet true to the specified requirements?

Are all the required test cases computed?

Are there enough test cases to surface possible problems?

Is the worksheet simple and intuitive for another user to use?

Is the worksheet idiot-proof and protected against tampering?

Does this work sheet fits the computer screen well?

Does it fit naturally well on paper within a page or two? Try print prev iew.

Do not use font sizes smaller than those used here. That would be v ery user unfriendly .

Do not reduce the magnification to fit the screen or printed page.

Color is nice but not necessa ry . I t is useful if it helps user to understa nd better.

Color work sheets ma y not come out nicely in bla ck -& -white prints.

Color printout ha s to be rea da ble a fter photocopy ing in bla ck -& -white.

Is the documentation comprehensive?

Does not need to list all cells used in work sheet, but … * must be readable using just the printed copy (without the row and column headers) * must contain all the input and referenced cells * must contain all the unique formula cells with appropriate cell referencing * cell formulas when copied to other cells must still work Are there notes to explain rationale and use of the worksheet?

Are these notes clear and easy to understand?

Print and use this as a benchmark for y our school assignment or work project reference.