12 Months         2004 2004 2004 2004 2004 2004 2004 2004 2004 2004 2004 2004 2004
to end Dec         Jan Feb Mar  Apr May Jun Jul Aug Sep Oct Nov Dec Year
ASSUMPTIONS No. 2 - DIRECT COSTS Help with Entering Assumptions
 
      Set cells in this row to zero if not entering seasonal assumptions       Total
Seasonal index ->> 5.00 4.00 4.00 5.00 4.00 4.00 5.00 4.00 4.00 5.00 4.00 4.00 52.0
                           
Finished goods required (based on  
  equivalent sales value in $000): Total
 - Group1 100.0 [1] 120.0 80.0 135.0 55.0 175.0 80.0 145.0 155.0 150.0 125.0 165.0 1,485.0
 - Group2 33.5 80.5 83.8 83.8 63.7 97.2 103.8 114.0 117.3 103.8 134.0 107.2 1,122.6
 - Group3 16.5 39.7 41.3 41.3 31.4 47.9 51.1 56.2 57.8 51.1 66.0 52.8 553.1
 - Group4 85.0 125.0 85.0 105.0 120.0 110.0 120.0 105.0 130.0 130.0 115.0 150.0 1,380.0
Total goods required ($000) 235.0 365.2 290.1 365.1 270.1 430.1 354.9 420.2 460.1 434.9 440.0 475.0 4,540.7
   
Cost of materials/packaging or goods  
 for resale (as % sales):     Set cells below to zero only if a 'pure' services business         Average
 - Group1 ->> 36.00 36.00 36.00 36.00 36.00 36.00 36.00 36.00 36.00 36.00 36.00 36.00 36.00
 - Group2 ->> 38.00 38.00 38.00 38.00 38.00 38.00 38.00 38.00 38.00 38.00 38.00 38.00 38.00
 - Group3 ->> 35.00 35.00 35.00 35.00 35.00 35.00 35.00 35.00 35.00 35.00 35.00 35.00 35.00
 - Group4 ->> 30.00 30.00 30.00 30.00 30.00 30.00 30.00 30.00 30.00 30.00 30.00 30.00 30.00
                           
Cost of materials/packaging or goods required ($000):  
 - Group1 36.0 43.2 28.8 48.6 19.8 63.0 28.8 52.2 55.8 54.0 45.0 59.4 534.6
 - Group2 12.7 30.6 31.8 31.8 24.2 36.9 39.4 43.3 44.6 39.4 50.9 40.7 426.6
 - Group3 5.8 13.9 14.5 14.5 11.0 16.8 17.9 19.7 20.2 17.9 23.1 18.5 193.6
 - Group4 25.5 37.5 25.5 31.5 36.0 33.0 36.0 31.5 39.0 39.0 34.5 45.0 414.0
Total cost of materials ($000): 80.0 125.2 100.6 126.4 91.0 149.7 122.1 146.7 159.6 150.3 153.5 163.6 1,568.8
   
Desired inventory of material/packaging  
 or goods for resale ($000): Opening   Targets ->> Set cells below to zero if a 'pure' services business.          
 - Group1 ->> 50.0 50.0 50.0 50.0 50.0 50.0 50.0 50.0 50.0 50.0 50.0 50.0 50.0  
 - Group2 ->> 20.0 20.0 20.0 20.0 20.0 20.0 20.0 20.0 20.0 20.0 20.0 20.0 20.0  
 - Group3 ->> 10.0 10.0 10.0 10.0 10.0 10.0 10.0 10.0 10.0 10.0 10.0 10.0 10.0  
 - Group4 ->> 40.0 40.0 40.0 40.0 40.0 40.0 40.0 40.0 40.0 40.0 40.0 40.0 40.0  
Total inventory ($000): 120.0 120.0 120.0 120.0 120.0 120.0 120.0 120.0 120.0 120.0 120.0 120.0 120.0  
  The above totals will be included in monthly balance sheets  
Purchases of materials/packaging & goods for resale ($000): Total
 - Group1 36.0 43.2 28.8 48.6 19.8 63.0 28.8 52.2 55.8 54.0 45.0 59.4 534.6
 - Group2 12.7 30.6 31.8 31.8 24.2 36.9 39.4 43.3 44.6 39.4 50.9 40.7 426.6
 - Group3 5.8 13.9 14.5 14.5 11.0 16.8 17.9 19.7 20.2 17.9 23.1 18.5 193.6
 - Group4 25.5 37.5 25.5 31.5 36.0 33.0 36.0 31.5 39.0 39.0 34.5 45.0 414.0
 Total purchases ($000): 80.0 125.2 100.6 126.4 91.0 149.7 122.1 146.7 159.6 150.3 153.5 163.6 1,568.8
   
                          Average
Direct labor (Persons) ->> 10.0 11.0 12.0 13.0 14.0 15.0 16.0 17.0 18.0 19.0 20.0 21.0 15.5
                           
Average payroll & benefits cost per direct     Include wages, bonuses, benefits, overtime and all other payroll costs & taxes     Average
person ($000/person/month) ->> 1.100 1.100 1.100 1.100 1.100 1.100 1.100 1.100 1.100 1.100 1.100 1.100 1.100
                           
Total direct payroll/benefit costs ($000) 11.0 12.1 13.2 14.3 15.4 16.5 17.6 18.7 19.8 20.9 22.0 23.1 204.6
  The variable descriptions in blue below can be changed  
Other direct costs ($000):        X = 0 to 4     Try to include all direct/variable costs here, irrespective of type of business     Total
 -Energy (variable) 2 <<->> 12.5 12.5 12.5 15.0 15.0 17.5 17.5 17.5 17.5 20.0 20.0 20.0 197.5
 -Repairs/maintenance (variable) 1 <<->> 15.0 15.0 15.0 17.5 17.5 20.0 20.0 20.0 20.0 22.5 22.5 22.5 227.5
 -Subcontract 1 <<->>    
 -Consumables A 1 <<->>    
 -Consumables B 1 <<->>    
 -Consumables C 1 <<->>    
Total other direct costs ($000)   27.5 27.5 27.5 32.5 32.5 37.5 37.5 37.5 37.5 42.5 42.5 42.5 425.0
   
Total cost of matl/pack & goods, direct payroll/benefit Total
and other direct costs ($000) 118.5 164.8 141.3 173.2 138.9 203.7 177.2 202.9 216.9 213.7 218.0 229.2 2,198.4
  If manufacturer holding finished goods inventories, enter opening value based on COST valuation  
Total finished goods inventory Opening Also, enter opening values (based on SELLING PRICES) within Sales worksheet  
  (valued at cost in $000) ->> 126.3 118.5 135.4 143.7 156.6 154.3 172.9 179.8 188.4 200.4 211.3 215.5 219.6  
  The above values will be included in monthly balance sheets Total
Cost of sales ($000) 126.3 147.9 133.0 160.3 141.2 185.1 170.4 194.3 204.9 202.8 213.8 225.2 2,105.1
                                     
Note: Cashflows relating to purchases will not be calculated until values have been entered into table 2 at sheet RecPay.
Supplementary Data & Calculations:

[1]
Note that formula in cols H-R differ from that in col G. Formulae in each row below also differ.