Cells F7:F9 contain the total amount of each production input available to Beacon in this production cycle. Cells E7:E9 calculate the total amounts of each production input that will be used in the production cycle based on the corresponding number of units of Arkel and Kallex that are produced. Summary of production of washing machinesĬells C7:C9 contain the amount of each production input required in the production of each unit of Arkel, while cells D7:D9 contain the amount of each production input required in the production of each unit of Kallex. Details of the relevant facts are summarised in the table "Summary of Production of Washing Machines".īased on these facts and the assumption that 100% of production will be sold, Beacon must decide how many units of each washing machine to produce in the coming production run to maximise profits. At the same time, manufacturing each Arkel unit requires 18 hours of labour, 6 feet of rubber hosing, and 1 drum, while manufacturing each Kallex unit requires 12 hours of labour, 8 feet of rubber hosing, and 1 drum. Selling each Arkel unit earns the company a profit of $350 while selling each Kallex unit earns the company a profit of $300. In particular, it has only 3,132 hours of labour, 1,440 feet of rubber hosing, and 200 drums available. In the coming production cycle, Beacon faces key resource constraints. At the start of every production cycle, Beacon must decide how many units of each washing machine to produce, given its available resources. It currently sells two models of washing machines: the Arkel and the Kallex. Mathematical representation of Beacon's business problemīeacon Co. The technique can be used in many other accounting and business settings to help decision-makers determine optimal outcomes given limited resources. This example provides one setting where linear programming can be applied. The example below demonstrates how a management accountant could use the Solver tool to perform linear programming to determine an optimal product mix that maximises profits given a limited set of resources. It's instead an introduction to the topic and how the Excel Solver add-in can be used to help with this type of complex problem. It should also be noted that while all the mathematical expressions for the objective function and constraints in linear programming are necessarily linear in nature (hence the name see the sidebar "Limitations of Linear Programming" at the bottom of the page), the technique remains one of the most widely used methods of optimisation, and the largest and most complex linear programming problems have millions of decision variables and hundreds of thousands of constraints.īefore we continue, it's important to note that this article is not intended to be an exhaustive course in linear programming. Instead, they usually have bounds (eg, ≥ 0).
LINEAR PROGRAMMING SOLVER FOR EXCEL PLUS
LINEAR PROGRAMMING SOLVER FOR EXCEL FREE
This article provides a description of linear programming, demonstrates how it can be performed using Microsoft Excel's free Solver add-in, and illustrates its use through an example from management accounting.
Linear programming is one technique that accountants can often readily apply to determine the best outcome in these situations. It is therefore important that they apply appropriate analytical techniques in approaching such decisions. Many of these decisions require management accountants to determine or recommend specific courses of action that would lead to an optimal outcome (such as maximising profits or minimising costs) given a limited set of resources (such as production inputs).
A management accountant's knowledge of relevant revenues and costs is important for many decisions, among them capital budgeting, outsourcing, special orders, product mix, and the adding or dropping of specific product lines.
0 kommentar(er)
