Spreadsheet Problem

In: Business and Management

Submitted By Traveling
Words 616
Pages 3
Cumberland Industries' financial planners must forecast the company's financial results for the coming year. The forecast for many items will be based on sales, and any additional funds needed will be obtained as notes payable.

a. Assuming the historical trend continues, what will sales be in 2010? Base your forecast on a spreadsheet regression analysis of the 2004-2009 sales data above, and include the summary output of the regression in your answer. By what percentage are sales predicted to increase in 2010 over 2009? Is the sales growth rate increasing or decreasing?

Here are the company's historical sales. Hint: Use the Trend function to forecast sales for 2010.

Year Sales Growth Rate
2004 129,215,000
2005 180,901,000 40.0%
2006 235,252,000 30.0%
2007 294,065,000 25.0%
2008 396,692,000 34.9%
2009 455,150,000 14.7%
2010 515,465,267

% Increase in Predicted Sales for 2010 over 2009:

2009 Sales 455,150,000
2010 Sales 515,465,267

% increase 13.25% Note: This growth rate has been declining over time.

b. Cumberland’s management believes that the firm will actually experience a 20 percent increase in sales during 2010. Construct 2010 pro forma financial statements. Cumberland will not issue any new stock or long-term bonds. Assume Cumberland will carry forward its current amounts of short-term investments and notes payable, prior to calculating AFN. Assume that any Additional Funds Needed (AFN) will be raised as notes payable (if AFN is negative, Cumberland will purchase additional short-term investments). Use an interest rate of 9 percent for short-term debt (and for the interest income on short-term investments) and a rate of 11 percent for long-term debt. No interest is earned on cash. Use the beginning of year debt balances to calculate net interest expense. Assume that dividends grow at an 8 percent rate.

Key Input…...

Similar Documents

Spreadsheet

...Spreadsheet Analysis & Modeling MIS-505 Questions for Final Exam 1. What is name range? How can ranges be named? What are its uses? 2. What is lookup in Excel? What are the different kinds of lookup functions? What are its uses? 3. What is Index function? Write down the procedure of index function. What are its uses? 4. What is Match function? How you can write an Index and Match functions in a nested way? What are its uses? 5. Describe different kinds of text functions in Excel (such as left, right, mid). 6. How can you put date in Excel? What are its uses? 7. What is NETWORKINGDAYS function in Excel? What is NETWORKINGDAYS.INT function in Excel? What are its uses? 8. What is workday and WORKDAY.INT functions in Excel? What are its uses? 9. What is Net Present Value? How do NPV and XNPV function work in Excel? What are its uses? 10. What is internal rate of return (IRR)? How do IRR, XIRR, and MIRR work in Excel? What are its uses? 11. What is present value? How does PV function work? What are its uses? 12. What is Future Value? How does FV function work? What are its uses? 13. Write down the functions of PMT, PPMT, IPMT. How can you use these functions to prepare a loan amortization schedule? 14. Write down the function of IF Statement, AND, OR and XOR. 15. Describe different Time functions. What are its uses? 16. What is What If Analysis? Describe different kind of what if analysis. What are its uses...

Words: 474 - Pages: 2

Use of Spreadsheets in Business and Technology

...Use of Spreadsheets in Business and Technology Marty Montgomery August 25, 2013 Many corporations depend on spreadsheets as a fundamental (Institute of Corporate Auditors, 2006) device in their financial reporting and operational processes. As a result, the use of spreadsheets is an essential part of the information and decision-making structure for these organizations. In developing and using spreadsheets, organizations need to balance their ease and flexibility against the value of reliable information for management’s application. As users of spreadsheet applications such as Microsoft Excel® or Lotus 1-2-3® have become more sophisticated, so have spreadsheets. Once used to support simple functions such as logging, tracking and totaling information, spreadsheets with enhanced formulas and built-in advanced features are now used to support such business functions as complex valuation models. The use of macros and multiple spreadsheets which are linked together allows users to build very complicated—and sometimes convoluted—models and other business functions with minimal or no documentation. In addition, these complex spreadsheets are not usually supported by the same control environment as formally-developed, purchased applications. For example, the developers and users of spreadsheets are usually not trained in structured programming, testing, version control or systems development life cycles, and spreadsheets are rarely restricted from unauthorized access by......

