Questions
Improving Decision Making: Making the Rent vs. Buy Decision for Data Storage for ABC Digital Software...

Improving Decision Making: Making the Rent vs. Buy Decision for Data Storage for ABC Digital

Software skills: Spreadsheet formulas, and charts
Business skills: Technology rent vs. buy decision, TCO analysis

This project provides an opportunity for you help a real-world company make a decision about whether to rent or buy new technology for data storage. You’ll use spreadsheetsoftware to compare the total 4-year cost of ownership for two options.

Deciding whether to store data on the cloud or on-site is often the dilemma for most business owners. There are advantages and disadvantages to both options. However, in this project you are only focusing on the cost benefits of each option regardless of other aspects including the access time, privacy and scalability issues which are also critically important in making this decision.

Scenario

ABC Digital is looking for a solution for storing their data. They have approximately 100 Terabytes of data that they need to store. The followings are typical cost information associated to option 1, that is to store data on the premises:

Description

Cost/ Resource

Copies of data needed for redundancy

3

Dollar per Gigabyte of Storage

$4

Typical Fulltime Staff for Data Admin

1 Full Time Admin for 200TB of data

Typical Cost of Full Time Data Engineer

$80,000 for the first year, increases by 10% each year

Facilities and Power Charges

$20,000 for the first year, increases by 8% each year

The cost items associated to cloud-based storage (Option 2) is given below.

Description

Cost/ Resource

Dollar per Gigabyte per year for cloud charge

$1.48

Dollar per Gigabyte of initial data migration into cloud

$0.1

Cost of Data Communication and Networks

$60,000 for the first year, increases by 7% each year

Note that with cloud solution, the backup and redundancies are provided by the cloud provider and the service is included in the cost.

Tasks

  1. Use your spreadsheet application to calculate the cost of each option for year 1 to year 4. To get the full mark, you should use Excel formula when it is appropriate. In other words, I should be able to change the above parameters (listed in the tables) and the total cost values should be changed automatically. (30 points)

  2. Use an appropriate chart type to present and compare the cost of both solutions in one graph for year 1 to year 4. (15 points)

  3. Determine which cost items are considered as capital expenditure (Capex) and which ones are operational expenditures (Opex) for each solution. (15 points)

  4. Calculate the total cost of ownerships for a 4-year period for each of the two solutions and suggest the best solution for the ABC Digital. (20 points)

  5. One of the main advantages of spreadsheets is that you can easily change the parameters of the model and evaluate “What if...” types of scenarios. Your manager wants to see the sensitivity of the total cost for solution 1 in relation to the year-to-year increase in the “Facilities and Power Charges”. More specifically, your current solution shows the total cost when considering 8% year-to-year increase of the cost of “Facilities and Power Charges” as shown in the Table. However, she wants to see how your estimates changes with this quantity. You should evaluate the total costs over 4 years when evaluating 4%,6%,8% (your current solution), 10%,12% and 14% increase in the year-to-year cost of “Facilities and Power Charges”. The first year cost is still the same, i.e. $20,000 for all scenarios. You should create a new sheet for this. This sheet should show the total cost of solution 1 when changing the year-to-year changes in the Facilities and Power Charges as suggested above. (20 points)

In: Accounting

The comparative balance sheets for Larkspur Corporation show the following information. December 31 2017 2016 Cash...

The comparative balance sheets for Larkspur Corporation show the following information.

December 31

2017

2016

Cash

$33,700

$13,200

Accounts receivable

12,100

9,900

Inventory

12,000

9,100

Available-for-sale debt investments

–0–

3,000

Buildings

–0–

29,500

Equipment

45,000

19,800

Patents

5,000

6,100

$107,800

$90,600

Allowance for doubtful accounts

$3,100

$4,500

Accumulated depreciation—equipment

2,000

4,500

Accumulated depreciation—building

–0–

6,100

Accounts payable

5,000

3,000

Dividends payable

–0–

4,900

Notes payable, short-term (nontrade)

2,900

4,000

Long-term notes payable

31,000

25,000

Common stock

43,000

33,000

Retained earnings

20,800

5,600

$107,800

$90,600


Additional data related to 2017 are as follows.

