When BB Construction receives a new assignment, it takes place according to the following procedure:

• A client or a contractor who has entered into a design-build contract on behalf of the client announces a call for tenders in the market.
• The call for tenders specifies the type of work to be performed, when the work is to begin, and when it is expected to be completed.
• If this is a project that BB Construction has the capacity and expertise to carry out, the company prepares a preliminary estimate, which is a budget of the project's expected costs and the price the company must charge to ensure a sufficient contribution margin.
• Based on the conclusion of the preliminary estimate, BB Construction submits a bid. It is common for the company to be asked to reconsider the price of the work, and such requests are sometimes met during further negotiations with the client.

BB Construction has for many years used a project calculation model calledStrict. Since this software is no longer being developed and is not compatible with newer versions of Windows, the company has decided to develop its own spreadsheet-based project calculation model. This model will be used both for the preliminary estimates that form the basis of the bids and as a forecasting model during the execution of the work.

The company has a policy of not bidding on a project if the expected contribution margin based on the preliminary estimate is less than25%, even though the average achieved margin has been just under20%. By setting the contribution margin to a minimum of 25%, the company has accounted for potential discounts in competitive situations, as well as the risk of project costs exceeding expectations.

The spreadsheet-based calculation model must include the following components:

• The project's material costs
• The project's costs for any subcontractors
• The project's wage and personnel costs
• Calculation of achieved contribution margin
• Calculation of achieved contribution ratio
• Average achieved revenue per working hour in the project
• Average achieved contribution margin per working hour in the project
• The project's full cost, based on an allocation of indirect administrative costs according to a percentage rate
• The project's net contribution after allocation of indirect administrative costs

Task 8 First, make a calculation model that satisfies the above requirements and use it to find the bid price, where:

• Material costs are NOK 2,263,000
• Costs for subcontractors are NOK 809,000
• Total wage and personnel costs in the project are NOK 4,608,000
• A total of 9,600 working hours are planned for the project
• Budgeted administrative costs are 16% of budgeted revenues

Your model must be designed so that the bid price is calculated automatically and that it is easy to assess how this changes as a result of changes in the variables listed above.