Words: 949 - Pages: 4

Spreadsheet Modeling

... SPREADSHEET MODELING IN CORPORATE FINANCE To accompany Principles of Corporate Finance by Brealey and Myers CRAIG W. HOLDEN Richard G. Brinkman Faculty Fellow and Associate Professor Kelley School of Business Indiana University Prentice Hall, Upper Saddle River, New Jersey 07458 To Kathryn, you’re the inspiration, and to Diana and Jimmy, with joy and pride. Craig CONTENTS Preface PART 1 TIME VALUE OF MONEY Chapter 1 Single Cash Flow 1.1 Present Value 1.2 Future Value Problems Chapter 2 Annuity 2.1 Present Value 2.2 Future Value 2.3 System of Four Annuity Variables Problems Chapter 3 Net Present Value 3.1 Constant Discount Rate 3.2 General Discount Rate Problems Chapter 4 Real and Inflation 4.1 Constant Discount Rate 4.2 General Discount Rate Problems Chapter 5 Loan Amortization 5.1 Basics 5.2 Sensitivity Analysis Problems PART 2 VALUATION Chapter 6 Bond Valuation 6.1 Basics 6.2 By Yield To Maturity 6.3 System Of Five Bond Variables 6.4 Dynamic Chart Problems Chapter 7 Stock Valuation 7.1 Two Stage 7.2 Dynamic Chart Problems Chapter 8 The Yield Curve 8.1 Obtaining It From Bond Listings 8.2 Using It To Price A Coupon Bond 8.3 Using It To Determine Forward Rates Problems Chapter 9 U.S. Yield Curve Dynamics 9.1 Dynamic Chart Problems PART 3 CAPITAL BUDGETING Chapter 10 Project NPV 10.1 Basics 10.2 Forecasting Cash Flows 10.3 Working Capital 10.4 Sensitivity Analysis Problems Chapter 11 Cost-Reducing......

Words: 49278 - Pages: 198

Accounting Problems

...SUBDOMAIN 319.1 - ACCOUNTING & FINANCE SUBDOMAIN 319.2 - INFORMATION TOOLS Competency 319.1.2: Ratios - The graduate determines the financial condition of a firm using financial ratios and other financial data. Competency 319.2.1: Technology Tools - The graduate uses information technology tools for specified business purposes. Objectives: 319.1.2-04: Determine a firm’s financial condition by calculating and benchmarking specified ratios against other companies in the industry. 319.2.1-01: Use the Internet to gather information on a specified business topic. 319.2.1-02: Produce business letters or memorandums using a word processing application. 319.2.1-03: Develop a spreadsheet report that solves a business problem. Introduction: Company G operates a small chain of wholly owned home centers selling to consumers and contractors. Sales volume varies among the individual stores and ranges between $11 million and $20 million per year. The company is organized as a corporation and does not operate as a sub-chapter S corporation. Given: Consolidated financial statements for Company G are shown on the attached “Statement Analysis Template.” • Sales volume shown is net sales. • During Year 12 60% of net sales were on a credit basis. • Inventory is stated at cost on a first-in, first-out basis. • Depreciation expense totaled $1,875,000 for Year 12 and $1,644,000 for Year 11. • Depreciation expense is not shown as a separate item on the income statements......

Words: 894 - Pages: 4

Business Computing Excel Spreadsheet

...Used: * Microsoft Excel 2007 (Home edition) * Microsoft Word 2008: (License No: 92464-513-2506215-12994) * Microsoft Expression Web (Student version) Attached Files: * Business Report.docx * Spreadsheet Assignment.xlsm * Website files * home.html * about.html * sit.html * work.html * registration.html * locate.html * contact.html * Formulae sheets Attempted the High Distinction Level Table of Contents 1.0 Executive Summary 3 2.0 Business Problem Opportunity 4 3.0 Recommendations (based on ‘What-if’ analysis) 5 3.1 Production targets 5 3.2 Fixed costs 5 3.3 Sales 6 4.0 Appendices 7 4.1 User instructions 7 1.0 Executive Summary Classic Offices is a company that manufactures and sells chairs. The recent recession of 2009 had a negative global effect on markets all over the world and Classic Offices was adversely affected too. Along with vital information such as income and commission figures, the increment of fixed costs and reduction of profits have been captured in the form of this user-friendly spreadsheet. This spreadsheet has been designed with the consideration of Classic Offices’ internal users in mind. To make interpretation of the spreadsheet easier: * Different years have been colour coded * Appropriate command buttons (e.g. ‘Home’, ‘Print’) are accompanied with icons Internal users of Classic Offices (mainly employees) will be able......

