# Business Driven Technology Study Set 5

## Quiz 21 :Appendix

Question Type
Showing 1 - 20 of 201
Financial Destiny ou have been introduced to Microsoft Excel and are ready to begin using it to help track your monthly expenses and take charge of your financial destiny. The first step is to create a personal budget so you can see where you are spending money and if you need to decrease your monthly expenses or increase your monthly income. Project Focus Create a template for a monthly budget of your income and expenditures, with some money set aside for savings (or you can use the data file, AYK1_Data.xlsx, we created). Create variations of this budget to show how much you could save if you cut back on certain expenses, found a roommate, or got a part-time job. Compare the costs of a meal plan to costs of groceries. Consider how much interest would be earned if you saved $100 a month, or how much debt paid on student loans or credit card bills. To expand your data set, make a fantasy budget for 10 years from now, when you might own a home, have student loan payments, and have a good salary. Data File: AYK1_Data.xlsx Free Essay Answer: Answer: Create a monthly budget template for a person in excel sheet including his projected and actual income and expenditures as shown below: Calculate total projected and actual monthly income of the person as shown below: From the above calculations, it can be concluded that projected monthly income of the person exceeds his actual income by$300.
Calculate total projected and actual monthly expenditure of the person as shown below:
Housing expenses:
From the above calculations, it is concluded that actual monthly expenses on housing have exceeded the projected cost by $12. Transportation Cost: From the above calculations, it is concluded that actual monthly expenses on transportation have exceeded the projected cost by$36.
Insurance cost:
From the above calculations, it is concluded that actual monthly expenses on insurance is equal to the projected cost.
Food cost:
From the above calculations, it is concluded that actual monthly expenses on food is less than the projected cost by $5. Similarly calculate the expenditure on pets, personal care, and entertainment products as shown below: From the above calculations, it is concluded that actual monthly expenses on pets and entertainment meets the projected cost. However, actual expenses on entertainment are less than the projected cost by$37.
Total projected and actual expenses of the person can be calculated by adding his different expenditures on housing, transportation, insurance, food, pets, entertainment and personal care.
Now, calculate the difference between actual and projected income of the person, as follows:
If the person gets extra source of income for the same month, his savings would increase as shown below:
Calculation of Previous savings:
Savings after getting an extra income of $200: Hence, it can be concluded that savings of the person would increase by$200 after getting an additional source of income.
Now, prepare a 10 years budget for the same person after he gets a job and have his spouse and children. This budget would include few additional sources of income and expenditures. Those cells are highlighted in the below template:
Hair/nails

Clothing

Dry cleaning

Health club

Organization dues or fees

Other

Subtotals

Income of the person would increase after getting a job. His expenditures would also increase after getting married and having children. The person would pay loans, taxes and credit bills. Besides this, he would require savings and investments.

Tags
Cash Flow Gears is a five-year-old company that specializes in bike components. The company is having trouble paying for its monthly supplies and would like to perform a cash flow analysis so it can understand its financial position. Cash flow represents the money an investment produces after subtracting cash expenses from income. The statement of cash flows summarizes sources and uses of cash, indicates whether enough cash is available to carry on routine operations, and offers an analysis of all business transactions, reporting where the firm obtained its cash and how it chose to allocate the cash. The cash flow statement shows where money comes from, how the company is going to spend it, and when the company will require additional cash. Gears would like to project a cash flow statement for the next month. Project Focus Using the data file AYK2_Data.xlsx complete the cash flow statement for Gears using Excel. Be sure to create formulas so the company can simply input numbers in the future to determine cash flow. Data File: AYK2_Data.xlsx
Free
Essay

