Basic Modelling Case

During the construction of a perimeter wall around a private house, five men were employed. The daily rates of pay were

• Jack = Team leader £400
• John = Second in command £330
• Joe, Jeb and Jim = Team members £300

The house owner has agreed these rates of pay and has agreed to pay the men at the end of each week and at the end of the job, if it comes before the end of a week.

Task

• Construct a table in Excel that serves the following purposes
• Provides a record of attendance of everyone who works on the job
• Provides the times they worked on the job: whole days and half days
• Shows the total money earned
  • per day
  • cumulatively
  • Shows the payments made by the house owner
  • Has a final column that shows the cumulative amount owing at any time, net of payments made.
  • In this exercise you should use the =SUMPRODUCT() function in addition to any other functions and formulas you feel appropriate.

Your final table might look like the following:

Day No	£300	£300	£300	£330	£400	Total	CumTotal	Paid	Owing
1
2
3

However, if you prefer a different layout for your table, that is fine providing it following the nature of the task set here.

Specimen Data: who worked when …

• Day 1 Jack, Joe, Jeb and Jim all worked a full day
• Day 2 Joe and Jeb worked a full day
• Day 3 John, Joe and Jeb worked a full day
• Day 4 Jack, John, Joe, Jeb and Jim worked a full day
• Day 5 Jack, John, Joe, Jeb and Jim worked a full day
• Day 6 Joe and Jeb worked a half day
• Day 7 Jack, John, Joe and Jeb worked a full day

At the end of day 7 the house owner paid the £5,000 the team leader requested, to be shared among the men.

© Duncan Williamson