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 called Strict. 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 than $25\%,$ even though the average achieved margin has been just under $20\%.$ 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, create 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.