Cash flow statement is the financial statement of a company that represents total income and expenditures for a specific period. Hence, it would help to determine the savings of a company for a certain period of time.
Cash flow statement of Firm G is given below:
From the above calculations, it can be concluded that Firm G would be able to save an amount of $26,211 at the month end. Tags Technology Budget Tally is a start-up website development company located in Seattle, Washington. The company currently has seven employees and is looking to hire six new employees in the next month. Project Focus You are in charge of purchasing for Tally. Your first task is to purchase computers for the new employees. Your budget is$250,000 to buy the best computer systems with a scanner, three color printers, and business software. Use the web to research various products and calculate the costs of different systems using Excel. Use a variety of Excel formulas as you analyze costs and compare prices. Use the data file AYK3_Data.xlsx as a template. Data File: AYK3_Data.xlsx
Free
Essay

Company T is going to hire six new employees in next month. Therefore, they would require purchasing six laptops, a scanner, three colour printers and business software. Total budget allocated for this purpose is $250,000. Prepare an excel sheet to compare the prices of different products offered by Apple, Dell and HP, as shown below: (Here, least expensive products are taken for each brand; company can increase its expenditure on various products as per their requirement) Similarly, calculate the total price of laptops, printers, scanners and software for other brands as shown below: Company T has a total budget of$250,000. Calculate its total expenditures and savings as follows:
From the above calculations, it is clear that the company can save maximum amount of money by purchasing products of Hewlett Packard.