Words: 1137 - Pages: 5

Spreadsheets

...Strengths and Weaknesses of Each Spreadsheet (Teacher) Aims to help the teacher: * Recording Results * Tracking the Learners’ Progress Strengths: One of the strengths of the teacher’s spreadsheet was the use of conditional formatting. Conditional formatting was helpful by easily presenting data, by formatting a range of cells depending on certain grades or scores means that analysing and categorising data will much easier with a visual representation and the teacher can make more accurate conclusions. The way the tables are laid out, and by defining a range is very useful for a teacher when it comes to neatly organising a spreadsheet with many tables of class’s results. By defining a range means that tables are easy to input into formulae, and will help reduce errors. AutoSum is very beneficial to a teacher because it simply adds up an entire column without having to add each cell individually. This saves a lot of time, and helps reduce errors, especially if there is lots of students’ data. Weaknesses: Macros are not included in the spreadsheet, which would’ve been very useful for a teacher. Macros stop repetitive tasks being completed over and over again. This is useful as it saves a lot of time to the teacher, e.g. if they click a customised button which filters data or changes sheets. This will also improve the usability of the spreadsheet and make the data input easier. Goal seek, which also isn’t included in the spreadsheet, would also be very......

Words: 1576 - Pages: 7

Modeling and Solving Lp Problems in a Spreadsheet

...Modeling and Solving LP Problems in a Spreadsheet Chapter 3 C.T. Ragsdale. 2008. Spreadsheet Modeling & Decision Analysis, 5th E. Revised, Thompson 1 Section 1 EXCEL SOLVER 2 Introduction • Solving LP problems graphically is only possible when there are two decision variables • Few real-world LP have only two decision variables • Fortunately, we can now use spreadsheets to solve LP problems 3 LP Solvers • Conventional – MPS (IBM) – LINDO, GINO – GAMS – AMPL • Algebraic Language • Spreadsheet Modeling • The company that makes the Solver in Excel, Lotus 12-3, and Quattro Pro is Frontline Systems, Inc. Check out their web site: http://www.solver.com – Frontline Solver, Premium Solver, Risk Solver – What’s Best? 4 Steps in Implementing an LP Model in a Spreadsheet 1. Organize the data for the model on the spreadsheet. 2. Reserve separate cells in the spreadsheet for each decision variable in the model. 3. Create a formula in a cell in the spreadsheet that corresponds to the objective function. 4. For each constraint, create a formula in a separate cell in the spreadsheet that corresponds to the left-hand side (LHS) of the constraint. 5 The Simple Farm Model Again! max π = x1 + 1.5x2 s/t x1 + 2x2 ≤ 160 3x1 + 2x2 ≤ 240 x1 ≥ 0, x2 ≥ 0 6 Implementing the Model See file FarmEx original.xls 7 Cell Labels 8 How Solver Views the Model • Target cell - the cell in the spreadsheet that represents the objective......

Words: 2610 - Pages: 11

Chapter 4. Solution to End-of-Chapter Comprehensive/Spreadsheet Problem

...Chapter 4. Solution to End-of-Chapter Comprehensive/Spreadsheet Problem Problem 4-24 Corrigan Corporation's December 31 Balance Sheets Assets 2011 2010 Cash $72,000 $65,000 Accounts receivable 439,000 328,000 Inventories 894,000 813,000 Total current assets $1,405,000 $1,206,000 Land and building 238,000 271,000 Machinery 132,000 133,000 Other fixed assets 61,000 57,000 Total assets $1,836,000 $1,667,000 Liabilities and equity Accounts and notes payable $432,000 $409,500 Accrued liabilities 170,000 162,000 Total current liabilities $602,000 $571,500 Long-term debt 404,290 258,898 Common stock 575,000 575,000 Retained earnings 254,710 261,602 Total liabilities and equity $1,836,000 $1,667,000 Corrigan Corporation's December 31 Income Statements 2011 2010 Sales $4,240,000 $3,635,000 Cost of goods sold 3,680,000 2,980,000 Gross operating profit $560,000 $655,000 General admin. and selling epenses 236,320 213,550 Depreciation 159,000 154,500 Miscellaneous 134,000 127,000 EBT $30,680 $159,950 Taxes (40%) 12,272 63,980 Net income $18,408 $95,970 Per-Share Data 2011 2010 EPS $0.80 $4.17 Cash dividends $1.10 $0.95 Market price......

