I need help with the following case study of Advance Financial Managemdnt Accounting. I have added pictures of the case along with the student instruction.

Please help me with step by step process if anyone can!

Rocky Mountain Vacations Rocky Mountain Vacations (RMV) Ltd. is a retailer of recreational vehicles (RVS) located in Kamloops, British Columba. RMV was founded in 2011 by Jane Curtis with the plan of selling quality, used RVs to middle income families at reasonable prices. The company bought products at various auctions across B.C. and Alberta or directly from owners. It then conducted a thorough overhaul of an RV's mechanics, upgraded the interior and exterior, and did a major cleaning before putting the unit on sale. The refurbished products were warrantied by RMV at no additional cost to the owner for one year and all maintenance work after that was completed at very reasonable rates. Customers were very happy with the RVs and repair services offered by RMV. Quality was considered excellent and RMV had a reputation of "standing behind" its work with no questions asked." Curtis was pleased with the company's growth over this time but felt profitability could have been higher. In 2015, Curtis changed her business plan and decided to focus solely on selling new, high-margin RVs to wealthier clients. She arranged to become the exclusive dealer for North Star RV, an up-scale manufacturer, in the Kamloops market. This was not only an attempt to improve profitability, but Curtis had always dreamt of becoming a luxury RV distributor. To comply with the dealership agreement, RMV was required to complete a major renovation and expansion of its sales facility near the Kamloops Airport. The refurbished building was quite large and luxurious, and the display lot was expanded and paved. The sales facility included a new, 4-bay service area that provided warranty work and regular maintenance for North Star products. Only two bays are currently needed, but Curtis hopes to expand service work as the business grows. RMV also has a 50-unit, RV storage facility in an adjacent lot. At the end of 2016, RMV's balance sheet was as follows: Rocky Mountain Vacations Ltd. Balance Sheet December 31, 2016 Current CAD 500.000 Temporary 15.00 Accounts receive 2.300.000 1,76 40 Totalcuments CAD 4,733.400 Finantst 24 Totalt CAD 8,577.700 Currealities Accounts payable CAD 1.435.00 Line of credit Current portion of long-term del 461,300 Totalcume liais CAD 1. Long-term White Termos 2.10 Shareholders equity Share capital 1.301 Retained camins 3.281.400 Total liabilities and CAD 672 Sales The RV industry is very seasonal with most sales occurring in the second and third quarters. The second quarter is the busiest season as families get ready for summer holidays, but the third quarter remains strong as "snowbirds migrate" to their winter "nests in California, Arizona, and Florida. Curtis has prepared the following sales estimates for 2017: Quarter | Quarter 2 Quarter Quarter (January March April June July-September October December 11 9 Models Freedom Indende A BOBY $ 16 11 81 65 90 Service hours 19 590 50 530 to North Star's three RV models cater to different price segments. Retail prices for 2017 are: CAD 165.36 Independence CAD 1 Autonomy CADAS. Unit sales are expected to increase by 5.0% in 2018 but RMV has not raised its prices since adopting its new strategy in 2015. The dealer relations representative from North Star estimates that RMV could raise its prices by 2.0% with no change to the units sold. RMV's shop rate is CAD 70 per hour, which is low compared to the industry standard of CAD 90. All parts are resold at cost. Service work is paid for immediately by the customer. Twenty percent of all sales are for cash, but the remainder require financing, RMV offers customers three- and five-year financing plans, but these sales agreements are immediately resold to North Star's financing unit that repackages them and sells them as asset- backed securities. This process takes approximately 60 days, and RMV is paid in full at that time. The dealers have been lobbying North Star to reduce this payment period to 30 days through faster processing. RMV's fifty covered storage lots are rented at CAD 250 per unit per quarter, which RMW promotes as the best rate in town." In the Kamloops market, rates for a similar unit averages CAD 500 per unit and the customer must commit to a one-year lease. Payment is received electronically from customers on the last day of each quarter. Cost of Sales The 2017 wholesale prices for North Star RVs are: CAD 144,100 Independence AD 12 CAD 7.30 The company's two mechanics are paid on piece rate and receive CAD 50 per service hour. Both mechanics are fully employed in quarters 2 and 3but are underutilized the rest of the year. RMV has been exploring ways to improve efficiency in the service area. As engines in all three RV models are standard General Motors designs, parts can be sourced locally on a just-in-time basis, therefore, negligible parts inventories are required. Supply inventories are also minimal. For planning purposes, RMV finds that parts and supplies average 100.0% and 10.0% of service revenue. RMV purchases all of its RVs on terms net 30 from North Star. RMV maintains inventory levels at 40.0% of next quarter's sales for each model of RV. The dealer relations representative from North Star says other dealers maintain inventory of only 25.0% of next quarter's sales on average. The representative also indicates that North Star's credit terms are sometimes extended to net 60 to aid struggling dealers or help finance expansions. Beginning inventory consists of two Freedom models, seven Independence models, and eight Autonomy models. Wholesale prices were the same as in 2016. Other Expenses Category Details Selling Five sales staff at CAD 32,000 per employee per year plus a commission equal to 6.0% of RV sales Administration and storage CAD 950,000 per year Depreciation CAD 285,000 per year Other than inventory purchases and depreciation, all expenses are paid as incurred. RMV has been considering paying its sales staff on a straight commission basis to improve results. The number of salespeople will likely decline as poorer performers see their incomes drop, but the remaining salespeople should be able to maintain their earnings. Capital Budget RMV has prepared a list of capital acquisitions that it plans to make in 2017: Item Estimated Acquisition Date Cost Service bay equipment - 10-year CAD 850,000 Quarter 1 life Sales facility furniture - 3-year life CAD 75,000 Quarter 1 The equipment is needed to open the remaining two service bays and additional furniture is required for the sales offices. The equipment purchases can be delayed if necessary. All assets are depreciated on a straight-line basis and the residual value is assumed to be negligible. Financing RMV has negotiated a CAD 5,500,000, 5.50% line of credit wi Royal Bank. The loan is secured by the company's current ass can borrow no more than 50.0% of the value of its inventory a 70.0% of its accounts receivable. Interest is paid at the end of quarter and all repayments or draw downs are made at that tim Purchases of fixed assets can be financed with a term or mortg= with the Royal Bank. The bank is willing to lend 60.0% of the of equipment and 75.0% of the value of real estate. Loans are down monthly on a straight-line basis over the life of the asset generally cannot be paid early. Asset acquisitions and interest principal payments are made at the end of the quarter only. Th loan currently has a fixed interest rate of 7.50% per annum. Ti Royal Bank allows the company to make an additional principa payment equal to 20.0% of the balance of any term and mortga at the end of the fourth quarter each year. The Royal Bank requires that RMV maintain a current ratio of each quarter and an annual times interest earned ratio of 4.50 ( only calculated in the fourth quarter based the yearly totals) as submit audited quarterly and annual financial statements for re Also, the line of credit must be paid down to zero once per yea ensure the company only uses its line of credit to finance its se build up in net working capital. RMV attempts to maintain a long-term debt to total-capitalizat ratio of 25.00% although it is not a condition of any of its bank Company policy is to maintain a cash balance of CAD 600,000 end of each quarter. Surplus cash balances can be invested in a month term deposit at 1.50% per annum. Funds are invested o withdrawn at the end of the quarter only and income is paid ou time. Based on past experience, the company was studying wh cash balance of CAD 500,000 could be sufficient. Beginning inventory consists of two Freedom models, seven Independence models, and eight Autonomy models. Wholesale prices were the same as in 2016. Other Expenses Category Details Selling Five sales staff at CAD 32,000 per employee per year plus a commission equal to 6.0% of RV sales Administration and storage CAD 950,000 per year Depreciation CAD 285,000 per year Other than inventory purchases and depreciation, all expenses are paid as incurred. RMV has been considering paying its sales staff on a straight commission basis to improve results. The number of salespeople will likely decline as poorer performers see their incomes drop, but the remaining salespeople should be able to maintain their earnings. Capital Budget RMV has prepared a list of capital acquisitions that it plans to make in 2017: Item Estimated Acquisition Date Cost Service bay equipment - 10-year CAD 850,000 Quarter 1 life Sales facility furniture 3-year life CAD 75,000 Quarter The equipment is needed to open the remaining two service bays and additional furniture is required for the sales offices. The equipment purchases can be delayed if necessary. All assets are depreciated on a straight-line basis and the residual value is assumed to be negligible. Income Taxes The corporate tax rate is 15.0%. Corporate income tax installments of CAD 25,000 are paid at the end of each quarter. Distributions to Owner Current loan agreements limit Curtis' dividends to CAD 400,000 per year. RMV currently pays this amount to Curtis in four equal monthly installments, but she indicated it could be reduced to CAD 200,000 per year if necessary. Potential Expansion North Star approached Curtis about opening another dealership in Kelowna in 2019. She thinks this is a tremendous opportunity given that city's high average income and its emergence as a major Canadian retirement destination. Based on her experience in Kamloops, Curtis feels a net investment of CAD 6,500,000 would be needed and that 60.0% could be financed through the Royal Bank. Curtis was very anxious to pursue the Kelowna expansion, so she immediately met with her accountant. Riley Johnson, CPA, stressed that the economy in B.C. was currently doing well but that continuing low oil prices and rising interest rates may lead to a decline in consumer confidence next year that could significantly affect RV sales. He also indicated that RMV did not meet all its loan conditions last year, its profitability ratios were significantly below industry averages and it was over leveraged. Recent data from Statistics Canada indicated an industry average net profit margin of 2.10%, total assets turnover of 3.60, rate of return on assets of 7.56%, rate of return on equity of 15.12%, long-term debt to total capitalization ratio of 25.00 %, and times interest cared ratio of 4.50 - all ratios are calculated using year end information. Despite Johnson's cautious tone, Curtis still wants to somehow purse the expansion. She knows that other new North Star dealers can be found quickly if she hesitates, so being able to open a new location by 2019 is imperative. How can she address Johnson's concerns about profitability and over borrowing? Will she be able to find the equity for the proposed expansion or should she pursue a more prudent growth strategy given the current economic outlook? Page 2 Rocky Mountain Vacations Student Instructions Follow these steps in analyzing the case: 1. Assuming the status quo (no change to existing practices), prepare pro forma income statements, cash budgets, balance sheets, and ratio tables for quarter 1, 2, 3 and 4 of 2017. Note: The status quo includes the decision to reduce the dividends to CAD 200,000 and to delay the purchase of new service bay equipment to beyond 2017 due to the low utilization of existing facilities. 2. For each quarter, discuss the changes in cash flows, the financial decisions made, and whether the company is meeting its financial goals and lending conditions in each quarter based on Step 1. 3. What possible actions can RMV take to achieve its financial goals (higher profitability, reduced borrowing, and capital accumulation for the new dealership) and lending conditions? Are they reasonable? 4. Implement all reasonable actions identified in Step 3, and prepare revised pro forma income statements, cash budgets, balance sheets and ratio tables for quarter 1, 2, 3 and 4 of 2017. 5. Is RMV able to meet its financial goals and lending conditions after making the changes to the financial plan? How should the company proceed? Submission Requirements 1. Prepare pro forma income statements, cash budgets, and balance sheets for quarter 1, 2, 3 and 4 of 2017 as required in discussion question 1. This should be done in a separate Excel workbook from Part 2. 2. Prepare revised pro forma income statements, cash budgets, 2. Prepare revised pro forma income statements, cash budgets, and balance sheets for quarter 1, 2, 3 and 4 of 2017 as required in discussion question 4. This should be done in a separate Excel workbook from Part 1. 3. Prepare a 2-page memorandum addressing discussion questions 2, 3, and 5. Pro forma Template The pro forma financial statements should be prepared using the templates: Budgeted Income Statement Quarter 1 Quarter 2 Quarter 3 Quarter 4 Year Sales RV Freedom Independence Autonomy Total Service Parts Storage Total sales Expenses RV Freedom Independence Autonomy Total Service Parts Supplies Selling Administration and storage Depreciation Total expenses Operating profil Interest income est expense Income before tas Income tax Net Income Cash Budget Quarter 1 Quarter 2 Quarter 3 Quarter 4 Year Cash balance, beginning Cash receipts RV Last quarter This quarter Service Parts Storage Interest income Total cash receipts Cash disbursements Purchases Last quarter This quarter Service technicians Parts Supplies Selling Administration and storage Interest expense Income tax Regular dividends Capital purchase Total cash disbursements Sub-total Financing Repayment Linc-of-credit Term loan - Regular loan payment Term loan - Extra loan payment Borrowing Linc-of-credit Term loan Special dividends Issuance and repurchase of shares Total financing Temporary investment Cash balance, ending Budgeted Balance Sheet Quarter 1 Quarter 2 Quarter 3 Quarter Current assets Cash Temporary investments Accounts receivable Inventory Income taxes receivable Total current assets Fixed assets, net Total assets Current liabilities Accounts payable Line of credit Income taxes payable Current portion of long-term debe Total current liabilities Term loan Shareholders' equity Common shares Retained earnings Total liabilities and equities Key Financial Ratios Quarter 1 Quarter 2 Quarter 3 Quarter Current ratio (1.50) XX XX XX XX Net profit margin XX Total asset turnover XX Return on assets XX Return on equity XX Line of credit/(ARInventory) ( 4.50) XX Long-term debt / Total capitalization (-25.0% XX XXI XX XX LOC financing (maximum CAD 5.500,000) XX XX XX xx Note: Net profit margin, total asset turnover ratio, return on assets, return on equity and times interest earned should be calculated at the end of the year only using yearly data. Memorandum Template The following template should be used in preparing the memorandum: Memorandum To: From: Subject: Date Memorandum To: From: Subject: Date: (Short introduction) Status Quo (Address the questions asked in Stop 2 of the Case Analysis section) Quarter 1 Quarter 2 Quarter 3 Quarter 4 Possible Actions (Address the questions asked in Step 3 of the Case Analysis section using the table below.) Possible Action Reasonableness Recommendations (Address the questions asked in Step 5 of the Case Analysis section.) Grading Rubric You will be graded using the following criteria. Quality of Spreadsheets: 20% Physical appearance Laput page Spreadsheet automation 10 Thoroughness of Analysis: 60% Inicio Rocky Mountain Vacations Rocky Mountain Vacations (RMV) Ltd. is a retailer of recreational vehicles (RVS) located in Kamloops, British Columba. RMV was founded in 2011 by Jane Curtis with the plan of selling quality, used RVs to middle income families at reasonable prices. The company bought products at various auctions across B.C. and Alberta or directly from owners. It then conducted a thorough overhaul of an RV's mechanics, upgraded the interior and exterior, and did a major cleaning before putting the unit on sale. The refurbished products were warrantied by RMV at no additional cost to the owner for one year and all maintenance work after that was completed at very reasonable rates. Customers were very happy with the RVs and repair services offered by RMV. Quality was considered excellent and RMV had a reputation of "standing behind" its work with no questions asked." Curtis was pleased with the company's growth over this time but felt profitability could have been higher. In 2015, Curtis changed her business plan and decided to focus solely on selling new, high-margin RVs to wealthier clients. She arranged to become the exclusive dealer for North Star RV, an up-scale manufacturer, in the Kamloops market. This was not only an attempt to improve profitability, but Curtis had always dreamt of becoming a luxury RV distributor. To comply with the dealership agreement, RMV was required to complete a major renovation and expansion of its sales facility near the Kamloops Airport. The refurbished building was quite large and luxurious, and the display lot was expanded and paved. The sales facility included a new, 4-bay service area that provided warranty work and regular maintenance for North Star products. Only two bays are currently needed, but Curtis hopes to expand service work as the business grows. RMV also has a 50-unit, RV storage facility in an adjacent lot. At the end of 2016, RMV's balance sheet was as follows: Rocky Mountain Vacations Ltd. Balance Sheet December 31, 2016 Current CAD 500.000 Temporary 15.00 Accounts receive 2.300.000 1,76 40 Totalcuments CAD 4,733.400 Finantst 24 Totalt CAD 8,577.700 Currealities Accounts payable CAD 1.435.00 Line of credit Current portion of long-term del 461,300 Totalcume liais CAD 1. Long-term White Termos 2.10 Shareholders equity Share capital 1.301 Retained camins 3.281.400 Total liabilities and CAD 672 Sales The RV industry is very seasonal with most sales occurring in the second and third quarters. The second quarter is the busiest season as families get ready for summer holidays, but the third quarter remains strong as "snowbirds migrate" to their winter "nests in California, Arizona, and Florida. Curtis has prepared the following sales estimates for 2017: Quarter | Quarter 2 Quarter Quarter (January March April June July-September October December 11 9 Models Freedom Indende A BOBY $ 16 11 81 65 90 Service hours 19 590 50 530 to North Star's three RV models cater to different price segments. Retail prices for 2017 are: CAD 165.36 Independence CAD 1 Autonomy CADAS. Unit sales are expected to increase by 5.0% in 2018 but RMV has not raised its prices since adopting its new strategy in 2015. The dealer relations representative from North Star estimates that RMV could raise its prices by 2.0% with no change to the units sold. RMV's shop rate is CAD 70 per hour, which is low compared to the industry standard of CAD 90. All parts are resold at cost. Service work is paid for immediately by the customer. Twenty percent of all sales are for cash, but the remainder require financing, RMV offers customers three- and five-year financing plans, but these sales agreements are immediately resold to North Star's financing unit that repackages them and sells them as asset- backed securities. This process takes approximately 60 days, and RMV is paid in full at that time. The dealers have been lobbying North Star to reduce this payment period to 30 days through faster processing. RMV's fifty covered storage lots are rented at CAD 250 per unit per quarter, which RMW promotes as the best rate in town." In the Kamloops market, rates for a similar unit averages CAD 500 per unit and the customer must commit to a one-year lease. Payment is received electronically from customers on the last day of each quarter. Cost of Sales The 2017 wholesale prices for North Star RVs are: CAD 144,100 Independence AD 12 CAD 7.30 The company's two mechanics are paid on piece rate and receive CAD 50 per service hour. Both mechanics are fully employed in quarters 2 and 3but are underutilized the rest of the year. RMV has been exploring ways to improve efficiency in the service area. As engines in all three RV models are standard General Motors designs, parts can be sourced locally on a just-in-time basis, therefore, negligible parts inventories are required. Supply inventories are also minimal. For planning purposes, RMV finds that parts and supplies average 100.0% and 10.0% of service revenue. RMV purchases all of its RVs on terms net 30 from North Star. RMV maintains inventory levels at 40.0% of next quarter's sales for each model of RV. The dealer relations representative from North Star says other dealers maintain inventory of only 25.0% of next quarter's sales on average. The representative also indicates that North Star's credit terms are sometimes extended to net 60 to aid struggling dealers or help finance expansions. Beginning inventory consists of two Freedom models, seven Independence models, and eight Autonomy models. Wholesale prices were the same as in 2016. Other Expenses Category Details Selling Five sales staff at CAD 32,000 per employee per year plus a commission equal to 6.0% of RV sales Administration and storage CAD 950,000 per year Depreciation CAD 285,000 per year Other than inventory purchases and depreciation, all expenses are paid as incurred. RMV has been considering paying its sales staff on a straight commission basis to improve results. The number of salespeople will likely decline as poorer performers see their incomes drop, but the remaining salespeople should be able to maintain their earnings. Capital Budget RMV has prepared a list of capital acquisitions that it plans to make in 2017: Item Estimated Acquisition Date Cost Service bay equipment - 10-year CAD 850,000 Quarter 1 life Sales facility furniture - 3-year life CAD 75,000 Quarter 1 The equipment is needed to open the remaining two service bays and additional furniture is required for the sales offices. The equipment purchases can be delayed if necessary. All assets are depreciated on a straight-line basis and the residual value is assumed to be negligible. Financing RMV has negotiated a CAD 5,500,000, 5.50% line of credit wi Royal Bank. The loan is secured by the company's current ass can borrow no more than 50.0% of the value of its inventory a 70.0% of its accounts receivable. Interest is paid at the end of quarter and all repayments or draw downs are made at that tim Purchases of fixed assets can be financed with a term or mortg= with the Royal Bank. The bank is willing to lend 60.0% of the of equipment and 75.0% of the value of real estate. Loans are down monthly on a straight-line basis over the life of the asset generally cannot be paid early. Asset acquisitions and interest principal payments are made at the end of the quarter only. Th loan currently has a fixed interest rate of 7.50% per annum. Ti Royal Bank allows the company to make an additional principa payment equal to 20.0% of the balance of any term and mortga at the end of the fourth quarter each year. The Royal Bank requires that RMV maintain a current ratio of each quarter and an annual times interest earned ratio of 4.50 ( only calculated in the fourth quarter based the yearly totals) as submit audited quarterly and annual financial statements for re Also, the line of credit must be paid down to zero once per yea ensure the company only uses its line of credit to finance its se build up in net working capital. RMV attempts to maintain a long-term debt to total-capitalizat ratio of 25.00% although it is not a condition of any of its bank Company policy is to maintain a cash balance of CAD 600,000 end of each quarter. Surplus cash balances can be invested in a month term deposit at 1.50% per annum. Funds are invested o withdrawn at the end of the quarter only and income is paid ou time. Based on past experience, the company was studying wh cash balance of CAD 500,000 could be sufficient. Beginning inventory consists of two Freedom models, seven Independence models, and eight Autonomy models. Wholesale prices were the same as in 2016. Other Expenses Category Details Selling Five sales staff at CAD 32,000 per employee per year plus a commission equal to 6.0% of RV sales Administration and storage CAD 950,000 per year Depreciation CAD 285,000 per year Other than inventory purchases and depreciation, all expenses are paid as incurred. RMV has been considering paying its sales staff on a straight commission basis to improve results. The number of salespeople will likely decline as poorer performers see their incomes drop, but the remaining salespeople should be able to maintain their earnings. Capital Budget RMV has prepared a list of capital acquisitions that it plans to make in 2017: Item Estimated Acquisition Date Cost Service bay equipment - 10-year CAD 850,000 Quarter 1 life Sales facility furniture 3-year life CAD 75,000 Quarter The equipment is needed to open the remaining two service bays and additional furniture is required for the sales offices. The equipment purchases can be delayed if necessary. All assets are depreciated on a straight-line basis and the residual value is assumed to be negligible. Income Taxes The corporate tax rate is 15.0%. Corporate income tax installments of CAD 25,000 are paid at the end of each quarter. Distributions to Owner Current loan agreements limit Curtis' dividends to CAD 400,000 per year. RMV currently pays this amount to Curtis in four equal monthly installments, but she indicated it could be reduced to CAD 200,000 per year if necessary. Potential Expansion North Star approached Curtis about opening another dealership in Kelowna in 2019. She thinks this is a tremendous opportunity given that city's high average income and its emergence as a major Canadian retirement destination. Based on her experience in Kamloops, Curtis feels a net investment of CAD 6,500,000 would be needed and that 60.0% could be financed through the Royal Bank. Curtis was very anxious to pursue the Kelowna expansion, so she immediately met with her accountant. Riley Johnson, CPA, stressed that the economy in B.C. was currently doing well but that continuing low oil prices and rising interest rates may lead to a decline in consumer confidence next year that could significantly affect RV sales. He also indicated that RMV did not meet all its loan conditions last year, its profitability ratios were significantly below industry averages and it was over leveraged. Recent data from Statistics Canada indicated an industry average net profit margin of 2.10%, total assets turnover of 3.60, rate of return on assets of 7.56%, rate of return on equity of 15.12%, long-term debt to total capitalization ratio of 25.00 %, and times interest cared ratio of 4.50 - all ratios are calculated using year end information. Despite Johnson's cautious tone, Curtis still wants to somehow purse the expansion. She knows that other new North Star dealers can be found quickly if she hesitates, so being able to open a new location by 2019 is imperative. How can she address Johnson's concerns about profitability and over borrowing? Will she be able to find the equity for the proposed expansion or should she pursue a more prudent growth strategy given the current economic outlook? Page 2 Rocky Mountain Vacations Student Instructions Follow these steps in analyzing the case: 1. Assuming the status quo (no change to existing practices), prepare pro forma income statements, cash budgets, balance sheets, and ratio tables for quarter 1, 2, 3 and 4 of 2017. Note: The status quo includes the decision to reduce the dividends to CAD 200,000 and to delay the purchase of new service bay equipment to beyond 2017 due to the low utilization of existing facilities. 2. For each quarter, discuss the changes in cash flows, the financial decisions made, and whether the company is meeting its financial goals and lending conditions in each quarter based on Step 1. 3. What possible actions can RMV take to achieve its financial goals (higher profitability, reduced borrowing, and capital accumulation for the new dealership) and lending conditions? Are they reasonable? 4. Implement all reasonable actions identified in Step 3, and prepare revised pro forma income statements, cash budgets, balance sheets and ratio tables for quarter 1, 2, 3 and 4 of 2017. 5. Is RMV able to meet its financial goals and lending conditions after making the changes to the financial plan? How should the company proceed? Submission Requirements 1. Prepare pro forma income statements, cash budgets, and balance sheets for quarter 1, 2, 3 and 4 of 2017 as required in discussion question 1. This should be done in a separate Excel workbook from Part 2. 2. Prepare revised pro forma income statements, cash budgets, 2. Prepare revised pro forma income statements, cash budgets, and balance sheets for quarter 1, 2, 3 and 4 of 2017 as required in discussion question 4. This should be done in a separate Excel workbook from Part 1. 3. Prepare a 2-page memorandum addressing discussion questions 2, 3, and 5. Pro forma Template The pro forma financial statements should be prepared using the templates: Budgeted Income Statement Quarter 1 Quarter 2 Quarter 3 Quarter 4 Year Sales RV Freedom Independence Autonomy Total Service Parts Storage Total sales Expenses RV Freedom Independence Autonomy Total Service Parts Supplies Selling Administration and storage Depreciation Total expenses Operating profil Interest income est expense Income before tas Income tax Net Income Cash Budget Quarter 1 Quarter 2 Quarter 3 Quarter 4 Year Cash balance, beginning Cash receipts RV Last quarter This quarter Service Parts Storage Interest income Total cash receipts Cash disbursements Purchases Last quarter This quarter Service technicians Parts Supplies Selling Administration and storage Interest expense Income tax Regular dividends Capital purchase Total cash disbursements Sub-total Financing Repayment Linc-of-credit Term loan - Regular loan payment Term loan - Extra loan payment Borrowing Linc-of-credit Term loan Special dividends Issuance and repurchase of shares Total financing Temporary investment Cash balance, ending Budgeted Balance Sheet Quarter 1 Quarter 2 Quarter 3 Quarter Current assets Cash Temporary investments Accounts receivable Inventory Income taxes receivable Total current assets Fixed assets, net Total assets Current liabilities Accounts payable Line of credit Income taxes payable Current portion of long-term debe Total current liabilities Term loan Shareholders' equity Common shares Retained earnings Total liabilities and equities Key Financial Ratios Quarter 1 Quarter 2 Quarter 3 Quarter Current ratio (1.50) XX XX XX XX Net profit margin XX Total asset turnover XX Return on assets XX Return on equity XX Line of credit/(ARInventory) ( 4.50) XX Long-term debt / Total capitalization (-25.0% XX XXI XX XX LOC financing (maximum CAD 5.500,000) XX XX XX xx Note: Net profit margin, total asset turnover ratio, return on assets, return on equity and times interest earned should be calculated at the end of the year only using yearly data. Memorandum Template The following template should be used in preparing the memorandum: Memorandum To: From: Subject: Date Memorandum To: From: Subject: Date: (Short introduction) Status Quo (Address the questions asked in Stop 2 of the Case Analysis section) Quarter 1 Quarter 2 Quarter 3 Quarter 4 Possible Actions (Address the questions asked in Step 3 of the Case Analysis section using the table below.) Possible Action Reasonableness Recommendations (Address the questions asked in Step 5 of the Case Analysis section.) Grading Rubric You will be graded using the following criteria. Quality of Spreadsheets: 20% Physical appearance Laput page Spreadsheet automation 10 Thoroughness of Analysis: 60% Inicio