1. Equipment that had cost $11,100 and was 40% depreciated at time of disposal was sold for $2,500.
2. $10,000 of the long-term note payable was paid by issuing common stock.
3. Cash dividends paid were $4,900.
4. On January 1, 2017, the building was completely destroyed by a flood. Insurance proceeds on the building were $30,200 (net of $2,100 taxes).
5. Investments (available-for-sale) were sold at $1,800 above their cost. The company has made similar sales and investments in the past.
6. Cash was paid for the acquisition of equipment.
7. A long-term note for $16,000 was issued for the acquisition of equipment.
8. Interest of $2,000 and income taxes of $6,400 were paid in cash.


Prepare a statement of cash flows using the indirect method. Flood damage is unusual and infrequent in that part of the country.

In: Accounting

Luzadis Company makes furniture using the latest automated technology. The company uses a job-order costing system...

Luzadis Company makes furniture using the latest automated technology. The company uses a job-order costing system and applies manufacturing overhead cost to products on the basis of machine-hours. The predetermined overhead rate was based on a cost formula that estimates $612,000 of total manufacturing overhead for an estimated activity level of 68,000 machine-hours.

During the year, a large quantity of furniture on the market resulted in cutting back production and a buildup of furniture in the company’s warehouse. The company’s cost records revealed the following actual cost and operating data for the year:

Machine-hours 50,000
Manufacturing overhead cost $ 570,000
Inventories at year-end:
Raw materials $ 18,000
Work in process (includes overhead applied of $22,500) $ 93,500
Finished goods (includes overhead applied of $76,500) $ 317,900
Cost of goods sold (includes overhead applied of $351,000) $ 1,458,600

Required:

1. Compute the underapplied or overapplied overhead.

2. Assume that the company closes any underapplied or overapplied overhead to Cost of Goods Sold. Prepare the appropriate journal entry.

3. Assume that the company allocates any underapplied or overapplied overhead proportionally to Work in Process, Finished Goods, and Cost of Goods Sold. Prepare the appropriate journal entry.

4. How much higher or lower will net operating income be if the underapplied or overapplied overhead is allocated to Work in Process, Finished Goods, and Cost of Goods Sold rather than being closed to Cost of Goods Sold?

In: Accounting

The following selected events and transactions were recorded by Milos County Hospital. Gross charges for hospital...

The following selected events and transactions were recorded by Milos County Hospital.

  1. Gross charges for hospital services, all charged to accounts and notes receivable, were as follows:
Patient service revenue $1,664,900
  1. The hospital cafeteria and gift shop had cash sales of $295,300.
  2. Additional information determined subsequently to recording patient service revenues and relating to the current year is as follows:
Contractual adjustments $ 632,000
Provision for bad debts 30,200
Charity care 261,400
  1. A federal cost reimbursement research grant of $350,000 was awarded. As of the end of the year, $200,000 in expenses related to the grant had been made.
  2. Vouchers totaling $1,326,540 were issued for the following items:
Fiscal and administrative services expenses $ 194,440
General services expenses 253,100
Nursing services expenses 585,000
Other professional services expenses 185,600
Inventory 101,200
Expenses accrued at December 31 7,200
  1. Collections of accounts receivable totaled $1,159,000. Accounts written off as uncollectible amounted to $11,900.
  2. Cash payments on vouchers payable (paid to employers and suppliers) during the year were $1,031,200.
  3. Supplies of $99,770 were issued to nursing services.
  4. On December 31, accrued interest income on investments was $800.
  5. Depreciation of buildings and equipment was as follows:
Buildings $ 51,000
Equipment 73,000

11. On December 31, closing entries were made in the general journal.

Required

  1. Prepare closing entries in accordance with the standards for a governmental health care entity that follows proprietary fund accounting. (If no entry is required for a transaction/event, select "No Journal Entry Required" in the first account field.)
  2. Calculate the net patient service revenue that would be reported on the statement of revenues, expenses, and changes in net position.

In: Accounting

Milano Pizza is a small neighborhood pizzeria that has a small area for in-store dining as...

Milano Pizza is a small neighborhood pizzeria that has a small area for in-store dining as well as offering take-out and free home delivery services. The pizzeria’s owner has determined that the shop has two major cost drivers—the number of pizzas sold and the number of deliveries made.

The pizzeria’s cost formulas appear below:

