Precision Machines Case Study Tutorial Step-by-Step – Part 1

Precision Machines Project Details

The precision machines case study is an exercise in Excel that will test your ability to format cells with formulas. We are going to go through each requirement step-by-step in order to help you understand how to approach this problem and develop an effective financial plan.

 
  • Student: Martin Gibson
  • Textbook: Capital Budgeting
  • Course: FIN 370
 

Precision Machines is preparing a financial plan for the next six months to determine the financial needs of the company. The historical analysis of the company’s sales shows that the company’s total sales are 30% cash sales and 70% credit sales. Further analysis of credit sales shows that the company receives 50% of the credit sales one month after the sale and the remaining 50% in the second month after the sale. This means the cash collections from sales are 30% in the first month of the sale, 35% in the second month, and 35% in the third month.

The materials purchased by the company amounts to 50% of the sales for the month. The company pays for the purchases one month after the initial purchase. The company likes to maintain a cash balance of $5,000. The cost of borrowing is 10%. The company plans to pay off the loan whenever there is a surplus and borrow when there is a deficit.

The attached spreadsheet shows revenues (sales), expenses, capital expenditures, and other expenses for Precision Machines’ next six months. Using the information given on the spreadsheet, prepare a cash budget for January through June and determine the cash surplus, deficit, and the financing needs of the company.

Step 1: Totaling the Cash Collections

The first step is to total the cash collections for each month. This can be done by multiplying the total cash amount for each month by the collection rate. In this case, we have collections rates of 30%, 35%, and 35% for the three months. The gray boxes are using the SUM function in excel to automatically total the values entered into the yellow boxes.

Note: You can also add a new cell with the cash collection rate and multiply by to avoid repeating the tax rate in every cell.

multiply each value with the cash collection rate

Multiply each value with the cash collection rate

 

Precision Machines Step-by-Step

Check out our three part series on precision machines. 

 

Your browser is out of date. It has security vulnerabilities and may not display all features on this site and other sites.

Please update your browser using one of modern browsers (Google Chrome, Opera, Firefox, IE 10).

X