Words: 1055 - Pages: 5

Database Program vs. Spreadsheet Program

...Database program Vs. Spreadsheet program When choosing which type of program you should use to manage your customer records, you need to know what the difference is between the two. Spreadsheets are designed to analyze data and sort list items, not for long-term storage of raw data. A spreadsheet should be used for ‘crunching’ numbers and storage of single list items. Spreadsheet programs provide the means for keeping inventory, statistical data modeling, and computing data. They also include graphing functions that allow for quick reporting and analysis of data. Spreadsheet programs are relatively easy to use, require little training to get started, and have the advantage that most data mangers are somewhat familiar with them. The disadvantages of spreadsheets include having to re-copy data over and over again to maintain it in separate data files, the inability to efficiently identify data errors, the lack of detailed sorting and querying abilities, and sharing violations among users wishing to view or change data at the same time. Additionally, spreadsheets are restricted to a finite number of records, and can require a large amount of hard-drive space for data storage. Databases require little or no duplication of data between information tables, and changes made to the data do not corrupt the programming. Databases offer better security to restrict users from accessing privileged information, and from changing coded information in the programming. Furthermore, the......

Words: 415 - Pages: 2

Spreadsheet Modelling

...Word Count: 587 This is my own unaided work . SPREADSHEET mODELLING Word Count: 587 . SPREADSHEET mODELLING Spreadsheet modelling As a future financial analyst my work will be based on analysing data from company’s gathered data spreadsheets. Hence it is crucial for my own career success to understand the importance of spreadsheet modelling and its implementation. As according to (Susan Coles, Jennifer Rowley, 1996) “The decision maker’s judgement must be exercised in the interpretation of the data and the final decision making.” There is no better way of benefiting my future career than developing advanced skills in modelling Excel. As I have learned from few articles I read about this subject, there are many advantages of excelling my skills so as problems I should look out for. Firstly, studying advanced Excel modelling not only helps students to build technological knowledge but “also largely improve their practical analytical skills and abilities.”(Junying, 2010). By studying how to model spreadsheets students get a real chance to see how actual data is analysed by spreadsheet build in models and help them to further their own analytical knowledge and see how it works in real life. I think it is hugely beneficial for students in their further real...

Words: 715 - Pages: 3

Business Spreadsheets

...Business Spreadsheets. Name: Course: Instructor: Institution: Date of submission: 1) Calculate X-Bar-Bar, R-Bar, and associated control limits using the data in the table above.  From the data we have, the X-Bar-Bar number can be seen at the top set of results titled Centre x-bar bar = 5.1 a. The R-Bar result is the “Center” result the bottom set of data is R-Bar. Therefore R-Bar = 1.083 2) Create X-Bar•R (Average & Range) Control Charts from the data in the table above.  The X-Bar Control Chart is shown below Control limits for the x - chart are: Control limits = x- bar ± A2 (MR) Control limits = 5.1 ± 0.729 *1.083 Control limits = 4.31 to 5.89 Control limits ranges between 4.31 to 5.89 The R Control Chart is shown below Control limits for R- chart are: Upper control limits = (MR) * D4 Upper control limits = 2.282 *1.083 Upper control limits = 2.47 Lower control limit = D3 * MR Lower control limit = 0 * 1.083 Lower control limit = 0 3) Discuss notable out-of-control conditions displayed in the completed X-Bar•R (Average & Range) Control Charts. We consider points outside the control limits. We do not consider runs, set of points within certain regions. From the X-Bar control chart, we can see that two points 19 and 21 are above the upper control limit. From the R-Chart we can see that one point, 18 is above the upper control limit. 4. If...