Fixed Cost
per Month
Cost per
Pizza
Cost per
Delivery
Pizza ingredients $ 4.30
Kitchen staff $ 6,110
Utilities $ 710 $ 0.30
Delivery person $ 3.10
Delivery vehicle $ 730 $ 1.30
Equipment depreciation $ 480
Rent $ 2,070
Miscellaneous $ 830 $ 0.15

  

In November, the pizzeria budgeted for 1,860 pizzas at an average selling price of $17 per pizza and for 240 deliveries.

Data concerning the pizzeria’s actual results in November appear below:

  

Actual Results
Pizzas 1,960
Deliveries 220
Revenue $ 33,970
Pizza ingredients $ 9,010
Kitchen staff $ 6,050
Utilities $ 935
Delivery person $ 682
Delivery vehicle $ 1,006
Equipment depreciation $ 480
Rent $ 2,070
Miscellaneous $ 850

Required:

1. Complete the flexible budget performance report that shows both revenue and spending variances and activity variances for the pizzeria for November. (Indicate the effect of each variance by selecting "F" for favorable, "U" for unfavorable, and "None" for no effect (i.e., zero variance). Input all amounts as positive values.)

In: Accounting

Consider an investment that costs $100,000 and has a cash inflow of $25,000 every year for...

Consider an investment that costs $100,000 and has a cash inflow of $25,000 every year for 5 years. The required return is 9% and required payback is 4 years.
• What is the payback period?
• What is the NPV?
• What is the IRR?
• Should we accept the project?
What decision rule should be the primary decision method?
When is the IRR rule unreliable?

In: Accounting

You're a recent graduate of Champlain College and landed a great job in New York as...

You're a recent graduate of Champlain College and landed a great job in New York as a junior financial analyst for Chuck Schwable, a leading investment firm whose name bears no resemblance to a well known investment firm. Schwable has recently been hired by Giggle, a leading IT corporation, to provide some capital budgeting recommendations for a major building project in downtown Manhattan. This is it...it's your big chance to make a great first impression and show off the skills you gained in your MGMT 240 finance class.

The senior financial analyst in your office asks you to provide a general synopsis of three common capital budgeting techniques: NPV, IRR, Profitability Index, and the Payback Period. Your synopsis will be presented to a small group of very important Giggle stakeholders who know nothing about capital budgeting.

For your synopsis (use fictitious data, if applicable):

  1. Describe each technique and how it pertains to making capital budgeting decisions
  2. Explain the benefits each technique has

In: Accounting

After reading the Graham, Campbell, and Rajgopal article, you might get the implication for the motivation...

After reading the Graham, Campbell, and Rajgopal article, you might get the implication for the motivation of voluntary disclosure. Why does the article suggest that managers’ earnings guidance can destroy the firm’ value?

Because the preparation of the guidance is costly

The earnings guidance can increase agency cost by discouraging managers from focusing on long-term value increase

The guidance is generally not accurate, and make confusions in the market

None of the above

In: Accounting

Baur Company has 21,000 shares of $10 par value, 8% preferred stock and 500,000 shares of...

Baur Company has 21,000 shares of $10 par value, 8% preferred stock and 500,000 shares of $1 par value common stock outstanding. As of December 31, 2018, it had $900,000 of Retained earnings. On December 31, 2018, the Board of Directors is considering the distribution of a cash dividend to the common and preferred stockholders. No dividends were declared in 2016 and 2017 and no dividends were in arrears prior to 2016. The company is considering the following options.

Option 1 The preferred stock is noncumulative and the dividend for 2018 would be $30,000.

Option 2 The preferred stock is cumulative and the dividends would be $30,000.

Option 3 The preferred stock is cumulative and the dividends would be $75,000.

Required: For each scenario, determine the dollar amount of dividends that would be paid to each class of stockholder.

Define the following as they pertain to dividend distribution: a. Date of declaration b. Date of record c. Date of payment

On which of these dates (date of declaration, date of record or date of payment) would a company NOT make a journal entry?

In: Accounting

Lance has two adult children from a previous marriage. He has gifted them money for the...

Lance has two adult children from a previous marriage. He has gifted them money for the past three years from his separate bank account and his wife has consented to split the gifts each year. Lance made gifts to his children as follows:

2016: Gifts of $60,000 to each child

2017: Gifts of $40,000 to each child

2018: Gifts of $50,000 to each child

  1. What is the amount of Lance’s total taxable gifts?

In: Accounting

