Windchill © 2014 Leong Thin Yin. All rights reserved.

Wind kmph Deg C: Air Windchill Effect a1 a2 a3 a4 b1

5 10 9.8 -0.2 13.12 0.6215 -11.37 0.3965 0.16

Wind Windchill temperatures for different air temperatures (deg C)

kmph 10.0 7.5 5.0 2.5 0.0 -2.5 -5.0 -7.5 -10.0 -12.5 -15.0 -17.5 -20.0 -22.5 -25.0 -27.5 -30.0 -32.5
5 9.8 6.9 4.1 1.2 -1.6 -4.4 -7.3 -10.1 -12.9 -15.8 -18.6 -21.4 -24.3 -27.1 -30.0 -32.8 -35.6 -38.5
10 8.6 5.6 2.7 -0.3 -3.3 -6.3 -9.3 -12.3 -15.3 -18.2 -21.2 -24.2 -27.2 -30.2 -33.2 -36.2 -39.2 -42.1
15 7.9 4.8 1.7 -1.3 -4.4 -7.5 -10.6 -13.7 -16.7 -19.8 -22.9 -26.0 -29.1 -32.2 -35.2 -38.3 -41.4 -44.5
20 7.4 4.2 1.1 -2.1 -5.2 -8.4 -11.6 -14.7 -17.9 -21.0 -24.2 -27.3 -30.5 -33.6 -36.8 -39.9 -43.1 -46.3
25 6.9 3.7 0.5 -2.7 -5.9 -9.1 -12.3 -15.5 -18.8 -22.0 -25.2 -28.4 -31.6 -34.8 -38.0 -41.3 -44.5 -47.7
30 6.6 3.3 0.1 -3.2 -6.5 -9.7 -13.0 -16.3 -19.5 -22.8 -26.0 -29.3 -32.6 -35.8 -39.1 -42.4 -45.6 -48.9
35 6.3 3.0 -0.4 -3.7 -7.0 -10.3 -13.6 -16.9 -20.2 -23.5 -26.8 -30.1 -33.4 -36.7 -40.0 -43.3 -46.6 -49.9
40 6.0 2.6 -0.7 -4.1 -7.4 -10.7 -14.1 -17.4 -20.8 -24.1 -27.4 -30.8 -34.1 -37.5 -40.8 -44.2 -47.5 -50.8
45 5.7 2.3 -1.0 -4.4 -7.8 -11.2 -14.5 -17.9 -21.3 -24.7 -28.0 -31.4 -34.8 -38.2 -41.5 -44.9 -48.3 -51.7
50 5.5 2.1 -1.3 -4.7 -8.1 -11.5 -15.0 -18.4 -21.8 -25.2 -28.6 -32.0 -35.4 -38.8 -42.2 -45.6 -49.0 -52.4
55 5.3 1.8 -1.6 -5.0 -8.5 -11.9 -15.3 -18.8 -22.2 -25.6 -29.1 -32.5 -36.0 -39.4 -42.8 -46.3 -49.7 -53.1
60 5.1 1.6 -1.8 -5.3 -8.8 -12.2 -15.7 -19.2 -22.6 -26.1 -29.5 -33.0 -36.5 -39.9 -43.4 -46.9 -50.3 -53.8

Comments
1. Formula for windchill temperature (deg C) = 13.12 + 0.6215 * T - 11.37 * T * V^0.16 + 0.3965 * V^0.16
2. It should not be hardcoded, in case scientists recalibrate and issue new parameter values.

Documentation
Wind speed kmph C4 <Input> Parameter a1 L4 <Input>
Air temperature degree C E4 <Input> Parameter a2 N4 <Input>
Windchill temperature degree C G4 <Input> Parameter a3 P4 <Input>
Windchill effect degree C I4 <Input> Parameter a4 R4 <Input>
Air temperatures degree C C7:T7 <Input> Parameter b1 T4 <Input>
Wind speeds kilometers per hour B8:B19 <Input>
Windchill temperature degree C C8 =$L$4 +$N$4* C$7+$P$4 *$B8^$T$4+$R$4*C$7*$B8^$T$4

LeongTY ProblemSet1a_Soln.xlsx/1.03
 Foreign Currency © 2014 Leong Thin Yin. All rights reserved.

Rate 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 USD Euro € Pound £ Yen ¥ Baht 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. More work to be done to ensure the above model works for all test cases.

Documentation
US dollar C3 <Input>, 1
Euro per USD D3 <Input>
Pound per USD E3 <Input>
Yen per USD F3 <Input>
Baht per USD G3 <Input>
SGD per USD H3 <Input>