Tags
Tracking Donations Lazarus Consulting is a large computer consulting company in New York. Pete Lazarus, the CEO and founder, is well known for his philanthropic efforts. Pete knows that most of his employees contribute to nonprofit organizations and wants to reward them for their efforts while encouraging others to contribute to charities. Pete began a program that matches 50 percent of each employee donation. The only stipulations are that the charity must be a nonprofit organization and the company will match only up to $2,000 per year per employee. Project Focus Open the data file AYK4_Data.xlsx and determine the following: What was the total donation amount per organization What were the average donations per organization Data File: AYK4_Data.xlsx Essay Answer: Tags Convert Currency You have decided to spend the summer traveling abroad with your friends. Your trip is going to take you to France, England, Italy, Switzerland, Germany, Norway, and Ireland. You want to use Excel to convert currencies as you travel around the world. Project Focus Locate one of the exchange rate calculators on the Internet (www.xe.com or www.x-rates.com). Find the exchange rates for each of the countries listed above and create formulas in Excel to convert$100, $500, and$1,000. Use the data file AYK5_Data.xlsx as a template. Data File: AYK5_Data.xls
Essay
Tags
Cost Comparison You are thinking about purchasing a new computer because the machine you are using now is four years old, slow, not always reliable, and does not support the latest operating system. Your needs for the new computer are simple: anti-virus software, email, web browsing, word processing, spreadsheet, database, iTunes, and some lightweight graphical tools. Your concern is what the total cost of ownership will be for the next three years. You have to factor in a few added costs beyond just the initial purchase price for the computer itself, such as: added hardware (this could include a new printer, docking station, or scanner), software (purchase of a new operating system), training (you're thinking about pursuing web training to get an internship next term), subsequent software upgrades, and maintenance. Project Focus It is useful to think about costs over time-both direct as well as indirect costs. Part of the reason this distinction is important is that a decision should rest not on the nominal sum of the purchase, but rather on the present value of the purchase. A dollar today is worth more than a dollar one year from now. The relevant discount rate (interest rate) is your marginal cost of capital corresponding to a level of risk equal with the purchase. Use the data file AYK6_Data.xlsx as a template. Data File: AYK6_Data.xlsx
Essay
Tags
Time Management You have just been hired as a business analyst by a new start-up company called Multi- Media. Multi-Media is an interactive agency that constructs phased and affordable website marketing, providing its clients with real and measurable solutions that are supported by easy-to-use tools. Because the company is very new to the business arena, it needs help in creating a project management plan for developing its own website. The major tasks for the development team have been identified but you need to create the timeline. Project Focus 1. The task names, durations, and any prerequisites are: Analyze and plan-two weeks. Cannot start anything else until done. Create and organize content-four weeks. Can start to develop "look and feel" before this is done. Develop the "look and feel"-four weeks. Start working on graphics and HTML at the same time. Produce graphics and HTML documents-two weeks. Create working prototype after the first week. Create a working prototype-two weeks. Give to test team when complete. Test, test, test-four weeks. Upload to a web server and test again-one week. Maintain. 2. Using Microsoft Excel or Microsoft Project, create a Gantt chart using the information provided above.
Essay
Tags
Maximize Profit Books, Books, Books is a wholesale distributor of popular books. The business buys overstocked books and sells them for a discount of more than 50 percent to local area bookstores. The owner of the company, BK Kane, would like to determine the best approach to boxing books so he can make the most profit possible. The local bookstores accept all shipments from Books, Books, Books because of BK's incredibly low prices. BK can order as many overstocked books as he requires, and this week's options include: Project Focus When packing a single box, BK must adhere to the following: 20 books or less. Books by three different authors. Between four and eight books from each author. Weight equal to or less than 50 pounds. BK has come to you to help him determine which books he should order to maximize his profit based on the above information. Using the data file AYK8_Data.xlsx, determine the optimal book order for a single box of books. Data File: AYK8_Data.xlsx
Essay
Tags
Security Analysis SecureWorks Inc. is a small computer security contractor that provides computer security analysis, design, and software implementation for the U.S. government and commercial clients. SecureWorks competes for both private and U.S. government computer security contract work by submitting detailed bids outlining the work the company will perform if awarded the contracts. Because all of the work involves computer security, a highly sensitive area, almost all of SecureWorks tasks require access to classified material or company confidential documents. Consequently, all of the security engineers (simply known as "engineers" within the company) have U.S. government clearances of either Secret or Top Secret. Some have even higher clearances for the 2 percent of SecureWorks work that involves so-called "black box" security work. Most of the employees also hold clearances because they must handle classified documents. Leslie Mamalis is SecureWorks' human resources (HR) manager. She maintains all employee records and is responsible for semiannual review reports, payroll processing, personnel records, recruiting data, employee training, and pension option information. At the heart of an HR system are personnel records. Personnel record maintenance includes activities such as maintaining employee records, tracking cost center data, recording and maintaining pension information, and absence and sick leave record keeping. While most of this information resides in sophisticated database systems, Leslie maintains a basic employee worksheet for quick calculations and ad hoc report generation. Because SecureWorks is a small company, Leslie can take advantage of Excel's excellent list management capabilities to satisfy many of her personnel information management needs. Project Focus Leslie has asked you to assist with a number of functions (she has provided you with a copy of her "trusted" personnel data file, AYK9_Data.xlsx): 1. Copy the worksheet Data to a new worksheet called Sort. Sort the employee list in ascending order by department, then by last name, then by first name. 2. Copy the worksheet Data to a new worksheet called Autofilter. Using the Autofilter feature, create a custom filter that will display employees whose birth date is greater than or equal to 1/1/1965 and less than or equal to 12/31/1975. 3. Copy the worksheet Data to a new worksheet called Subtotal. Using the subtotal feature create a sum of the salary for each department. 4. Copy the worksheet Data to a new worksheet called Formatting. Using the salary column, change the font color to red if the cell value is greater than or equal to 55000. You must use the conditional formatting feature to complete this step. Data File: AYK9_Data.xlsx
Essay
Tags
Gathering Data You have just accepted a new job offer from a firm that has offices in San Diego, Los Angeles, and San Francisco. You need to decide which location to move to. Because you have not visited any of these three cities and want to get in a lot of golf time, you determine that the main factor that will affect your decision is weather. Go to www.weather.com and locate the box in which you can enter the city or zip code for which you want information. Enter San Diego, CA, and when the data appear, click the Averages and Records tab. Print this page and repeat this for Los Angeles and San Francisco. You will want to focus on the Monthly Average and Records section on the top of the page. Project Focus 1. Create a spreadsheet to summarize the information you find. 2. Record the temperature and rainfall in columns, and group the cities into four groups of rows labeled Average High, Average Low, Mean, and Average Precipitation. 3. Fill in the appropriate data for each city and month. 4. Because rain is your greatest concern, use conditional formatting to display the months with an average precipitation below 2.5 inches in blue and apply boldface. 5. You also want to be in the warmest weather possible while in California. Use conditional formatting to display the months with average high temperatures above 65 degrees in green and apply an italic font face. 6. Looking at the average high temperatures above 65 degrees and average precipitation below two inches, to which city do you think you should relocate Explain your answer.

There is no answer for this question

Tags
Scanner System FunTown is a popular amusement park filled with roller coasters, games, and water features. Boasting 24 roller coasters, 10 of which exceed 200 feet and 70 miles per hour, and five water parks, the park's attendance remains steady throughout the season. Due to the park's popularity, it is not uncommon for entrance lines to exceed one hour on busy days. FunTown would like your help to find a solution to decrease park entrance lines. Project Focus FunTown would like to implement a handheld scanner system that can allow employees to walk around the front gates and accept credit card purchases and print tickets on the spot. The park anticipates an overall increase in sales of 4 percent per year with online ticketing, with an expense of 6 percent of total sales for the scanning equipment. FunTown has created a data file for you to use, AYK11_Data.xlsx, that compares scanning sales and traditional sales. You will need to create the necessary formulas to calculate all the assumptions including: Tickets sold at the booth. Tickets sold by the scanner. Revenues generated by booth sales. Revenues generated by scanner sales. Scanner ticket expense. Revenue with and without scanner sales. Three year row totals. Data File: AYK11_Data.xlsx
Essay
Competitive Pricing Bill Schultz is thinking of starting a store that specializes in handmade cowboy boots. Bill is a longtime rancher in the town of Taos, New Mexico. Bill's reputation for honesty and integrity is well-known around town, and he is positive that his new store will be highly successful. Project Focus Before opening his store, Bill is curious about how his profit, revenue, and variable costs will change depending on the amount he charges for his boots. Bill would like you to perform the work required for this analysis and has given you the data file AYK12_Data.xlsx. Here are a few things to consider while you perform your analysis: Current competitive prices for custom cowboy boots are between $225 and$275 a pair. Variable costs will be either $100 or$150 a pair depending on the types of material Bill chooses to use. Fixed costs are $10,000 a month. Data File: AYK12_Data.xlsx Essay Answer: Tags Adequate Acquisitions XMark.com is a major Internet company specializing in organic food. XMark.com is thinking of purchasing GoodGrow, another organic food Internet company. GoodGrow has current revenues of$100 million, with expenses of 150 million. Current projections indicate that GoodGrow's revenues are increasing at 35 percent per year and its expenses are increasing by 10 percent per year. XMark.com understands that projections can be erroneous, however; the company must determine the number of years before GoodGrow will return a profit. Project Focus You need to help XMark.com determine the number of years required to break even, using annual growth rates in revenue between 20 percent and 60 percent and annual expense growth rates between 10 percent and 30 percent. You have been provided with a template, AYK13_Data.xlsx, to assist with your analysis. Data File: AYK13_Data.xlsx Essay Answer: Tags Customer Relations Schweizer Distribution specializes in distributing fresh produce to local restaurants in the Chicago area. The company currently sells 12 different products through the efforts of three sales representatives to 10 restaurants. The company, like all small businesses, is always interested in finding ways to increase revenues and decrease expenses. The company's founder, Bob Schweizer, has recently hired you as a new business analyst. You have just graduated from college with a degree in marketing and a specialization in customer relationship management. Bob is eager to hear your thoughts and ideas on how to improve the business and help the company build strong lasting relationships with its customers. Project Focus Bob has provided you with last year's sales information in the data file AYK14_Data.xlsx. Help Bob analyze his distribution company by using a PivotTable to determine the following: 1. Who is Bob's best customer by total sales 2. Who is Bob's worst customer by total sales 3. Who is Bob's best customer by total profit 4. Who is Bob's worst customer by total profit 5. What is Bob's best-selling product by total sales 6. What is Bob's worst-selling product by total sales 7. What is Bob's best-selling product by total profit 8. What is Bob's worst-selling product by total profit 9. Who is Bob's best sales representative by total profit 10. Who is Bob's worst sales representative by total profit 11. What is the best sales representative's best-selling product (by total profit) 12. Who is the best sales representative's best customer (by total profit) 13. What is the best sales representative's worst-selling product (by total profit) 14. Who is the best sales representative's worst customer (by total profit) Data File: AYK14_Data.xlsx Essay Answer: Tags Assessing the Value of Information Recently Santa Fe, New Mexico, was named one of the safest places to live in the United States. Since then, housing development projects have been springing up all around Santa Fe. Six housing development projects are currently dominating the local market-Pinon Pine, Rancho Hondo, Creek Side, Vista Del Monte, Forest View, and Santa Fe South. These six projects each started with 100 homes, have sold all of them, and are currently developing phase two. As one of the three partners and real estate agents of Affordable Homes Real Estate, it is your responsibility to analyze the information concerning the past 600 home sales and choose which development project to focus on for selling homes in phase two. Because your real estate firm is so small, you and your partners have decided that the firm should focus on selling homes in only one of the development projects. From the New Mexico Real Estate Association you have obtained a spreadsheet file that contains information concerning each of the sales for the first 600 homes. It contains the following fields: The following numbers have been assigned to each of the housing development projects: It is your responsibility to analyze the sales list and prepare a report that details which housing development project your real estate firm should focus on. Your analysis should be from as many angles as possible. Project Focus 1. You do not know how many other real estate firms will also be competing for sales in each of the housing development projects. 2. Phase two for each housing development project will develop homes similar in style, price, and square footage to their respective first phases. 3. As you consider the information provided to you, think in terms of what information is important and what information is not important. Be prepared to justify how you went about your analysis. 4. Upon completing your analysis, please provide concise, yet detailed and thorough, documentation (in narrative, numeric, and graphic forms) that justifies your decision. Data file: AYK15_Data.xlsx Essay Answer: Tags Growth, Trends, and Forecasts Founded in 2002, Analytics Software provides innovative search software, website accessibility testing software, and usability testing software. All serve as part of its desktop and enterprise content management solutions for government, corporate, educational, and consumer markets. The company's solutions are used by website publishers, digital media publishers, content managers, document managers, business users, consumers, software companies, and consulting services companies. Analytics Software solutions help organizations develop long-term strategies to achieve web content accessibility, enhance usability, and comply with U.S. and international accessibility and search standards. You manage the customer service group for the company and have just received an email from CIO Sue Downs that the number of phone calls from customers having problems with one of your newer applications is on the increase. This company has a 10-year history of approximately 1 percent in turnover a year, and its focus had always been on customer service. With the informal motto of "Grow big, but stay small," it takes pride in 100 percent callbacks in customer care, knowing that its personal service was one thing that made it outstanding. The rapid growth to six times its original customer-base size has forced the company to deal with difficult questions for the first time, such as, "How do we serve this many customers " One option might be for the company to outsource its customer service department. Before deciding to do that, Analytics Software needs to create a growth, trend, forecast analysis for future predictions. Project Focus 1. Create a weekly analysis from the data provided in AYK16_Data.xlsx. 2. The price of the products, the actual product type, and any warrantee information is irrelevant. 3. Develop a growth, trend, and forecast analysis. You should use a three-day moving average; a shorter moving average might not display the trend well, and a much longer moving average would shorten the trend too much. 4. Upon completing your analysis, please provide concise yet detailed and thorough documentation (in narrative, numeric, and graphic forms) that justifies your recommendations. Data File: AYK16_Data.xlsx Essay Answer: Tags Shipping Costs One of the main products of the Fairway Woods Company is custom-made golf clubs. The clubs are manufactured at three plants (Denver, Colorado; Phoenix, Arizona; and Dallas, Texas) and are then shipped by truck to five distribution warehouses in Sacramento, California; Salt Lake City, Utah; Chicago, Illinois; Albuquerque, New Mexico; and New York City, New York. Because shipping costs are a major expense, management has begun an analysis to determine ways to reduce them. For the upcoming golf season, the output from each manufacturing plant and how much each warehouse will require to satisfy its customers have been estimated. The CIO from Fairway Woods Company has created a data file for you, AYK17_Data. xlsx, of the shipping costs from each manufacturing plant to each warehouse as a baseline analysis. Some business rules and requirements you should be aware of include: The problem presented involves the shipment of goods from three plants to five regional warehouses. Goods can be shipped from any plant to any warehouse, but it costs more to ship goods over long distances than over short distances. Project Focus 1. Your goal is to minimize the costs of shipping goods from production plants to warehouses, thereby meeting the demand from each metropolitan area while not exceeding the supply available from each plant. To complete this project it is recommended that you use the Solver function in Excel to assist with the analysis. 2. Specifically you want to focus on: Minimizing the total shipping costs. Total shipped must be less than or equal to supply at a plant. Total shipped to warehouses must be greater than or equal to the warehouse demand. Number to ship must be greater than or equal to 0. Data File: AYK17_Data.xlsx Essay Answer: Tags Formatting Grades Professor Streterstein is a bit absentminded. His instructor's grade book is a mess, and he would like your help cleaning it up and making it easier to use. In Professor Streterstein's course, the maximum possible points a student can earn is 750. The following table displays the grade equivalent to total points for the course. Project Focus Help Professor Streterstein rework his grade book. Open the data file AYK18_Data.xlsx and perform the following: 1. Reformat the workbook so it is readable, understandable, and consistent. Replace column labels, format and align the headings, add borders and shading as appropriate. 2. Add a column in the grade book for final grade next to the total points earned column. 3. Use the VLookup Function to automatically assess final grades based on the total points column. 4. Using the If Function, format the workbook so each student's grade shows a pass or fail-P for pass, F for fail-based on the total points. Data File: AYK18_Data.xlsx Essay Answer: Tags Moving Dilemma Pony Espresso is a small business that sells specialty coffee drinks at office buildings. Each morning and afternoon, trucks arrive at offices' front entrances, and the office employees purchase various beverages such as Java du Jour and Café de Colombia. The business is profitable. Pony Espresso offices, however, are located north of town, where lease rates are less expensive, and the principal sales area is south of town. This means the trucks must drive across town four times each day. The cost of transportation to and from the sales area plus the power demands of the trucks' coffee brewing equipment are a significant portion of variable costs. Pony Espresso could reduce the amount of driving and, therefore, the variable costs, if it moved the offices closer to the sales area. Pony Espresso presently has fixed costs of10,000 per month. The lease of a new office, closer to the sales area, would cost an additional $2,200 per month. This would increase the fixed costs to$12,200 per month. Although the lease of new offices would increase the fixed costs, a careful estimate of the potential savings in gasoline and vehicle maintenance indicates that Pony Espresso could reduce the variable costs from $0.60 per unit to$0.35 per unit. Total sales are unlikely to increase as a result of the move, but the savings in variable costs should increase the annual profit. Project Focus Consider the information provided to you from the owner in the data file AYK19_Data.xlsx. Especially look at the change in the variability of the profit from month to month. From November through January, when it is much more difficult to lure office workers out into the cold to purchase coffee, Pony Espresso barely breaks even. In fact, in December, the business lost money. 1. Develop the cost analysis on the existing lease information using the monthly sales figures provided to you in the data file. 2. Develop the cost analysis from the new lease information provided above. 3. Calculate the variability that is reflected in the month-to-month standard deviation of earnings for the current cost structure and the projected cost structure. 4. Do not consider any association with downsizing such as overhead-simply focus on the information provided to you. 5. You will need to calculate the EBIT (earnings before interest and taxes). Data File: AYK19_Data.xlsx