Exercise 2-9 (Part Level Submission) Crane, Inc., has collected the following information on its cost of...

Exercise 2-9 (Part Level Submission) Crane, Inc., has collected the following information on its cost of electricity: Machine Hours Total Electricity Costs January 500 $230 February 540 $280 March 340 $190 April 430 $200 May 640 $260 June 690 $330 July 300 $160 August 500 $250 September 220 $100 October 730 $320 November 820 $340 December 600 $300 Collapse question part (a) Correct answer. Your answer is correct. Using the high-low method, compute the variable cost of electricity per machine hour. (Round unit cost to 2 decimal places, e.g. 52.75.) Variable cost $Entry field with correct answer 0.4 per machine hour Click if you would like to Show Work for this question: Open Show Work SHOW SOLUTION LINK TO TEXT LINK TO VIDEO Attempts: 1 of 2 used Collapse question part (b) Incorrect answer. Your answer is incorrect. Try again. Compute the total fixed cost of electricity. (Round answer to 2 decimal places, e.g. 52.75.) Fixed cost $Entry field with incorrect answer 0.40 Click if you would like to Show Work for this question: Open Show Work SHOW SOLUTION LINK TO TEXT LINK TO VIDEO Attempts: 2 of 2 used Collapse question part (c) Correct answer. Your answer is correct. Represent the electricity cost function in equation form. (Round answers to 2 decimal places, e.g. 52.75.) Total cost = $Entry field with correct answer 0.4 × MH + $Entry field with correct answer 12 Click if you would like to Show Work for this question: Open Show Work SHOW SOLUTION LINK TO TEXT LINK TO VIDEO Attempts: 2 of 2 used Collapse question part (d) What is the expected electricity cost when 730 machine hours are used? (Round answer to 2 decimal places, e.g. 52.75.) Total cost $

In: Accounting

The following are Wildhorse Corp.’s comparative balance sheet accounts at December 31, 2017 and 2016, with...

The following are Wildhorse Corp.’s comparative balance sheet accounts at December 31, 2017 and 2016, with a column showing the increase (decrease) from 2016 to 2017.

COMPARATIVE BALANCE SHEETS

2017

2016

Increase
(Decrease)

Cash

$811,100

$702,700

$108,400

Accounts receivable

1,139,100

1,176,000

(36,900

)

Inventory

1,847,000

1,704,500

142,500

Property, plant, and equipment

3,317,700

2,945,400

372,300

Accumulated depreciation

(1,158,000

)

(1,048,400

)

(109,600

)

Investment in Myers Co.

312,200

274,000

38,200

Loan receivable

250,000

250,000

   Total assets

$6,519,100

$5,754,200

$764,900

Accounts payable

$1,010,900

$960,700

$50,200

Income taxes payable

29,900

50,500

(20,600

)

Dividends payable

80,600

100,700

(20,100

)

Lease liabililty

432,100

432,100

Common stock, $1 par

500,000

500,000

Paid-in capital in excess of par—common stock

1,499,300

1,499,300

Retained earnings

2,966,300

2,643,000

323,300

   Total liabilities and stockholders’ equity

$6,519,100

$5,754,200

$764,900


Additional information:

1. On December 31, 2016, Wildhorse acquired 25% of Myers Co.’s common stock for $274,000. On that date, the carrying value of Myers’s assets and liabilities, which approximated their fair values, was $1,096,000. Myers reported income of $152,800 for the year ended December 31, 2017. No dividend was paid on Myers’s common stock during the year.
2. During 2017, Wildhorse loaned $332,200 to TLC Co., an unrelated company. TLC made the first semiannual principal repayment of $82,200, plus interest at 10%, on December 31, 2017.
3. On January 2, 2017, Wildhorse sold equipment costing $59,800, with a carrying amount of $38,000, for $40,100 cash.
4. On December 31, 2017, Wildhorse entered into a capital lease for an office building. The present value of the annual rental payments is $432,100, which equals the fair value of the building. Wildhorse made the first rental payment of $60,300 when due on January 2, 2018.
5. Net income for 2017 was $403,900.
6. Wildhorse declared and paid the following cash dividends for 2017 and 2016.

2017

2016

Declared December 15, 2017 December 15, 2016
Paid February 28, 2018 February 28, 2017
Amount $80,600 $100,700