US dollar C4 <Input>
Equivalent in Euro D4 =$C4/$C$3*D3
Smallest change C5:G5 <Input>
Initial USD amount C8 =(C9/100)*($H8/$H$3)*C$3
Input % C9:F9 <Input>, =(100-$G9)/4
Baht input amount G8 <Input>
Baht as % of initial basket G9 =(G8/G$3)*$H$3/$H8*100
Initial total SGD H8 =H4
Initial total % H9 =SUM(C9:G9)
Final USD amount C11 =ROUND(C8/C$5,0)*C$5
Final USD % C12 =(C11/C$3)*$H$3/$H11*100

LeongTY ProblemSet1a_Soln.xlsx/1.04
 Time Sheet © 2014 Leong Thin Yin. All rights reserved.

Total Income Tax Fee $/wk Total

25 Jul 26 Jul 27 Jul 28 Jul 29 Jul 30 Jul 31 Jul 1087 22% 8.00 816
Worker Sun Mon Tue Wed Thu Fri Sat Rate Week Gross Tax Union Net
h/day h/day $/hr Total Pay $ $ 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 gives a complete week of data and thus allows the pay to be issued.
3. Revised documentation for the date cells C4:I4 are

Documentation
Date C4 <Input>, =IF(B4="",TODAY()-WEEKDAY(TODAY())-6,B4+1)
Total gross pay L4 =SUM(L7:L10)
Income tax rate M4 <Input>
Union fee N4 <Input>
Total net pay O4 =SUM(O7:O10)

Hours worked C7:I7 <Input>

Hourly rate J7 <Input>
Total hours K7 =SUM(C7:I7)
Gross pay L7 =K7*J7
Income tax M7 =M$4*L7
Union fee N7 =N$4
Net pay O7 =L7-M7-N7

LeongTY ProblemSet1a_Soln.xlsx/1.05
 Sprocket Hub © 2021 Leong Thin Yin. All rights reserved.

SS/Order ratio 10%

Demand Unit Unit EOQ # Orders Order $ Carry $ Safety Total $

Order $ Carry $ Stock $
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.Order $ U.Carry $ Order Qty # Orders Order $ Carry $ 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. It may be better to order at more typical time intervals, say daily, weekly or monthly.
5. For case b, the recommendation is to order monthly, i.e., 12 times each year.

Documentation
Safety/Order ratio D3 <Input>
Annual demand B6 <Input>
Unit order cost C6 <Input>
Unit carry cost D6 <Input>
EOQ E6 <Input>, =SQRT(2*B6*C6/D6)
Orders/year F6 =B6/E6
Order cost G6 =C6*B6/E6
Carry cost H6 =D6*E6/2
Safety stock cost I6 =D$3*E6
Total cost J6 =SUM(G6:I6)

Grading Notes
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. It is usually wise to leave variables as real numbers.
4. Where needed, real values are changed to integers using ROUND, ROUNDUP, or ROUNDDOWN.
5. The worksheets in this workbook can be protected. To protect each worksheet, select
Review/Protect Sheet.
6. Input cells should not be protected when sheet protection is activated. 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.07
 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 Total 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 volumes, option 1 takes 2.9 years to break even.
The manual and electric hand tools currently in used are probably fully depreciated already.
2. At current sales volumes, option 2 takes 3.3 years to break even.
3. At current sales volumes, option 3 takes 6.7 years to break even.
4. Sales is however expected to grow and thus options 2 and 3 may break even sooner.

Documentation
Current annual volume C3 <Input>
Fixed cost B6 <Input>
Variable cost C6 <Input>
Price D6 <Input>
Breakeven volume E6 =B6/(D6-C6)
Breakeven sales F6 =E6*D6
Breakeven years G6 =E6/C$3
Variable cost as % of price H6 =C6/D6
Profit $ I6 =E6*(D6-C6)-B6

Sales volume E14 <Input>

LeongTY ProblemSet1a_Soln.xlsx/1.08
ghts reserved.

Grading Notes
1. Marginal contribution per unit sold is (Price - Cost).
2. Total marginal contribution = Marginal contribution per unit sold * Units sold.
3. Breakeven occurs when the Total marginal contribution = Fixed cost.
4. Equations such as these are not be given in real life.
5. You need to consult your 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 worksheet fits the computer screen well?

Does it fit naturally well on paper within a page or two? Try print preview.
Do not use font sizes smaller than those used here. That would be very user unfriendly.
Do not reduce the magnification to fit the screen or printed page.

Color is nice but not necessary. It is useful if it helps user to understand better.
Color worksheets may not come out nicely in black-&-white prints.
Color printout has to be readable after photocopying in black-&-white.

Is the documentation comprehensive?

Does not need to list all cells used in worksheet, 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 your school assignment or work project reference.

LeongTY ProblemSet1a_Soln.xlsx/1.08