De Montfort University Module: Financial Decision Making (BAMG1103)
Coursework 1
Mega Ltd case
Mega Ltd is considering to finance their acquisition of $50,000 in new equipment in year 1. Mega director board decided to borrow $40,000 from a local bank. The bank has asked them to produce a 3-year cash flows broken down by year (Year 1, 2, and 3). Sales in the prior year were $80,000 and are expected to increase 10 percent each year so that sales of $88,000 are expected in the first year. Collections in the year of sale are expected to be 90 percent, with the remaining 10 percent collected in the next year. Purchases are based on an expected cost of sales of 40 percent and a required ending inventory of 25 percent of next year’s sales. Prior year expenses included advertising expense of $2,500, depreciation expense of $100, wages expense of $26,000, supplies expense of $450, and utilities expense of $360. All expenses except depreciation and interest expenses are paid in the year in which they are incurred and are expected to increase 8 percent each year. Interest expense is expected to remain constant at $600 each year for years 1–3. Payments in the year of purchase are expected to be 85 percent, with the remaining 15 percent paid in the next year. Proceeds from the $40,000 loan are expected in year 1, and $50,000 of equipment will be purchased during year 1. In sub-sequent years equipment purchases are expected to be $1,000 each year. Proceeds from projected equipment sales each year are expected to amount to $200. Annual payments of $13,000 on the loan also begin in year 1. The cash balance in the prior year was $6,400 and the beginning inventory in the prior year was 6,500.
Use Excel to create a cash flows statement based on the assumptions just provided. The Excel worksheet should include cash receipts, operating cash payment, cash from (to) operating activities, cash from (to) investing activities, and cash from (to) financing activities…. (Save your file as FDM_studentname_groupnumber and send the Excel file to [email protected]
a. Print the newly completed worksheet in “Value View” and “Formula View”, with your name and date printed in the upper left header and the student number in the middle header.
(60 marks)
b. What is end-of-year cash if the sales growth each year were 25 percent and what annual sales growth would be needed to produce an ending cash balance of $0 in year 3. Print the resulting worksheet in “Value View”, with your name and date printed in the upper left header and the student number in the middle header. (10 marks)
c. Write a not more than one page summary report based on the cash flows statement in part a together with appropriate charts/tables/graph if you are the financial accountant in Mega Ltd to advise the feasibility to recruit on loan. (20 marks)
d. Quality of works (10 marks)
USA 
India