Prepare a statement of cash flows for Wildhorse Corp. for the year ended December 31, 2017, using the indirect method. (Show amounts that decrease cash flow with either a - sign e.g. -15,000 or in parenthesis e.g. (15,000).)

In: Accounting

CASE STUDY USING EXCEL SPREADSHEET You work for Theo Walcott Tours Ltd which provide tourists and...

CASE STUDY USING EXCEL SPREADSHEET You work for Theo Walcott Tours Ltd which provide tourists and visitors with ‘experiences’ of Perth and its surrounds. Your manager is currently investigating introducing another product, which are ‘Luxury’ helicopter rides over beautiful bushland. Each trip would be 50km in total. Your manager wants you to use cost-volume-profit analysis in order to help assess the plan’s feasibility. She provides you with the following estimated data: Selling price per trip: $600 (total for 3 customers – trips only run with 3 customers) Costs: Fuel: $50 per trip Walcott ‘goodie bag’ per customer: $40 Helicopter rental per month: $20,000 Insurance per month (unlimited trips): $1,000 Pilot costs: $5,000 per month plus $100 per trip Maintenance costs are difficult to estimate but data from a similar company in a different location shows that these monthly costs were $11,000 when 5,000 kms were flown and $5000 when 1,500 kms were flown. ACCM 4100 Management Accounting 1 Trimester 2, 2020: Individual Excel Assignment REQUIRED: Calculate the following: 1) The Break-even point in trips per month 2) The Break-even point in dollars of revenue per month 3) Assuming a profit after tax requirement from the Helicopter trip business of $120,000 per year and a tax rate of 30%, calculate: a) Trips required per month to obtain target profit b) Revenue required per month to obtain target profit Your manager has requested that the spreadsheet is easy to use for ‘What-if’ analysis – so she would like to be able to change some of the inputs to see the impact on the calculations above – for example, if the Helicopter were able to be rented more cheaply or the selling price was increased.

In: Accounting

Prepare Journal Entries, Ledger, T- Accounts, Trial Balance, Income Statement, and Balance Sheet The following are...

Prepare Journal Entries, Ledger, T- Accounts, Trial Balance, Income Statement, and Balance Sheet

The following are transactions of Samantha Payapag Advertising Company for the month of July 2013

July 3 Samantha Payapag invested 500,000 in the business.

July 5 Bought for cash, advertising supplies costing 80,000. Paid rental of the office, 7,300

July 9 Bought delivery truck from MJ Idos Trading, 350,000 on credit

July 12 Received 43,000 cash as advertising income

July 13 Bought furniture & fixtures, 32,000 in cash

July 17 Took 3,200 cash for personal purposes

July 18 Billed Bernalyn Galvez for the advertising service rendered to promote her product to the market, 10,000

July 23 Paid salaries of the employees, 15,000. Billed Zaldy Co. for the advertising service rendered, 4,000

July 24 Collected 1/2 of the amount Bernalyn Galvez owed to the company

July 26 Purchased another truck amounting to 120,000 from Edwina Motor, Inc. on credit

July 27 Paid MJ Idos Trading 230,000 as partial settlement of the account

July 30 Paid utility expense for the month

In: Accounting

For the just completed year, Hanna Company had net income of $134,000. Balances in the company’s...

For the just completed year, Hanna Company had net income of $134,000. Balances in the company’s current asset and current liability accounts at the beginning and end of the year were as follows:

December 31
End of
Year
Beginning of Year
Current assets:
Cash and cash equivalents $ 61,000 $ 83,000
Accounts receivable $ 166,000 $ 196,000
Inventory $ 435,000 $ 367,000
Prepaid expenses $ 11,500 $ 13,800
Current liabilities:
Accounts payable $ 356,000 $ 392,000
Accrued liabilities $ 8,000 $ 12,700
Income taxes payable $ 38,000 $ 33,000

The Accumulated Depreciation account had total credits of $58,000 during the year. Hanna Company did not record any gains or losses during the year.

The company’s income statement for the year appears below:

Sales $ 1,080,000
Cost of goods sold 600,000
Gross margin 480,000
Selling and administrative expenses 310,000
Income before taxes 170,000
Income taxes 36,000
Net income $ 134,000

Required:

Using the direct method, convert the company's income statement to a cash basis. (Adjustment amounts that are to be deducted should be indicated with a minus sign.)

In: Accounting