Words: 890 - Pages: 4

Is There a Problem

...Incident 1: Is there a problem ad 1.) Dorothy overlooked some of the proper recruiting practices, when reviewing the recruitment and selection process Dorothy did not continue with the procedure, in fact she didn’t go past the second step. Dorothy did not provide a job description or job specifications. 2.) Dorothy mentioned good credit is needed for the job which is a blatant faux pas in the ad. That it something that should have been brought up in a interview. Including good appearance in the ad is also a bias requesting that only individuals with good looks can apply for the job. 3.) Better recruitment sources that could be a recruiting at a college since Dorothy specified that she is looking for a college graduate with a good neat appearance. A professional association could help Dorothy narrow down her candidates for her software engineer position. Incident 2: There’s no future here 1.) The career development for this company did a very bad job at career development. Bob was never given any development for him to assume a Terry’s spot. The career planning was also flawed because the company never gave Bob any career goals. Bob was trained but not developed and was not given any career path. I can understand Bob’s frustration. 2.) The company was not a learning organization. The company should have given Bob more training to be ready to undertake Terry’s role once he left. Management development could have been a strategy they could have......

Words: 358 - Pages: 2

Problems

...machinery useful life to lower the amortization cost. I would recommend Mr. Magers to try to implement/formulate a LP, in which he should try minimize cost for series 200. The procedures I would recommend hiring cheap labor, minimizing power cost by running the machinery in an efficient manner, try to find a different vendor for their raw materials. I would also recommend to try to find a different vendor for 100 series so they can match prices with Bosworth, because if Bosworth can lower the 100 series price to $2.25, they must be making some kind of profit in order to lower the price and still be successful, which means that Berkshire too hasn’t utilized efficient ways to produce their 100 series product. Maybe formulating a LP module/Spreadsheet to have their cost as variables in determining the objective function of minimizing cost for 100 series. It can depend on the raw material costs, how much power should be utilized per 100 series product, hiring cheap labor, extending the useful life of the product to minimize depreciation cost (set it to 30 instead of 20) and to find a cheap rental price for their storage of products....

Words: 339 - Pages: 2

Bond Valuation Spreadsheet

...Week 3 DQ 2 Forecasting Methods Read Problem 6 in Chapter 6 of your textbook.  Calculate and answer parts a through d. Include all calculations and spreadsheets in your post. Explain why the moving average method was used instead of another forecasting method. What might be another forecasting method that could prove to be just as useful?  Your initial post should be 200-250  words. Below see the number of mergers that took place over a 12-year period in the savings and loan industry. |Year |  |Mergers |  |Year | |2005 |61 |52.6 |8.4 |70.56 | |2006 |83 |55.6 |27.4 |750.76 | |2007 |123 |63 |60 |3,600 | |2008 |97 |75.2 |21.8 |475.24 | |2009 |186 |85.6 |100.4 |10,080.16 | |2010 |225 |110 |115 |13,225 | |2011 |240 |142.8 ...

Words: 573 - Pages: 3

Problems

...the problem with stating the need for genuine indisputable examples of design and purpose because who is the judge or standard of what is indisputable? McCloskey may look at the complexities of DNA to RNA and the order of life as natural progression of evolution. In fact, McCloskey implies that evolution displaces the need for a designer. I, however, disagree. One thing that evolution does is offers an explanation contrary to intelligent design regarding the scientific observations of the natural order of life. While evolution provides the scientific explanation of natural order, it does not explain the creation or ultimate beginning of such natural order. Could not a theist come back in reply to McCloskey and state that a necessary being or God put this scientific process of natural order in place? C. Stephen Evans gives a great analogy when he likens this to a man’s desire to produce shoes. The man creates a machine to achieve the production of shoes, therefore allowing an individual to explain mechanically the structure and visuals of the shoe. However, the machine is not ultimately responsible for the creation of the shoes. Instead, the machine “operates as it doesbecause it is a means for achieving the result; it was designed to do so.”[9] In sum, if evolution were absolutely true, it does not displace the need for a designer. Instead, one can plausibly declare that a designer designed the process of evolution as a means for achieving certain results. The Problem......

Words: 554 - Pages: 3