Authors: Michelle Ragghianti
Patrick Dotson
Trent Halford
The purpose of this report is to describe the workings of several spreadsheets and propose a new format that would be more efficient and user-friendly. These spreadsheets are used in a business to determine production requirements, estimate sales, and create proforma financial statements. After describing the spreadsheets, a proposal for a new user-friendly interface will be introduced.
This spreadsheet is used as the data source for the other three spreadsheets. The main data is imported into this spreadsheet and linked to the others through formulas. The first thing a user must do is specify which disk drive the data is imported from. Then the data is imported and available for use. Models for demand forecasting are also set up in this spreadsheet for use in the sales spreadsheet.
What major decisions are made
in this functional area?
No real decisions are made in this spreadsheet; it is the basis for all other functional areas to use for data.
What inputs and what outcomes
are required to help decision making?
Inputs in this spreadsheet are data from competitors such as information on price, demand, sales, materials, wages, and productivity. Also, seasonal indices are imported along with other numbers that may be used in demand forecasting. No outcomes are found in this spreadsheet.
What kind of analyses could
one do?
This spreadsheet does not actually perform the analyses; it is used in other spreadsheets to perform analyses, which will be discussed later.
Potential or recommended
improvements?
1. Change the buttons on the ‘DSS Source’ spreadsheet to say ‘Download from Data Warehouse’ instead of drive ‘A’ or ‘B’.
2. More clearly label the data items in the ‘Update’ and ‘Models’ spreadsheet so that a user can tell what all the information is for. Right now, a user cannot tell what a lot of information is, such as the information under Product X on the ‘Update’ spreadsheet.
The format of this spreadsheet is not critical considering the user won’t be using it to perform analyses. As long as the spreadsheet imports the correct data, it doesn’t need a lot of designing.
This spreadsheet is used to estimate Sales for each product being analyzed. It can be used to run what-if analyses to decide what level of sales is necessary to keep the business in profit.
What major decisions are made
in this functional area?
The major decisions made in this spreadsheet are price levels, advertising expenditures, research and development expenditures, and whether there is enough capacity to handle the estimated sales.
What inputs and what outcomes
are required to help decision-making?
Inputs include historical sales levels, historical expenditures on items that affect sales, competitor information, and regression coefficients used in calculating demand. Outputs are capacity availability, demand forecasts, marketing profit, market share, units to manufacture, and marketing costs.
What kind of analyses could
one do?
What-if analyses of demand at different levels of price, advertising expenditures, research and development expenditures, capacity levels, and competitor actions.
Potential or recommended
improvements?
The only part of this spreadsheet that most users would look at is the part that is boxed in. It would make sense to make this the main user interface, and have all the other information on a separate spreadsheet or even hidden from view.
This spreadsheet is used to estimate Production requirements for different levels of Sales. It can be used to run what-if analyses to decide what level of production is necessary and most cost efficient for an estimated amount of sales and inventory levels. These changes can include more workers, more overtime, or a combination of the two.
What major decisions are made
in this functional area?
Major decisions in this functional area include production levels, staffing levels, overtime allowed, and how much to ship at one time.
What inputs and what outcomes
are required to help decision-making?
Inputs required are estimated sales, ending inventory, plant capacity, average employment, average wages, and the percentage of overtime allowed. Outcomes include costs of production, number of employees required to complete work, costs of labor and overtime, and shipping costs.
What kind of analyses could
one do?
What-if analyses on level of production necessary for given amount of sales and inventory. The most cost effective method of staffing can be found with this spreadsheet through manipulating number of employees and overtime levels. Also, the most cost effective shipping plan can be found with this spreadsheet.
Potential or recommended
improvements?
The biggest improvement that could be made to this spreadsheet is hiding the unnecessary information that the user is unlikely to need.
This sheet allows the user to change the different variables per product to determine the impact that the changes in variables will have on the financial outlook of the company. By changing, the variables or drivers, financial managers can determine the level of cash flow necessary to maintain company stability and to implement its current strategy.
What major decisions are made
in this functional area?
The Proforma spreadsheet aids in the financial decision-making at a mid to senior management level. Different scenarios can be analyzed using fluctuations in the cost of goods sold. This sheet allows the user to change the different variables per product to determine the impact that the changes in variables will have on the financial outlook of the company. By changing, the variables or drivers, financial managers can determine the level of cash flow necessary to maintain company stability and to implement its current strategy. Additional criteria include the capital structure and different capital budgeting options including costs for commercial paper, debt, dividends, and expected collection rate.
What inputs and what outcomes
are required to help decision-making?
The inputs in this sheet include:
The outputs for this sheet are related to the balance sheet, income statement, or cash flow for the company based on projections entered in the input area. The proforma outputs create in this sheet allow managers to analyze how different variables will impact the financial outcome of possible decisions.
The outputs include:
What kind of analyses could
one do?
A manager could change variables that are within the control of the company in the near future. For example, the company could easily increase or decrease the advertising budget for the company without large delays, but the company would additional time to hire and train new employees if they wish to exceed capacity beyond its current production capacity. The hiring of new employees will not have immediate impact in the next couple of financial quarters except for overhead cost.
By changing the variables, the company can determine its internal profitability rate and cost of capital. By obtaining its profitability rate and cost of capital, management can determine future investment strategies in its attempt to increase shareholder value. The main idea would be to insure that the profitability rate exceeds the cost of capital where value for the shareholder can be created. The scenarios analysis will help management focus on which variables have the biggest impact, so they can implement or maintain its strategy.
Potential or recommended
improvements?
The proforma sheet needs to reduce the number of variables available to the user. The user of this would benefit by having inputs that would automatically change based on changes in one variable. For example, if the user changes the price of one product, changes in advertising, R&D and production would be changed automatically to meet the new demand at the entered sales price. By allowing the decision maker the ability to change all the variables, conflicts will be created between the input variables. For example, an increase in price may reduce advertising required at the new sales price, whereas the manager may increase price and increase advertising simultaneously which would most likely create conflict leading to incorrect decisions. The idea would be for this decision maker to make changes that his or her knowledge based will allow and leaving other variables to those functions based on their expertise. The changes created by the decision maker will allow that individual the opportunity to test his/her changes with each functionality to determine if his/her changes are achievable.
The proposed system architecture is in a separate file Interfaces.xls. The proposed interfaces for each spreadsheet, Sales, Prodreq, and Proforma are in this file.