Wages and Variable Overheads, have been calculated, by multiplying the units of production (200 units) with the overheads charged per unit (30 pounds and 33 pounds after October).
Fixed Overheads are charged normally for each month reduced by 300 pounds per month, due to depreciation.
Debenture interest is charged for 10% to Loan divided by 2, due to six months (half year) calculation of it .
- After calculating the cash budget we are then ready for the budgeted profit & loss account.
Depreciation of 1,800 will be charged to Machinery and Equipment, as Land & Buildings can’t be depreciated
- Finally we calculate the budgeted Balance Sheet for the year end 30 December 2000.
- In a word processed report to management of no more than 2000 words you are required to appraise the benefits and drawbacks which may occur with the use of computers in the budget process.
A budget spreadsheet is useful to both small and large companies. The spreadsheet also allows cost items to be entered for each period or month of the year. Spreadsheets includes a general data entry area that lets you set up some guiding assumptions and parameters for budget calculation.
Whatever spreadsheet is employed, the modeler should be aware of a common structure and appearance. In simplistic terms, a spreadsheet is a software program which consists of rows and columns of cells which combine to form a grid or worksheet. The spreadsheet is user-friendly in appearance and is readily accessible, even to those model users with limited computing experience. From the point of view of the management accountant, one advantage of developing a budget using a spreadsheet is that the formulae which form the basis of many business budgets can be executed both quickly and automatically. Specifically, each cell on the spreadsheet grid can contain text, number or formulae which can be combined appropriately by the accountant to develop a formal budget. Because the spreadsheet is informal, the resultant budget can be used by end users with varying computing experience.
Unfortunately, creating a large spreadsheet manually is time consuming and tedious, even when you use a calculator or copy results from a computer printout. Another problem with manual spreadsheets is that making a mistake is too easy. If you do not discover the mistake, the consequences can be serious. If you discover the mistake after the spreadsheet is finished, you must manually redo all the calculations that used the wrong number.
Through the spreadsheets, the accountant, or the user of the computerized budgeting process can divide monthly the imports of the businesses raw materials and finished goods as well as their exports to the production department. So, he can have a clear cybernetic view of the stock for each month and of course for the year end. This can be done by using a certain formula, in order to find the closing stock (Opening stock + Purchases – Production)
The user can work the same way, in order to find the closing stock of the finished goods by adding the Opening stock with the stock which has been used for the production, and by reducing the units, which has been sold out. By this way he presents the closing stock of raw materials and finished goods with a great detail, invitingly and unerringly.
Spreadsheets have the ability to present the sales which have been budgeted for each month and for the year end, and the cost of goods manufactured (production costs, wages & variable overheads etc.).
The user can find the debtors and creditors figures for the end of the year, by crediting or debiting this figures, according to the conditions which have been agreed, about their debit or credit. In this occasion, spreadsheets can do all the work for him. All that the user got to do in this occasion is to program the spreadsheet, to account for a certain amount of months to the creditors and debtors. For example, in “question 1” we are told that all the customers have 2 months to pay, thus they get charged for 44,500 (November 22,000, December 22,500). These amounts can be filled automatically, if the cell next to Debtors is equal to the sum of November and December sales figures (D25=H17+I17). The same counts for the creditors, if their cell is equal to the multiply of the last month purchases of Raw Materials by their purchase price. This way can be found the closing stock of Raw Materials and Finished Goods.
Before the budgeting of the two major financial statements, the “Budgeted Profit & Loss Account” and the “Budgeted Balance Sheet”, the cash budget take place. As well as before, spreadsheets can show in a detail for each month the cash movements of the Business, by aggregate the cash receipts (customers, loans, augmentations etc.) and ablate the payment tickets (Purchases, wages & variable overheads, fixed overheads, interests, taxes etc.), showing a detailed view to the sections which affected the cash movement into the business.
Spreadsheets go on to the budgeted Profit & Loss Account, by calculating the gross profit of the business for each month by ablating the cost of sales from the businesses sales figures. The retained profit for the year can be found by ablating the total expenses from the gross profit.
The ability to store and retrieve data is central to any business information system. Consequently, an important feature of spreadsheet software is its ability to store and retrieve the data with which it works.
In a budget application, for example, the spreadsheet may be stored on a hard or floppy disc and retrieved several times during the budgeting process. Each time, one or more of the budget categories may be changed. It may also be useful to retrieve the budget again several weeks or months into the budget period, to compare actual costs with those expected when the budget was first developed.
Another important feature of spreadsheet software is that spreadsheet can be used as a standard format into which data can be downloaded from the central computer facilities. In this process, data stored in a central database are requested through the user’s terminal or microcomputer. The data are then modified and even summarized so that they can fit into the row-and-column format of a spreadsheet, and then they are entered into the spreadsheet.
This feature gives end-users a significant advantage. It means that business professionals with only spreadsheet skills can access centralized corporate data and process them according to their own needs. Without such a link, they might have to ask the central data processing department to obtain and process the data, an alternative that frequently takes more time and costs more money than the value of the data merits.
Some spreadsheet packages can also graph the data contained in cells. The user can plot the data as a line bar graph or as a pie chart.
Graphics are used when a visual picture of the data can communicate a message more effectively than can a column or table of numbers. This is an especially effective way to show trends or comparisons (Let’s say comparison between the budgeted and the actual movement of the business, or even a comparison between the budgeted months etc.).
The ability to accept formulae and to automatically recalculate when figures are adjusted makes spreadsheet packages ideal for planning applications such as those used in cost accounting. In fact, it would appear that every software house that has written a spreadsheet package has included a cash budget as an example in their manual.
The advantages of using a spreadsheet for this type of application are that most formulae only need to be entered once and can then be copied to all of the other cells and automatically incremented by the spreadsheet, and also that an of the figures can be adjusted and the spreadsheet will automatically recalculate the new answers.
Spreadsheets can be used for any application that the user requires (providing the user has the necessary skill with the package) but are most often used for budgeting, forecasting, stock control, job costing, contract costing, forecasting and variance analysis. Whilst programs can be obtained or written to perform any one of these functions, the spreadsheet has the benefit of being able to perform as many applications as the user is capable of designing a spreadsheet for.
One disadvantage with spreadsheets in the past was the need to possess a good understanding of their commands in order to make effective use of them. This is not always as straight forward as it sounds, as most spreadsheets packages have a larger range of commands than some high level languages. This problem has been largely overcome by the increased sophistication of the macro commands in most spreadsheets.
Like any other tool electronic spreadsheet can be misused, consequently, using this powerful software is not without some risk. Before the age of the electronic spreadsheet, some business decisions were made on instinct alone. Now, with electronic spreadsheets, users can rely more on financial facts. Building very complex financial models is relatively easy, and comparing many different scenarios can help people make sound decisions. Used carefully, an electronic spreadsheet can help increase profitability.
One problem is that users can forget that they are manipulating a model and not the real company. A financial model is just numbers inside a computer, merely an electronic image of a real company or even of an imagined one. Spurred by the excitement of playing with the numbers, users can forget that a company is more than numbers – it also includes people, corporate image and mission, corporate and individual values and ethics, and many other intangibles. Decisions that affect a company should often be supported by facts from computer, but not the exclusion of other factors.
A second problem is that computers have a tendency to legitimize things. It seems normal that the output from an electronic spreadsheet program (or from any other program) is only as accurate and realistic as the formulas and input entered by the user. But some people do not realize that. It is easy to present true and accurate figures with an electronic spreadsheet. It is just as easy to present incorrect, incomplete, or misleading information with a spreadsheet. The problem here is a societal one – we tend to believe that what the computer tells us is true, especially when the results are printed into a professional – looking report.
As a result, we must take extra care to question the spreadsheet author about the source of his or her input, the formulas used, and the assumptions made. Computer printouts look so authoritative that we often accept them with complacency. We must instead question their authority.
Budget making becomes complex when budget information from all departments and field offices of a company is accumulated into a single budget for the whole company. If this sounds like a nightmare, it usually is one. However, nightmare or not, it is essential that a business plan its activities and measure its performance against such a budget. Even more important, the managers who prepare the budgets must be willing to honor them as the year unfolds. The discipline of cost budget preparation is much more evident on a year-to-year basis in large companies than in small businesses. Small businesses tend to prepare such budgets to support obtaining funds for start-up or expansion.
A Budgetary control system includes also a cash budget analysis. The cash budget is a tool to recognize the cash position of a company on a month to month basis. Finally the cost budget feeds a variance analysis area that lets you compare actual costs to budgeted costs on a monthly and year-to-date basis.
Not all spreadsheet applications are as successful as the one described. In fact, there are a growing number of reports of how erroneous spreadsheet data have led to unfortunate decisions. Budgets have been approved, schedules finalized, and alternative projects chosen, all using bad data.
But spreadsheets can be deceptively simple to build and use. And those who are unfamiliar with their mechanics can easily get into trouble. Perhaps the user is unfamiliar with the assumptions made when the template was built. Or, unknown to the user, formulas, hidden from direct view, could have been entered incorrectly. Sometimes the user enters data incorrectly. And users may access centrally stored data that are inappropriate for use in a particular template.
Certainly spreadsheet output – such as table graph – looks very professional. But a responsible user must go beyond the aesthetics and carefully check the assumptions of the spreadsheet template, the data, and the procedures which were used to obtain calculated entries.
