Can you please tell me how to create a detailed estimate that provides the total (aggregate) projected cost on Excel? I only need you to answer Q1.

Global Green Books Publishing continues to produce customized eBooks as a new product line. They have developed a standard WBS that helps them develop effective Project Charters.

Customers expect Global Green Books to deliver eBooks at a low cost, but the company has not always been able to deliver on this expectation. This is partly because the Accounting department in Global Green Books was having difficult tracking the costs associated with each eBook. The lack of effective tracking is preventing effective budget management on each project.

Global Green Books now wants to develop detailed budget estimates for each new eBook project. The budgets should capture direct costs and indirect costs. The direct costs for an eBook project should include labour costs for Global Green Books staff, material costs, subcontractor/outside labour and new equipment costs. Material costs for these eBooks would include any copyright permission costs for content and images used in an eBook. Indirect and General Administration costs (facility related expenses, support staff in areas like Project Management, Accounting etc.) will be covered by applying an overhead rate on direct labour.

Global Green Books has received a new order for a European History course. See Appendix A for general assumptions on activity durations associated with this eBook.

In addition to the internal labour costs, the Desktop Publishing Manager has estimated that the European History eBook will incur the following additional costs:

• An overhead rate on all direct labor costs of 1.5 (to cover indirect and general administration costs)
• Material costs of $1,000 for each copyright permission needed (37 copyright permissions are required)
• Equipment costs of $800 for unique equipment needed for this project (one special oversize map scanner)
• One-time cost associated with the installation and training of the oversize map scanner. In addition, this work is being outsourced to a subcontractor at a fixed price of $500.

In addition to direct and indirect costs, Global Green Books targets a 20% profit margin on each project, and budgets for a 20% contingency on labour costs and a 10% contingency on copyright permission costs based on the average risk level of its eBook orders.

Samantha asked your team to develop the budget for this eBook project. The budget should have 2 key components:

1. a detailed estimate that provides the total (aggregate) projected cost and
2. a time-phased budget that will help the organization manage cash flow.

For the first component, the detailed estimate, Samantha would like to know the total project cost and the total labour costs by phase of work. Since labour represents the largest component of this project budget, isolating it will allow her to easily track variances.

In creating the time-phased budget, assume that the eBook project must be completed within 60 days. This will allow you to estimate the cash flow needs of the project by spreading the costs over a 60-day period. Some key assumptions are listed in Appendix A. Once your budget is approved, a detailed schedule will be created. The detailed schedule will confirm the projects duration.

Appendix A

Global Green Books keeps track of actual activity durations and general labour costs in their estimating database. The following averages can be used for each new eBook project. Adjustments are made for each new eBook project as warranted. Adjustments consider unique project information identified during Project Planning. Here is a list of some of the assumptions you can make in your budget. You will need to identify other assumptions in order to complete the budget estimates for all of the WBS deliverables. Note: 1 day is assumed to be 8 hours long.

• Customer Service Reps earn $18 per hour and take an average of 3 hours to check and verify each new order.
• The desktop publishing team leads the planning phase which takes about 20 hours in total. The average salary in the desktop publishing team is $30 per hour.
• The desktop publishing team spends an average of 120 hours (3 weeks) producing an eBook.
• Quality Assistants earn $50 an hour and spend an average of 4 hours on each quality check performed.
• Marketing Coordinators earn $35 an hour and spend an average of 10 hours creating a marketing plan for customers.

I have not provided hourly rates for the Project Manager (Samantha) and other support staff as their costs are covered by the overhead rate.